Working with MySQL Data | Sams Teach Yourself PHP, MySQL and Apache All in One (4th Edition)

Inserting, updating, deleting, and retrieving data all revolve around the use of the mysql_query() function to execute the basic SQL queries you learned about in Chapter 16, "Learning Basic SQL Commands." For INSERT, UPDATE, and DELETE queries, no additional scripting is required after the query has been executed because you're not displaying any results (unless you want to). When using SELECT queries, you have a few options for displaying the data retrieved by your query. Let's start with the basics and insert some data, so you'll have something to retrieve later on.

Inserting Data with PHP

The easiest method for inserting data is to simply hard-code the INSERT statement, as shown in Listing 17.6.

Listing 17.6. A Script to Insert a Record

  1: <?php  2: // open the connection  3: $conn = mysql_connect("localhost", "joeuser", "somepass");  4: // pick the database to use  5: mysql_select_db("testDB",$conn);  6: // create the SQL statement  7: $sql = "INSERT INTO testTable values ('', 'some value')";  8: // execute the SQL statement  9: $result = mysql_query($sql, $conn) or die(mysql_error()); 10: // echo the result identifier 11: echo $result; 12: ?>

You might wonder why you need to echo the result identifier if you're just inserting data. Well, you don't have to; it's just there for kicks. You can clean this script up a bit by replacing the query execution line so that it simply executes and prints a relevant statement if successful, as shown in Listing 17.7.

Listing 17.7. The Modified Insert Script

  1: <?php  2: // open the connection  3: $conn = mysql_connect("localhost", "joeuser", "somepass");  4: // pick the database to use  5: mysql_select_db("testDB",$conn);  6: // create the SQL statement  7: $sql = "INSERT INTO testTable values ('', 'some value')";  8: // execute the SQL statement  9: if (mysql_query($sql, $conn)) { 10:     echo "record added!"; 11: } else { 12:     echo "something went wrong"; 13: } 14: ?>

Running this script will result in the addition of a row to the testTable table. To enter more records than just the one shown in the script, you can either make a long list of hard-coded SQL statements and use mysql_query() multiple times to execute these statements, or you can create a form-based interface to the record addition script.

To create the form for this script, you really only need one field because the id field can automatically increment. The action of the form is the name of the record-addition script; let's call it insert.php. Your HTML form might look something like Listing 17.8.

Listing 17.8. An Insert Form

  1: <HTML>  2: <HEAD>  3: <TITLE>Insert Form</TITLE>  4: </HEAD>  5: <BODY>  6: <FORM ACTION="insert_modified.php" METHOD=POST>  7: <P>Text to add:<br>  8: <input type=text name="testField" size=30>  9: <p><input type=submit name="submit" value="Insert Record"></p> 10: </FORM> 11: </BODY> 12: </HTML>

Save this file as insert_form.html, and put it in the document root of your Web server. Next, create the insert_modified.php script shown in Listing 17.9. The value entered in the form will replace the hard-coded values in the SQL query with a variable called $_POST[testField].

Listing 17.9. An Insert Script Used with the Form

  1: <?php  2: // open the connection  3: $conn = mysql_connect("localhost", "joeuser", "somepass");  4: // pick the database to use  5: mysql_select_db("testDB",$conn);  6: // create the SQL statement  7: $sql = "INSERT INTO testTable values ('', '$_POST[testField]')";  8: // execute the SQL statement  9: if (mysql_query($sql, $conn)) { 10:     echo "record added!"; 11: } else { 12:     echo "something went wrong"; 13: } 14: ?>

Save the script as insert_modified.php, and put it in the document root of your Web server. In your Web browser, access the HTML form that you created. It should look something like Figure 17.1.

Figure 17.1. The HTML form for adding a record.

Enter a string in the Text to Add field, as shown in Figure 17.2.

Figure 17.2. Text typed in the form field.

Finally, click the Insert Record button to execute the insert.php script and insert the record. If successful, you will see results similar to Figure 17.3.

Figure 17.3. The record has been successfully added.

To verify your work, you can use the MySQL command-line interface to view the records in the table:

 mysql> select * from testTable; +----+--------------------+ | id | testField          | +----+--------------------+ | 1 | some value          | | 2 | this is some text!  | +----+--------------------+ 2 rows in set (0.00 sec)

Next, you'll learn how to retrieve and format results with PHP.

Retrieving Data with PHP

Because you have a few rows in your testTable table, you can write a PHP script to retrieve that data. Starting with the basics, write a script that issues a SELECT query but doesn't overwhelm you with result data; let's just get the number of rows. To do this, use the mysql_num_rows() function. This function requires a result, so when you execute the query, put the result index in $result (see Listing 17.10).

Listing 17.10. A Script to Retrieve Data

  1: <?php  2: // open the connection  3: $conn = mysql_connect("localhost", "joeuser", "somepass");  4: // pick the database to use  5: mysql_select_db("testDB",$conn);  6: // create the SQL statement  7: $sql = "SELECT * FROM testTable";  8: // execute the SQL statement  9: $result = mysql_query($sql, $conn) or die(mysql_error()); 10: //get the number of rows in the result set 11: $number_of_rows = mysql_num_rows($result); 12: echo "The number of rows is $number_of_rows"; 13: ?>

Save this script as count.php, place it in your Web server document directory, and access it through your Web browser. You should see a message like this:

 The number of rows is 2

The number should be equal to the number of records you inserted during testing. Now that you know there are some records in the table, you can get fancy and fetch the actual contents of those records. You can do this in a few ways, but the easiest method is to retrieve each row as an array.

What you'll be doing is using a while statement to go through each record in the resultset, placing the values of each field into a specific variable, and then displaying the results onscreen. The syntax of mysql_fetch_array() is

 $newArray = mysql_fetch_array($result);

Follow along using the sample script in Listing 17.11.

Listing 17.11. A Script to Retrieve Data and Display Results

  1: <?php  2: // open the connection  3: $conn = mysql_connect("localhost", "joeuser", "somepass");  4: // pick the database to use  5: mysql_select_db("testDB",$conn);  6: // create the SQL statement  7: $sql = "SELECT * FROM testTable";  8: // execute the SQL statement  9: $result = mysql_query($sql, $conn) or die(mysql_error()); 10: //go through each row in the result set and display data 11: while ($newArray = mysql_fetch_array($result)) { 12:     // give a name to the fields 13:     $id = $newArray['id']; 14:     $testField = $newArray['testField']; 15:     //echo the results onscreen 16:     echo "The ID is $id and the text is $testField <br>"; 17: } 18: ?>

Save this script as select.php, place it in your Web server document directory, and access it through your Web browser. You should see a message for each record entered into testTable, as shown in Figure 17.4.

Figure 17.4. Selecting records from MySQL.

Essentially, you can create an entire database-driven application using just four or five MySQL functions. This chapter has barely scratched the surface of using PHP with MySQL; there are many more MySQL functions in PHP.

Additional MySQL Functions in PHP

There are approximately 40 MySQL-specific functions in PHP. Most of these functions are simply alternate methods of retrieving data or are used to gather information about the table structure in question. Throughout this book, especially in the project-related chapters a little later, you'll gradually be introduced to more of the MySQL-specific functions in PHP. However, for a complete list of functions, with practical examples, visit the MySQL section of the PHP manual at http://www.php.net/mysql.