Retrieving data From MySQL to PHP

Now, that we have inserted some data into the database, we want to get it back.

<?php

$host = "localhost"; 
$user = "user12"; 
$pass = "34klq*"; 
$db = "mydb";

$r = mysql_connect($host, $user, $pass);

if (!$r) {
    echo "Could not connect to server\n";
    trigger_error(mysql_error(), E_USER_ERROR);
} else {
    echo "Connection established\n"; 
}

$r2 = mysql_select_db($db);

if (!$r2) {
    echo "Cannot select database\n";
    trigger_error(mysql_error(), E_USER_ERROR); 
} else {
    echo "Database selected\n";
}

$query = "SELECT * FROM Cars LIMIT 5";

$rs = mysql_query($query);

if (!$rs) {
    echo "Could not execute query: $query";
    trigger_error(mysql_error(), E_USER_ERROR); 
} else {
    echo "Query: $query executed\n";
} 

while ($row = mysql_fetch_assoc($rs)) {
    echo $row['Id'] . " " . $row['Name'] . " " . $row['Price'] . "\n";
}

mysql_close();

?>

In this example, we retrieve five rows from the Cars table.

$query = "SELECT * FROM Cars LIMIT 5";

This SQL statement selects 5 rows from the Cars table.

$rs = mysql_query($query);

We execute the query with the mysql_query() function and retrieve the result set.

if (!$rs) {
    echo "Could not execute query: $query";
    trigger_error(mysql_error(), E_USER_ERROR); 
} else {
    echo "Query: $query executed\n";
} 

If the query did not succeed, we generate an error message.

while ($row = mysql_fetch_assoc($rs)) {
    echo $row['Id'] . " " . $row['Name'] . " " . $row['Price'] . "\n";
}

We loop through the result set and print the data to the console. The mysql_fetch_assoc() function returns an associative array of strings that corresponds to the fetched row, or FALSE if there are no more rows. In other words, the function call returns a row from the result set. This row is in the form of an associative array. The column names are keys to the associative array. When there are no more rows in the result set, the function returns FALSE and the while loop terminates.

$ php query.php
Connection established
Database selected
Query: SELECT * FROM Cars LIMIT 5 executed
1 Audi 52642
2 Mercedes 57127
3 Skoda 9000
4 Volvo 29000
5 Bentley 350000

This is the output of the example.

---

In the second example, we will fetch data with the mysql_fetch_row() function.

<?php

$host = "localhost"; 
$user = "user12"; 
$pass = "34klq*"; 
$db = "mydb";

$r = mysql_connect($host, $user, $pass);

if (!$r) {
    echo "Could not connect to server\n";
    trigger_error(mysql_error(), E_USER_ERROR);
} else {
    echo "Connection established\n"; 
}

$r2 = mysql_select_db($db);

if (!$r2) {
    echo "Cannot select database\n";
    trigger_error(mysql_error(), E_USER_ERROR); 
} else {
    echo "Database selected\n";
}

$query = "SELECT Id, Name, Price From Cars LIMIT 5";

$rs = mysql_query($query);

if (!$rs) {
    echo "Could not execute query: $query";
    trigger_error(mysql_error(), E_USER_ERROR); 
} else {
    echo "Query: $query executed\n";
} 

$nrows = mysql_num_rows($rs);

for ($i = 0; $i < $nrows; $i++) {
    $row = mysql_fetch_row($rs);
    echo $row[0];
    echo " ";
    echo $row[1];
    echo " ";
    echo $row[1];
    echo "\n";
}

mysql_close();

?>

We get the first 5 rows from the Cars table.

$nrows = mysql_num_rows($rs);

The mysql_num_rows() function gets the number of rows from the result set.

for ($i = 0; $i < $nrows; $i++) {
    $row = mysql_fetch_row($rs);
    echo $row[0];
    echo " ";
    echo $row[1];
    echo " ";
    echo $row[1];
    echo "\n";
}

We use the for loop to iterate over the returned rows. The mysql_fetch_row() function retrieves the row from the result set in the form of an enumerated array.

$ php query.php
Connection established
Query: SELECT * FROM Cars LIMIT 5 executed
1 Audi 52642
2 Mercedes 57127
3 Skoda 9000
4 Volvo 29000
5 Bentley 350000

Output.

---

In the following example, we show how to retrieve a specific row from a table.

<?php

$host = "localhost"; 
$user = "user12"; 
$pass = "34klq*"; 
$db = "mydb";

$r = mysql_connect($host, $user, $pass);

if (!$r) {
    echo "Could not connect to server\n";
    trigger_error(mysql_error(), E_USER_ERROR);
} else {
    echo "Connection established\n"; 
}

$r2 = mysql_select_db($db);

if (!$r2) {
    echo "Cannot select database\n";
    trigger_error(mysql_error(), E_USER_ERROR); 
} else {
    echo "Database selected\n";
}

$name = "Volkswagen";

$query = sprintf("SELECT Id, Name, Price From Cars Where Name = '%s'", 
    mysql_real_escape_string($name));

$rs = mysql_query($query);

if (!$rs) {
    echo "Could not execute query: $query\n";
    trigger_error(mysql_error(), E_USER_ERROR); 
} else {
    echo "Query: $query executed\n";
} 

while ($row = mysql_fetch_object($rs)) {
    echo $row->Id;
    echo " ";
    echo $row->Name;
    echo " ";
    echo $row->Price;
    echo "\n";
}

mysql_close();

?>

Developers must take security concerns into account when working with input from users. We must always process the data sent from outside world. Check for validity of the data.

$name = "Volkswagen";

In the script, we check, if we have "Volkswagen" in the Caras table. This value might come from an xml file or a web form. We will show, how to check it.

$query = sprintf("SELECT Id, Name, Price From Cars Where Name = '%s'", 
    mysql_real_escape_string($name));

We build the SQL statement using the sprintf() function. We process the $name variable with the mysql_real_escape_string()function. This function escapes special characters in a string for use in an SQL statement. This prevents SQL injection attacks and data corruption. After the variable was processed, it is put into the SQL statement string.

while ($row = mysql_fetch_object($rs)) {
    echo $row->Id;
    echo " ";
    echo $row->Name;
    echo " ";
    echo $row->Price;
    echo "\n";
}

We fetch the data using the mysql_fetch_object() function. The function fetches a result row as an object. And we use the object notation to get the table columns.

$ php query3.php
Connection established
Database selected
Query: SELECT Id, Name, Price From Cars Where Name = 'Volkswagen' executed
8 Volkswagen 21600

The output of the example. We found the car and printed the whole row to the console.