Parsing the query string

Many PHP scripts don’t require any information from the web app to get the data that the app needs. For example, if the script’s job is to return every record from a table, or to return a predetermined subset of a table, then your app just needs to call the script.

However, it’s more common for a web app to decide on-the-fly (say, based on user input or some other event) what data it requires, and in such cases it needs to let the server know what to send. To get your web app to request data from the web server, you send a query string to the server. You can send a query string using two different methods:

• GET: Specifies the data by adding the query string to the URL of the request. This is the method I talk about in this chapter.
• POST: Specifies the data by adding it to the HTTP header of the request. This method is associated with HTML forms and some AJAX requests, which I cover in Book 6.

In the GET case, the query string is a series of name-value pairs that use the following general form:

name1=value1&name2=value2&…

Here’s an example:

book=4&chapter=1&example=2

In the case of a GET request, you build the request by taking the URL of the PHP script that will handle the request, adding a question mark (?) to designate the boundary between the script address and the query string, and then adding the query string itself. Here’s an example:

http://mcfedries.com/webcodingfordummies/example.php?book=4&chapter=1&example=2

Now your PHP script has something to work with, and you access the query string data by using PHP’s $_GET variable, which is an associative array created from the query string's name-value pairs. Specifically, the array’s keys are the query string’s names, and the array’s values are the corresponding query string values. For example, the preceding URL creates the following $_GET array:

$_GET['book'] => 4

$_GET['chapter'] => 1

$_GET['example'] => 2

Note, however, that it’s good programming practice to not assume that the $_GET array is populated successfully every time. You should check each element of the array by using PHP's isset() function, which returns true if a variable exists and has a value other than null. Here's some PHP code that checks that each element of the preceding $_GET array exists and isn’t null:

if (isset($_GET['book'])) {

$book_num = $_GET['book'];

} else {

echo 'The "book" parameter is missing!<br>';

echo 'We are done here, sorry.';

exit(0);

}

if (isset($_GET['chapter'])) {

$chapter_num = $_GET['chapter'];

} else {

echo 'The "chapter" parameter is missing!<br>';

echo 'Sorry it didn't work out.';

exit(0);

}

if (isset($_GET['example'])) {

$example_num = $_GET['example'];

} else {

echo 'The "example" parameter is missing!<br>';

echo 'You had <em>one</em> job!';

exit(0);

}

This code checks each element of the $_GET array:

• If the element exists and isn't null, the code assigns the array value to a variable.
• If the element either doesn’t exist or is null, the code outputs a message specifying the missing parameter and then stops the code by running the exit(0) function (the 0 just means that you're terminating the script in the standard way).

Connecting to the MySQL database

You give PHP access to MySQL through an object called MySQLi (short for MySQL Improved). There are actually several ways to bring PHP and MySQL together, but MySQLi is both modern and straightforward, so it’s the one I cover in this book.

You connect to a MySQL database by creating an instance of the MySQLi object. Here's the general format to use:

$var = new MySQLi(hostname, username, password, database);

• $var: The variable that stores the new MySQLi object.
• hostname: The name of the server that's running MySQL. If the server is on the same computer as your script (which is usually the case), then you can use localhost as the hostname.
• username: The account name of a user who has access to the MySQL database.
• password: The password associated with the username account.
• database: The name of the MySQL database.

Here's a script that sets up the connection parameters using four variables, and then creates the new MySQLi object:

<?php

$host = 'localhost';

$user = 'logophil_reader';

$password = 'webcodingfordummies';

$database = 'logophil_webcodingfordummies';

$mysqli = new MySQLi($host, $user, $password, $database);

?>

However, you shouldn’t connect to a database without also checking that the connection was successful. Fortunately, the MySQLi object makes this easy by setting two properties when an error occurs:

• connect_errno: The error number
• connect_error: The error message

These properties are null by default, so your code can use an if() test to check if either connect_error or connect_errno has been set:

if($mysqli->connect_error) {

echo 'Connection Failed!

Error #' . $mysqli->connect_errno

. ': ' . $mysqli->connect_error;

exit(0);

}

If an error occurs, the code displays a message like the one shown in Figure 3-1 and then runs exit(0) to stop execution of the script.

Before moving on to querying the database, there are two quick housekeeping chores you need to add to your code. First, tell your MySQLi object to use the UTF-8 character set:

$mysqli->set_charset('utf8');

Second, use the MySQLi object's close() method to close the database connection by adding the following statement at the end of your script (that is, just before the ?> closing tag):

$mysqli->close();

Creating and running the SELECT query

To run a SELECT query on the database, you need to create a string variable to hold the SELECT statement and then use that string to run the MySQLi object's query() method. Here’s an example:

$sql = 'SELECT category_name, description

FROM categories';

$result = $mysqli->query($sql);

// Check for a query error

if (!$result) {

echo 'Query Failed!

Error: ' . $mysqli->error;

exit(0);

}

The result of the query is stored in the $result variable. You might think that this variable now holds all the data, but that's not the case. Instead, $result is an object that contains information about the data, not the data itself. You make use of that information in the next section, but for now notice that you can use the result object to check for an error in the query. That is, if $result is null, the query failed, so display the error message (using the MySQLi object's error property) and exit the script.

