Simple Connection to MySQL with PHP

The MySQL database is one of the most popular among PHP developers. It's my database of choice, and has held up remarkably well in multiple e-commerce situations. Therefore, you would be correct in assuming that there are numerous well-documented PHP functions you can use in conjunction with your MySQL databases. However, you only need a few of these functions in order to make a simple connection and select some data:


mysql_connect - opens a connection to the MySQL server; requires a hostname, username and password.



mysql_db_select - selects a database on the MySQL server.


mysql_query - issues the SQL statement.




mysql_fetch_array - puts a SQL statement result row in an array.


mysql_free_result - frees the resources in use by the current connection.


mysql_close - closes the current connection.


For the rest of PHP's MySQL-related functions, get thee to the PHP Manual!


Just for argument's sake, let's pretend that MySQL is already installed on your system, and you have a valid username and password for an existing database. Let's also assume that you've created a table on that database, called COFFEE_INVENTORY. The COFFEE_INVENTORY table has three columns: COFFEE_NAME, ROAST_TYPE and QUANTITY.


The rows in the COFFEE_INVENTORY table could be populated with data such as:


French Roast,dark,18


Kenya,medium,6


Ethiopian Harrar,medium,35


Sumatra,dark,8


Columbian,light,12


Now, let's do some PHP. Before you begin, you must know the name of the server on which the database resides, and have a valid username and password for that server. Then, start your PHP code by creating a connection variable:

";
echo "Coffee Name</TH>Roast Type</TH>Quantity</TH>";


After defining the variables within the while loop, print them in table format:

echo "$coffee_name</TD>$roast_type</TD>$quantity</TD></TR>";


The new while loop now looks like this:

while ($row = mysql_fetch_array($sql_result)) {
$coffee_name = $row["COFFEE_NAME"];
$roast_type = $row["ROAST_TYPE"];
$quantity = $row["QUANTITY"];
echo "$coffee_name</TD>$roast_type</TD>$quantity</TD></TR>";
}


After the while loop, close the HTML table:

echo "</TABLE>";


Finally, you'll want to free up the resources used to perform the query, and close the database connection. Failing to do so could cause memory leaks and other nasty resource-hogging things to occur.

mysql_free_result($sql_result);
mysql_close($connection);
?>


The full script to perform a simple connection and data selection from a MySQL database could look something like this:

";
echo "Coffee Name</TH>Roast Type</TH>Quantity</TH>";

// format results by row
while ($row = mysql_fetch_array($sql_result)) {
$coffee_name = $row["COFFEE_NAME"];
$roast_type = $row["ROAST_TYPE"];
$quantity = $row["QUANTITY"];
echo "$coffee_name</TD>$roast_type</TD>$quantity</TD></TR>";
}

echo "</TABLE>";

// free resources and close connection
mysql_free_result($sql_result);
mysql_close($connection);
?>


Please see the PHP Manual for additional MySQL functions, and try using your own tables and SQL statements instead of the examples above.