There are numerous PHP functions for PHP -> mSQL connectivity, documented in
detail in the PHP Manual. However, you only need a few of these functions in
order to make a simple connection and select some data:
msql_connect - opens a connection to a mSQL; requires a server name.
msql_db_select - selects an mSQL database.
msql_query - issues the SQL statement.
msql_fetch_array - puts a SQL statement result row in an array.
msql_free_result - frees the resources in use by the current connection.
msql_close - closes the current connection.
Just for argument's sake, let's pretend that a mSQL database is already installed on your system. 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. Then, start your PHP code by creating a connection variable:
<?
$connection = msql_connect("servername");
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 mSQL database called "myDB". First, create a database variable, such as:
$db = msql_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 msql_query function. The msql_query function takes two arguments: the connection and SQL statement variables you've previously created.
$sql_result = msql_query($sql,$connection);
To format the results currently held in $sql_result, first separate the results by row, using the msql_fetch_array function:
while ($row = msql_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:
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:
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.
