Home > MySQL > Querying with PHPprinter version

MySQL Querying with PHP

This tutorial uses the people table created in the Create Tutorial.

Using Query Strings

MySQL queries used with PHP are the same as MySQL command line queries.

But with PHP there are two essentials differences.

A query takes the form of a string variable that is passed as an argument of a PHP library function.
The library function returns the results in an array variable.

Constructing a Query String

This is straightforward and follows the rules of PHP single quoted and double quoted strings covered in the PHP Strings Tutorial.

Variables may be embedding in query strings using concatenation as covered in the PHP Variables Tutorial.

The mysql_query() function uses the query string as an argument and returns the results in an array.

Example of a Query String

<?php
$var1 = "firstname";
$var2 = "lastname";
$query = "SELECT $var1, $var2 FROM people";
$result = mysql_query($query, $connection) or exit(mysql_error());
?>

Any query, no matter how complex, that you would use with the MySQL command line can be placed in a PHP string and used with the mysql_query() function.

Outputting the Results

The results can be accessed by using the mysql_fetch_array() function that takes the results returned from the mysql_query() function as an argument.

The function retrieves one row at a time into an associative array that uses the table attribute names to access the values.

To retrieve the values from all rows a loop is required - normally a while loop since you normally won't know how many rows have been returned.

Example of Retrieving Results

<?php
while ($row = mysql_fetch_array ($result))
{
    $firstname = $row["firstname"];
    $lastname = $row["lastname"];
    echo ($firstname.' '.$lastname.'<br>');
}
?>

Formatting Results

Often you will want to output MySQL results in an HTML table. This means mixing HTML, PHP and MySQL in one script but it's fairly straightforward.

The contents of the people table are shown below in an HTML table followed by the script required to output it.

ID	First Name	Lastname	Address	County	Country	Age
1	Dave	Clark	Main Street	West Lothian	UK	40
2	Mary	Smith	North Street	Cumbria	UK	36
3	Frank	Jones	Brick Lane	London	UK	34
4	Charles	Clarkson	Ferry Road	Sussex	UK	32

Example of Formatting MySQL Results

<?php
$connection = mysql_connect("localhost", "username", "password") or exit(mysql_error());
mysql_select_db("test", $connection) or exit(mysql_error());
$query = "SELECT * FROM people";
$result = mysql_query($query, $connection) or exit(mysql_error());
?>
<table border="1">
<tr><th>ID</th>
<th>First Name</th>
<th>Lastname</th>
<th>Address</th>
<th>County</th>
<th>Country</th>
<th>Age</th></tr>
<?php
while ($row = mysql_fetch_array ($result))
{
    $id = $row[id];
    $firstname = $row["firstname"];
    $lastname = $row["lastname"];
    $address = $row["address"];
    $county = $row["county"];
    $country = $row["country"];
    $age = $row["age"];
    echo ('<tr>');
    echo ('<td>'.$id.'</td>');
    echo ('<td>'.$firstname.'</td>');
    echo ('<td>'.$lastname.'</td>');
    echo ('<td>'.$address.'</td>');
    echo ('<td>'.$county.'</td>');
    echo ('<td>'.$country.'</td>');
    echo ('<td>'.$age.'</td>');
    echo ('</tr>');
}
?>
</table>

Previous - Accessing a Database Next - Create and Drop with PHP