Connecting to MySQL with PHP

Summary

PHP can be used to write dynamic Web pages that can connect and communicate with a MySQL server. PHP has built-in functions for the task, while Perl has a module called DBI for interfacing with databases like MySQL. This article demonstrates some simple examples of how to use PHP as a separate front-end for MySQL.

Note: You must install and start your MySQL server before you can retrieve data from it. Today we will be using my MySQL as part of the lab.

Included on this page:

MySQL Scripting With PHP
Resources

MySQL Scripting With PHP

Here is an example PHP script that connects to your MySQL server and prints out the first row of the user table from the mysql database. Copy and paste the following code into a file called "mysql-test.php", make the appropriate substitutions for the highlighted portions, and then upload the file to a location in your Web directory.

<html>
<body>
<?php

    // connect and select a database 
    $link = mysql_connect("host.u.washington.edu:port number", "root", "rootpassword")
          or die ("Couldn't connect:  Check to make sure that:<br>" .
          "<ul><li>your MySQL server is running</li>" .
          "<li>you used the correct hostname (<tt>vergil/ovid</tt>)<li>" .      
          "<li>you didn't forget the 'u' in the hostname</li>" .
          "<li>you added a colon with your port number after the hostname</li>" .
          "<li>you used the username 'root'</li>" .       
          "<li>you used the correct root password</li>" .
          "<li>you didn't forget to close a set of quotation marks</li><br><br>");
    print "Connected successfully.<br>";
    $db = "mysql";
    mysql_select_db($db) or die("Could not select the database '" . $db . "'.  Are you sure it exists?");

    // perform an SQL query 
    $query = "SELECT * FROM user";
    $result = mysql_query($query) or die("Query failed");

    // print the result of the first row (row counting starts at zero)
    printf("Host: %s<br>\n", mysql_result($result, 0, "Host"));                  
    printf("User: %s<br>\n", mysql_result($result, 0, "User"));
    printf("Grant privilege: %s<br>\n", mysql_result($result, 0, "Grant_priv"));

    // free result set 
    mysql_free_result($result);

    // close the connection 
    mysql_close($link);
    
?>
</body>
</html>

Be sure to replace host with either vergil or ovid, port number with the port your MySQL server is running on, and rootpassword with the root password you selected when you set up your MySQL server.
Save the file as "mysql-test.php" in your public_html directory. When you view the file in a Web browser, you should see something similar to the following:

Connected successfully.
Host: localhost
User: root
Grant privilege: Y
This tells you that your script successfully connected to your MySQL server. It then selected the database "mysql" that you configured when you installed MySQL, and then output the host, user, and grant privilege of the first row in the "user" table. Not very exciting, but if you haven't created any databases yet, this default mysql database is the only one you've got. To create your own, see Using SQL (Structured Query Language) with the Command-Line MySQL Client.

Resources

The scripts in this article demonstrate the most basic MySQL connection and query functions. For more information about PHP and DBI module functions, refer to the following links.

PHP
PHP.net has official MySQL Documentation; the Webmonkey PHP/MySQL Tutorial is a gentle and methodical introduction to PHP scripting for MySQL.

Perl DBI
Perl.com has a very good Short Guide to DBI.