JavaScript Editor Js editor     Website development 



Main Page

Previous Page
Next Page

Executing Simple Queries

Once you have successfully connected to and selected a database, you can start executing queries. These queries can be as simple as inserts, updates, and deletionsas I'll demonstrate hereor as involved as complex joins returning numerous rows, as you'll see later in the chapter. In any case, the PHP function for executing a query is mysqli_query(). It takes the database connection as its first argument and the actual query to be run as its second. You'll normally want to assign the returned result to a variable:

$query_result = mysqli_query ($dbc, $query);

The $query_result variable will, in layman's terms, contain reference information for the result of a query. In other words, $query_result will be a pointer to the data being returned by MySQL and can be used to determine the successful execution of a query.

With simple queries that do not return records, you can check their effectiveness by invoking the mysqli_affected_rows() function. For example:

echo '# of affected rows:' . mysqli_affected_rows($dbc);

This function returns the number of rows that were affected by INSERT, UPDATE, DELETE, and similar queries. This corresponds to what MySQL reports when running such queries within the mysql client (Figure 7.3).

Figure 7.3. MySQL reports upon how many records were affected by certain types of queries. You can mimic this in PHP by using mysqli_affected_rows().


One final, albeit optional, step in your script would be to close the existing MySQL connection:

mysqli_close($dbc);

This line closes the database connection established by the mysqli_connect() function. It is not required, because PHP will automatically close the connection when a script terminates, but using it does make for good programming form.

To demonstrate this process, I'll make a PHP page that displays and handles a form. The express purpose will be to add different expense categories to the database.

Troubleshooting PHP and MySQL

When using PHP and MySQL together, there are many reasons why problems might occur. For starters, if you receive an error that claims mysqli_connect() is an undefined function, it means that PHP has not been installed with MySQL support. If you see an error that says Client does not support authentication protocol, this means that you're using an older version of the PHP MySQL libraries with a newer version of MySQL. The solution is discussed in Appendix A, "Troubleshooting." If you cannot connect to MySQL and aren't seeing these problems, the most likely cause is that you are not using a username/hostname/password combination that's been established in MySQL. See Chapter 2 for instructions on creating users.

Once you have PHP and MySQL working together and once you can connect to a database, most errors stem from your SQL queries. Later in the chapter you'll learn how to make use of MySQL's error messages, but the best debugging technique is this:

  1. Print, using PHP, the query that is being executed.

  2. Run that same query using another interface like the mysql client or phpMyAdmin.

The first step verifies what exact query PHP is running in MySQL. This is particularly important with PHP, as many queries will be dynamically generated and could actually be other than you would think. The second step will show you what result that query gives. The result could be an error or not. In any case, these two simple steps will reveal whether you have a PHP, MySQL, or SQL problem occurring. And identifying the problem is the first step in solving it!


To execute simple queries:

1.
Create a new PHP document in your text editor (Script 7.2).

2.
Begin with the standard HTML code.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtm l1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/ xhtml" xml:lang="en" lang="en">
<head>
  <meta http-equiv="content-type" content="text/html; charset=utf-8" />
  <title>Add An Expense Category</title>
</head>
<body>

Script 7.2. This PHP page makes it possible to add records to the databasespecifically the expense_categories tablevia your Web browser.

1    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
2            "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
3    <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
4    <head>
5        <meta http-equiv="content-type" content="text/html; charset=utf-8" />
6        <title>Add An Expense Category</title>
7    </head>
8    <body>
9    <?php
10
11   // ***** add_expense_category.php *****
12   // ***** Script 7.2 *****
13   // This page displays and handles a form
14   // for inserting records into the expense_categories table.
15
16   if(isset($_POST['submitted'])) { // If the form has been submitted, handle it.
17
18      //Check the required form fields.
19      if (!empty($_POST['expense_category'])) {
20
21          //Include the MySQL information:
22          require_once ('../mysqli_connect.inc.php');
23

24           // Create the query:
25           $q = "INSERT INTO expense_categories (expense_category) VALUES (
'{$_POST['expense_catego ry']}')"
26
27           // Execute the query:
28           $r = mysqli_query ($dbc, $q);
29
30           // Print a message indicating success or not:
31           if (mysqli_affected_rows($dbc) == 1) {
32               echo '<b><font color="green">The category has been added!</font></b>';
33           } else {
34               echo '<b><font color="red">The category could not be added!</font></b>';
35           }
36
37           // Close the database connection:
38           mysqli_close($dbc);
39
40       } else { // Print a message if they failed to enter a category.
41           echo '<b><font color="red">You forgot to enter the category!</font></b>';
42       }
43
44   } else { // If the form has not been submitted, display it.
45
46   // Close out of the PHP for ease of coding.
47   ?>
48       Add a new category to the expense_categories table:<br />
49       <form action="add_expense_category.php" method="post">
50       <input type="text" name="expense_category" size="30" maxlength="30" /><br />
51       <input type="hidden" name="submitted" value="true"/>
52       <input type="submit" name="submit" value="Submit!"/>
53       </form>
54   <?php
55   } // Finish the main "submit" conditional.
56   ?>
57   </body>
58   </html>

