Working with MySQL Data
| Inserting, updating, deleting, and retrieving data all revolve around the use of the mysqli_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. Inserting Data with PHPThe easiest method for inserting data at this stage in the game is to simply hard-code the INSERT statement, as shown in Listing 18.4. Listing 18.4. A Script to Insert a Record
The only change between this scriptfor record insertionand the script in Listing 18.3 for table creation is the SQL query stored in the $sql variable on line 8, and text modifications on lines 12 and 14. The connection code and the structure for issuing a query remain the samein fact, most procedural code for accessing MySQL will fall into this same type of code template. Call this script mysqlinsert.php and place it on your web server. 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 mysqli_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 need only 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 18.5. Listing 18.5. An Insert Form
Save this file as insert_form.html and put it in the document root of your web server. Next, create the insert.php script shown in Listing 18.6. The value entered in the form will replace the hard-coded values in the SQL query with a variable called $_POST["testfield"]. Listing 18.6. An Insert Script Used with the Form
The only change between this script and the script in Listing 18.4 is the use of $_POST["testfield"] in place of the hard-coded text string. Save the script as insert.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 18.1. Figure 18.1. The HTML form for adding a record. Enter a string in the Text to Add field, as shown in Figure 18.2. Figure 18.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 18.3. Figure 18.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 PHPBecause 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 mysqli_num_rows() function (see line 12 of Listing 18.7). Listing 18.7. A Script to Retrieve Data
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: Result set has 4 rows. Line 12 uses the mysqli_num_rows() function to retrieve the number of rows in the resultset ($res), and it places the value in a variable called $number_of_rows. Line 13 prints this number to your browser. The number should be equal to the number of records you inserted during testing. By the Way The use of mysqli_free_result() before closing the connection with mysqli_close() ensures that all memory associated with the query and result is freed for use by other scripts. 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. You'll use 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 mysqli_fetch_array() is $newArray = mysqli_fetch_array($result_set); Follow along using the sample script in Listing 18.8. Listing 18.8. A Script to Retrieve Data and Display Results
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 18.4. This message is created in the while loop in lines 12 through 15. Figure 18.4. Selecting records from MySQL. Essentially, you can create an entire database-driven application using just four or five MySQLi functions. This chapter has barely scratched the surface of using PHP with MySQL; there are many more MySQLi functions in PHP. Additional MySQL Functions in PHPMore than 100 MySQL-specific functions are available through the MySQLi interface 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 MySQLi section of the PHP Manual at http://www.php.net/mysqli. |
EAN: 2147483647
Pages: 327