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

Basics Creating a Document Head and Body Elements Formatting Text Creating Lists Embedding Images URLs Explained Linking to Documents Creating Tables Forms Form Basics Input and Textarea Select Lists Frames Framesets The Frame Element Nested Framesets Targetting Frames Inline Frames		Tips and Tricks Meta Tags Transition Effects HTML Generators Create a Document Create a List Create a Table

Basics Implementing CSS CSS Syntax Pseudo Classes/Elements CSS Classes CSS Properties Font Properties Color and Background Text Properties Border Properties Margins and Padding Size and Position		Tips and Tricks Menu Buttons Special Effects

Basics Running a Script Variables Expressions and Operators Objects.Properties.Methods The Date Object Strings Regular Expressions Defining RegExp Patterns Branches and Conditions Loops Arrays Array Basics Array Methods Sorting Arrays User-Defined Functions Cookies Windows Frames		Tips and Tricks Image Replacement Using Includes Form Validation Debugging

Basics Creating a Script Running a Script Variables Expressions and Operators Strings Strings Basics Strings and Substrings Replacing Substrings Regular Expressions Branches and Conditions Loops Arrays Array Basics Array Functions Sorting Arrays User-Defined Functions Include and Require Uploading Files File Functions Session Variables		Tips and Tricks Page Templates Form Reply Scripts Form Validation JavaScript to PHP