In this book I will be following XHTML guidelines, so my code may look slightly different from what you are accustomed to. This is a minor point and immaterial to the topic at hand.

3.
Start the PHP section of the page.

<?php

4.
Write a conditional that checks if the form has been submitted.

if (isset($_POST[`submitted'])) {

This page will both display an HTML form and handle its submission. Therefore I'll create one large conditional that determines which step to take (display or handle) based upon whether or not the submit variable has a value. You'll see this pattern repeated in other scripts in this chapter.

To check if the form has been submitted, I'll refer to a hidden input that will act as a flag.

5.
Verify that all of the required fields were filled out.

if (!empty($_POST [`expense_category'])){

Because I do not want the script inserting blank expense categories into the table, I first make sure that text was entered into this field before proceeding. As a rule, any field that cannot be NULL in the database, aside from primary keys, ought to be checked by your scripts for values. Validating form-submitted data is critical; checking that the field isn't empty provides a minimal amount of security.

6.
Include the MySQL connection page.

require_once (`../mysql_connect. inc.php');

This one line of code will insert the contents of mysqli_connect.inc.php into this script, thereby creating a connection to MySQL and selecting the database. You may need to change the reference to the location of the file as it is on your server.

7.
Write and run the query.

$q = "INSERT INTO expense_categories (expense_category) VALUES 
(`{$_POST[`expense_category']}')";
$r = mysqli_query ($dbc, $q);

This query itself is similar to those demonstrated in Chapter 5, "Basic SQL." After assigning the query to a variable, it is run through the mysqli_query() function, which sends the SQL to MySQL.

8.
Print out the appropriate messages (Figures 7.4 and 7.5).

Figure 7.4. The script will display a message indicating successful insertion of a new category.


Figure 7.5. A message like this normally means there was a problem with the query. The "Error Handling" section later in the chapter discusses some useful debugging techniques.


if (mysqli_affected_rows($dbc) == 1) {
  echo `<b><font color="green">The category has been added! </font></b>';
}else {
  echo `<b><font color="red">The category could not be added! </font></b>';
}

The mysqli_affected_rows() function will return the number of rows in the database affected by the previous query. In this case, the INSERT query should create one new row.

9.
Close the database connection.

mysqli_close($dbc);

While closing the database connection is not required, it's always a good idea (assuming the connection is no longer required).

10.
Finish up the first part of the conditional and display the second part.

   } else {
       echo `<b><font color= "red">You forgot to enter the category!</font></b>';
   }
} else {

This completes the "handle" part of the script. The message will be printed if the form is not filled out (Figure 7.6). The rest of this page will be used for displaying the form.

Figure 7.6. Failure to fill out the form properly results in this message.


11.
Create the HTML form.

?>
Add a new category to the expense_categories table:<br />
<form action="add_expense_category.php" method="post">
<input type="text" name="expense_category" size="30" maxlength="30" /><br />
<input type="hidden" name= "submitted" value="true" />
<input type="submit" name= "submit" value="Submit!" />
</form>

I've kept the form and HTML as simple as possible. Here you should notice that I use an HTML input name (expense_category) that corresponds exactly to what the MySQL column name is. The maximum size of the input box also corresponds exactly to the maximum size of the column in the table. Neither of these settings is required, but both make for fewer mistakes. Also, since this page both displays and handles the form, the action attribute refers to this script.

One change you could make would be to offer up multiple text boxes for inserting expense categories. Then you could change your query on the handle side to perform multiple inserts.

12.
Complete the script.

<?php
}
?>
</body>
</html>

13.
Save the file as

add_expense_category.php.

I prefer longer, more descriptive filenames, but if you would like to use something different, be sure to also change the initial <form> tag accordingly.

14.
Test the file by running the script in your Web browser (Figure 7.7).

Figure 7.7. The simple HTML form first displayed by add_expense_category.php allows you to add records to the expense_categories table.


After running the script, you can always ensure that it worked by using the mysql client to view the values in the table (Figure 7.8).

Figure 7.8. Use the mysql client (or another interface) to confirm that the operations of your PHP script did work.


Tips

  • You should not end your queries with a semicolon in PHP, as you did when using the mysql client. This is a common, albeit harmless, mistake to make. (Lines of PHP code do conclude with a semicolon, of course.)

  • If, in the form, you submit text that contains a single quotation mark, the character will need to be escaped (preceded by a backslash) before the string is inserted into the database. Otherwise, the quotation mark will interfere with those delineating column values. For more information, see the sidebar "Magic Quotes" under "Security Considerations" later in this chapter.

  • You are not always obligated to create a $q (or $query) variable as I tend to do. Instead, you could directly insert your query text into mysqli_query():

    $r = mysqli_query($dbc, `SELECT * FROM tablename');

    However, as the construction of your queries becomes more complex, using a variable will be the only option.



Previous Page
Next Page


JavaScript Editor Js editor     Website development


©