Retrieving data from the database table

For Retrieving data from the database, we first need to open a connection and select the relevant database where our data table exists. Here our database will be “test”;

mysql_connect(“localhost”, “username”, “password”) or die(mysql_error());

mysql_select_db(“test”) or die(mysql_error());

The next step is to send an SQL query – in this case a Select statement – to the database.

Now try to get all records from MySQL. The SQL statement for this is:

SELECT * FROM users;

Using above MySQL statement we can get all records from the user table.

We need to send this command to the server and store the response. We can do this by using the mysql_query() function in this way:

$result = mysql_query(“SELECT * FROM users”);

If you try to print it with echo then you will get an output something like this:

Resource id #3

This is not what we want. To display the selected data correctly you need to do a bit more. There are more functions in PHP which you can use to retrieve data from a MySQL database result set. These are the followings:

• mysql_fetch_assoc() – Fetch a result row as an associative array
• mysql_fetch_row() – Fetch a result row as an enumerated array
• mysql_fetch_array() – Fetch a result row as an associative array, a numeric array, or both

All of them convert one record of the result to an array and later you can use this array as you want. To get the array you need to call one of the above mentioned functions. In this case I use the associative version.

$row = mysql_fetch_assoc($result);

echo (“ID: “.$row['id'].”,

 Name:”.$row['name'].”,

         City:”.$row['city'].”,

        Age:”.$row['age'].”<br/>”);

However the function returns only with one record. To list all of the records we need to create a loop. The fetch functions returns with an array if there is a record in the result set and returns false if no more records are available. So creating a while loop is very simple as here:

$result = mysql_query(“SELECT * FROM users”);  

while ($row = mysql_fetch_assoc($result)){

    echo (“ID: “.$row['id'].”,

     Name:”.$row['name'] .”,

     City:”.$row['city'].”,

     Age:”.$row['age'].”<br/>”);

   }