If you want to know how many rows the SELECT query returned, you can reference the result object’s num_rows property:

$result->num_rows

Storing the query results in an array

The object returned by the query() method is really just a pointer to the actual data, but you can use the object to retrieve the SELECT query's rows. There are various ways to do this, but I’ll go the associative array route, which uses the result object’s fetch_all(MYSQLI_ASSOC) method to return all the rows as an associative array. (If you prefer to work with a numeric array, replace the MYSQLI_ASSOC constant with MYSQLI_NUM):

$array = $mysqli_result->fetch_all(MYSQLI_ASSOC);

• $array: The name of the associative array you want to use to hold the query rows
• $mysqli_result: The result object returned by MySQLi's query() method

Note that this is a two-dimensional array, which makes sense because table data is two-dimensional (that is, it consists of one or more rows and one or more columns).

I’ll make this more concrete by extending the example:

$sql = 'SELECT category_name, description

FROM categories';

$result = $mysqli->query($sql);

// Check for a query error

if (!$result) {

echo 'Query Failed!

Error: ' . $mysqli->error;

exit(0);

}

// Get the query rows as an associative array

$rows = $result->fetch_all(MYSQLI_ASSOC);

// Get the total number of rows

$total_rows = count($rows);

echo "Returned $total_rows categories:<br>";

Here, fetch_all() stores the query result as an array named $rows. The code then uses count() to get the total number of rows in the array.

Looping through the query results

By storing the query results in an array, you make it easy to process the data by looping through the array using a foreach() loop:

// Get the query rows as an associative array

$rows = $result->fetch_all(MYSQLI_ASSOC);

// Loop through the rows

foreach($rows as $row) {

echo $row['category_name'] . ': ' .

$row['description'] . '<br>';

}

Here's what’s happening in the foreach() loop:

• Each item in the $rows array is referenced using the $row variable.
• Each $row item is itself an associative array, where the key-value pairs are the column names and their values.
• Because the keys of the $row array are the column names, the code can refer to the values using the $row['column'] syntax.

Incorporating query string values in the query

I talk earlier in this chapter about how you can use $_GET to parse a URL's query string, so now I show you an example that uses a query string value in a SELECT query. First, here’s the code:

<body>

<?php

// Parse the query string

if (isset($_GET['category'])) {

$category_num = $_GET['category'];

} else {

echo 'The "category" parameter is missing!<br>';

echo 'We are done here, sorry.';

exit(0);

}

// Store the database connection parameters

$host = 'localhost';

$user = 'logophil_reader';

$password = 'webcodingfordummies';

$database = 'logophil_webcodingfordummies';

// Create a new MySQLi object with the

// database connection parameters

$mysqli = new MySQLi($host, $user, $password, $database);

// Create and run a SELECT query

// This is an INNER JOIN of the products and

// categories tables, based on the category_id

// value that was in the query string

$sql = "SELECT products.product_name,

products.unit_price,

products.units_in_stock,

categories.category_name

FROM products

INNER JOIN categories

ON products.category_id = categories.category_id

WHERE products.category_id = $category_num";

$result = $mysqli->query($sql);

// Get the query rows as an associative array

$rows = $result->fetch_all(MYSQLI_ASSOC);

// Get the category name

$category = $rows[0]['category_name'];

echo "<h2>$category</h2>";

echo '<table>';

echo '<tr>';

echo '<th>Product</th>';

echo '<th>Price</th>';

echo '<th>In Stock</th>';

echo '</tr>';

// Loop through the rows

foreach($rows as $row) {

echo '<tr>';

echo '<td>' . $row['product_name']. '</td>

<td>' . $row['unit_price'] . '</td>

<td>' . $row['units_in_stock'] . '</td>';

echo '</tr>';

}

echo '</table>>';

// That's it for now

$mysqli->close();

?>

</body>

First, note that to keep the code shorter, I removed the error checking code. There’s quite a bit going on here, so I’ll go through it piece by piece:

• The script resides within an HTML file, and you’d load the file using a URL that looks something like this:

  http://mcfedries.com/webcodingfordummies/5-3-4.php?category=1

• The first part of the script uses $_GET['category'] to get the category number from the query string, and that value is stored in the $category_num variable.
• The script then builds a SQL SELECT statement, which is an inner join on the products and categories tables. The WHERE clause restricts the results to just those products that have the category value from the query string:

  WHERE products.category_id = $category_num

• The query() method runs the SELECT query and stores the result in the $result object.
• The fetch_all(MYSQLI_ASSOC) method stores the returned row in an associative array named $rows.
• Each element in the $rows array includes the category name in the category_name column, so the script arbitrarily uses $rows[0]['category_name'] to get the category name and store it in the $category variable.
• The script then outputs an <h2> heading for the category name, as well as some HTML table tags.
• A foreach() loop runs through the query rows. During each pass, the code outputs an HTML table row (<tr>) and a table cell (<td>) for each value.
• Finally, the code outputs the closing </table> tag and closes the MySQLi connection.

Figure 3-2 shows the result.