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. Here is a an example: 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:
<?     $connection = mysql_connect("servername","username","password");
Test that a connection was established and if it wasn't, print an error message and exit the program:
if (!$connection) {         echo "Couldn't make a connection!";         exit;     }
If you have made it through the connection test, the next steps are to select the database and create the SQL statement. Suppose that the COFFEE_INVENTORY table exists in a MySQL database called "myDB". First, create a database variable, such as:
$db = mysql_select_db("myDB", $connection);
Next, test that the database was selected. If it wasn't, print an error message and exit the program:
if (!$db) {         echo "Couldn't select database!";         exit;     }
Up to this point, you've told PHP to connect to a server and select a database. If you've made it this far, you can issue a SQL statement and hopefully see some results! Using the COFFEE_INVENTORY table, suppose you want to view your inventory, including the name of the coffee and the roast type, with the highest number of bags listed first. Create a variable that holds your SQL statement:
$sql = "SELECT COFFEE_NAME, ROAST_TYPE, QUANTITY            FROM COFFEE_INVENTORY          ORDER BY QUANTITY DESC";
Next, create a variable to hold the result of the query, carried out by the mysql_query function. The mysql_query function takes two arguments: the connection and SQL statement variables you've previously created.
$sql_result = mysql_query($sql,$connection);
To format the results currently held in $sql_result, first separate the results by row, using the mysql_fetch_array function:
while ($row = mysql_fetch_array($sql_result)) {         // more code here         }
The while loop will create an array called $row for each record in the result set. To get the individual elements of the record (COFFEE_NAME, ROAST_TYPE, QUANTITY), create specific variables:
$coffee_name = $row["COFFEE_NAME"];     $roast_type = $row["ROAST_TYPE"];     $quantity = $row["QUANTITY"];
You'll probably want to print the results in a simple HTML table. Step back and place this statement before the while loop begins, to open the table tag and create the row headings:
echo "<TABLE BORDER=1>";     echo "<TR><TH>Coffee Name&lt;/TH><TH>Roast Type&lt;/TH><TH>Quantity&lt;/TH>";
After defining the variables within the while loop, print them in table format:
echo "<TR><TD>$coffee_name&lt;/TD><TD>$roast_type&lt;/TD><TD>$quantity&lt;/TD>&lt;/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 "<TR><TD>$coffee_name&lt;/TD><TD>$roast_type&lt;/TD><TD>$quantity&lt;/TD>&lt;/TR>";     }
After the while loop, close the HTML table:
echo "&lt;/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:
<?php     // create connection     $connection = mysql_connect("servername","username","password");     // test connection     if (!$connection) {         echo "Couldn't make a connection!";         exit;     }     // select database     $db = mysql_select_db("myDB", $connection);     // test selection     if (!$db) {         echo "Couldn't select database!";         exit;     }     // create SQL statement     $sql = "SELECT COFFEE_NAME, ROAST_TYPE, QUANTITY            FROM COFFEE_INVENTORY          ORDER BY QUANTITY DESC";     // execute SQL query and get result     $sql_result = mysql_query($sql,$connection);     // start results formatting     echo "<TABLE BORDER=1>";     echo "<TR><TH>Coffee Name&lt;/TH><TH>Roast Type&lt;/TH><TH>Quantity&lt;/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 "<TR><TD>$coffee_name&lt;/TD><TD>$roast_type&lt;/TD><TD>$quantity&lt;/TD>&lt;/TR>";     }     echo "&lt;/TABLE>";     // free resources and close connection     mysql_free_result($sql_result);     mysql_close($connection); ?>