Retreiving Data from mySQL

Generating a query

Now that we have a connection to our database and have selected the database we want to use, we nead to execute a query. To do this we will first create a variable named $sql which will contain our SQL statement.

$sql = "SELECT Manufact.ManName AS manName, Car.CarName AS CarName FROM Car, Manufact WHERE Manufact.ManID = Car.ManID ORDER BY CarName";

Next we execute query using mysql_query().

$rs = mysql_query($sql, $linkID);

This will give us a result set which we can manipulate with several commands.

1. mysql_num_rows($rs).
   This will return the total number of rows in the result set.
2. mysql_fetch_row($rs).
   This will return an array for each row in the result set where each column is associated with an index.
   

     while($r = mysql_fetch_row($rs)) {
       print "<p>".$r[0]." - ".$r[1]."</p>";
     }
     

3. mysql_fetch_assoc($rs).
   This will return an accociative array(hash) for each row in the result set where each column is associated with an column with the same name.
   

     while($r = mysql_fetch_assoc($rs)) {
       print "<p>".$r['colName1']." - ".$r['colName2']."</p>";
     }
     

NOTE: #2 & #3 should be used with while() loops.

Closing your connection

Once we've finished with our query, to save memory resources on the server, we should close our connection. We do this by using mysql_close($linkID).

Summary

So putting this all together we have:

<?php
$linkID = mysql_connect("hostname","username","password");
mysql_select_db("myDatabaseName",$linkID);
$sql = "SELECT userName, password FROM userTable";
$rs = mysql_query($sql, $linkID);
while($r = mysql_fetch_assoc($rs)) {
  print "<p>UserName: ".$r['userName']."<br />Password: ".$r['password']."</p>";
}
mysql_close($linkID);
?>

NEXT


PREVIOUS


Master Index