Database Access With Php

One of the best things about PHP is its built in functions for database access. In this post, I will provide some examples on how to access a MySQL database using PHP. My free PHP host allows MySQL databases, so this came in very handy while trying my hand at PHP (I was a seasoned ASP programmer...ah...those good old days). I won't comment general code syntax, but will highlight database specific portions. For PHP help, try one of the many PHP sites on the 'net. The big mamma is of course http://www.php.net. Before I let you in on the code, let me explain what this code does. It was designed for a gateway for a link library. The table in my database is called links, and I query it for a short description and links to the site. This way, I can create a link repository and categorize the sites. The table has the following fields : siteID, siteDescription, siteTitle, siteLink, siteLogo, all VARCHAR32 types. Now, without further delay on to the code!

/* --- [ PHP Database Connection Code         ]---
   --- [ Copyright (C) Mohammad Burhan Khalid ]---
   --- [ Data Dictionary ] ---
   
   $_host = path your your MySQL server
   $_user = your user name (duh)
   $_pwd = your password (duh)
   $_db = the database name you want to connect to
   $handle = returns a "handle" to your database.
           we will use this to check the status of the
           connection
   $strDestination = the "siteID" for the site that we want to lookup. I passed it as a parameter to the gateway page. 
           Something like test.php?destination=ms
*/
   
$_host="database.mysite.com";
$_user="admin";
$_pwd="";
$_db="siteDB";
$handle = mysql_connect($_host,$_user,$_pwd);
if ($handle) { $errorCode=""; }
else { $errorCode="Can't Open Database ".$_db." ]";}
mysql_select_db($_db);
$strDestination=$HTTP_GET_VARS["destination"];
$strQuery = "SELECT * FROM links where siteID LIKE '".$strDestination."'";
$intQID = mysql_db_query($_db, $strQuery);
$QueryResult = mysql_fetch_assoc($intQID);
   $siteTitle = $QueryResult["siteTitle"];
   $siteDescription = $QueryResult["siteDescription"];
   $siteLogo = $QueryResult["siteLogo"];
   $siteLink = $QueryResult["siteLink"];
mysql_close($handle);

Now on to the explanation!

mysql_select_db($_db);

This line selects the database that you want to work with in MySQL. Takes a parameter of type string (but since all parameters in PHP are variant till runtime, all you need is a set of quotes around your variable. Ours was $_db).

$intQID = mysql_db_query($_db, $strQuery);

This little snippet does all the magic. It excutes the query and returns a handle (type int) to your query.

$QueryResult = mysql_fetch_assoc($intQID);

I used this function to return the results of the query as an associative array. Used the "handle" returned by the mysql_fetch_assoc() function.

$siteTitle = $QueryResult["siteTitle"];

This is a way to get the results from the table name. You can use numeric identifiers also, ie $QueryResult[0];. Nothing too fancy here, just getting elements from an array.

mysql_close($handle);

After we are done, its always nice to close our connection. We use the same "handle" that was returned when we opened the connection.

This is a very simplified way to connect to a MySQL database. Leave me your comments, and I might write a more advanced (say, looping through the fields, search for records, paginate, etc.) tutorials.

Leave me feedback, or email me with your comments.