Example 5: Connecting to mySQL from php

In this example, I will show how to connect to a mySQL server from a php page. Remember to keep file permissions to the most restricted setting possible. This is a very easy place to create security problems.

To open a connection to a database use the mysql_connect() function. This function uses the format:

$db = mysql_connect("$DB_SERVER","$DB_USER","$DB_PASS") ;

The return value is either a connection, or the value FALSE for a failed connection. The FALSE return value allows a page to fail gracefully. This can be done by adding the or die statement:

$db = mysql_connect("$DB_SERVER","$DB_USER","$DB_PASS") 
  or die("Could not connect to database<br>Check passwords and sockets");

I create a file like example-5a.php for connection information. When I want to open a connection, I can call this file from another php page. Remember to set permissions to 700 for php pages. This will prevent crackers from finding your password.

Now that a connection has been opened, you can query the database using mysql_query(). mysql_query() uses the syntax:

$result = mysql_query( query , resource ) ;

To display the results, you can use a loop to insert the results into HTML. For example, I have the following table named music:

+----+------------------------+-------------+-------+------+--------------+-------+-------+-------------+
| id | artist                 | album       | price | qtty | label        | genre | media | media_count |
+----+------------------------+-------------+-------+------+--------------+-------+-------+-------------+
|  1 | string cheese incident | carnival 99 |    10 |    2 | sci fidelity |     1 |     1 |           1 |
|  2 | Stevie Ray Vaughan     | SRV         |    20 |   10 |              |     2 |     1 |           4 |
|  3 | Grateful Dead          | x           |     0 |    0 | x            |     2 |     1 |           1 |
+----+------------------------+-------------+-------+------+--------------+-------+-------+-------------+

To select all the artists from the genre "2" I can use the code in example-5b.php. (I'll explain why this is just a "2", and not a word like "Blues" in the next example.) When I run this code, it should output something like:

• Stevie Ray Vaughan
• Grateful Dead

Don't forget to close the database connection when you are finished:

mysql_close($db);

This pretty much covers querying from php. You can do any mySQL query from the php mysql_query() function.