Web Design & Development

[ Web Design & Development Topics ]

MySQL and PHP

Connecting to a MySQL Server

Before we can do anything about connecting from the Web to our MySQL database(s) we first have to be able to tell our Web browser where the infromation is. Take a look:

//extract($_post);
$url="localhost";
$uname="root";
$pword="";
$dbase="cs453";

//connect to a database server
$conn = mysql_connect($url,$uname,$pword) or
die("unable to connect to database server at ".$url);

//connect to a specific database on the selected server
mysql_select_db($dbase,$conn) or
die("unable to connect to the database named ".$dbase." at ".$url);

This code is the code you need to connect PHP to your MySQL server.

Note: If you are using the resource bundle, the $uname will always be root and the $pword will always be blank.

Selecting a Record Set

Thats all well and good that we now know how to connect to the database, but now we need to make use of it. Lets start out by determining how many tables we have to work with. First we need to list our tables:

$sql="show tables";
$result = mysql_query ($sql);

Next we need to count up the results. To do this we will use the mysql_num_rows function and assign it to a variable:

$numrows=@mysql_num_rows($result);

Note: the @ sign above is for supressing errors, it is not required

Cut and paste the following code into a notepad document and save it as testing.php in your c:\phpdev\www folder. Once you have done that navigate to the file in your web browser (http://localhost/testing.php) to see it in action:

<?
$url="localhost";
$uname="root";
$pword="";
$dbase="cs453";

$conn = mysql_connect($url,$uname,$pword) or
die("unable to connect to database server at ".$url);

mysql_select_db($dbase,$conn) or
die("unable to connect to the database named ".$dbase." at ".$url);

$sql="show tables";
$result = mysql_query ($sql);

$numrows=@mysql_num_rows($result);

echo "$numrows row(s) was/were returned from your query of how many tables there are.<hr />";
?>

Working with Record Sets

We now now how to connect to and interact with our database. Lets see what we can do to work with the record sets. Consider this:

while ($row = mysql_fetch_assoc($result)) {
foreach ($row as $col_name => $col_val) {
echo "Column Name: $col_name; Value: $col_val<br />\n";
}
}

First, we start a while loop which will run as long as there are rows in the record set. PHP knows how long to run the instructions within the loop because the "mysql_fetch_assoc" function will continue to return an associative array of each row in the record set until there are no more rows.

Next, we evaluate the data for each row. In plain English we are saying that for each row we will print out the name and value for each column.

Add this above code snippet to your php script to see it in action. Your page should now look like this:

<?
$url="localhost";
$uname="root";
$pword="";
$dbase="cs453";

$conn = mysql_connect($url,$uname,$pword) or
die("unable to connect to database server at ".$url);

mysql_select_db($dbase,$conn) or
die("unable to connect to the database named ".$dbase." at ".$url);

$sql="show tables";
$result = mysql_query ($sql);

$numrows=@mysql_num_rows($result);

echo "$numrows row(s) was/were returned from your query of how many tables there are.<p/>";

echo "Here is a list of your tables:<p/>";

while ($row = mysql_fetch_assoc($result)) {
foreach ($row as $col_name => $col_val) {
echo "Column Name: $col_name; Value: $col_val<br />\n";
}
}

?>

Ok, now that is more useful, but what if we are looking for specific information to be used in a specific place?

Displaying Specific Data From a Record Set

Now let's suppose you wanted to get some specific information out of your database? You could do so in much the same way. Assuming you still have the books table that you created in the last lesson you can do the following:

<?
$url="localhost";
$uname="root";
$pword="";
$dbase="cs453";

$conn = mysql_connect($url,$uname,$pword) or
die("unable to connect to database server at ".$url);

mysql_select_db($dbase,$conn) or
die("unable to connect to the database named ".$dbase." at ".$url);

$sql="select title, author, edition from books";
$result = mysql_query ($sql);

while ($row = mysql_fetch_assoc($result)) {
extract($row);
echo "The book titled ".$title." was written by ".$author." and is a ".$edition."." <br />";
}

?>

Now what if we want to insert, update, or alter any information in the database?

Using PHP to Modify Database Records

Using PHP to insert, update, or alter information in the database is even easier than selecting records to view. Consider this:

<?
$url="localhost";
$uname="root";
$pword="";
$dbase="cs453";

$conn = mysql_connect($url,$uname,$pword) or
die("unable to connect to database server at ".$url);

mysql_select_db($dbase,$conn) or
die("unable to connect to the database named ".$dbase." at ".$url);

$sql="insert into books(title,author,edition)
values
('Designing Web Usability','Jakob Nielsen','Paperback')";

$result = mysql_query ($sql);

$affrows=@mysql_affected_rows();
echo "$affrows row(s) was/were affected by your query<br />";

?>

Cut, paste, and view this file on your phpdev system to see what happens.