Using mysqli_insert

Using mysqli_insert_id()

Relational databases can be tricky when it comes to using primary and foreign keys. To maintain the integrity of this relationship, a primary key must be created in one table (e.g., expense_categories) before being used as a foreign key in another (expenses). As you witnessed in Chapter 6, "MySQL Functions," the LAST_INSERT_ID() function returns the previously created primary key (auto-incremented number). In PHP, there is the similar mysqli_insert_id() function.

$id = mysqli_insert_id($dbc);

When making an application based upon a relational database, you'll frequently create scripts like add_expense.php, which ties in the primary key of one table (expense_categories) as a foreign key in another (expenses). However, if, when adding an expense, you'd like to choose an expense category that has not yet been created, you'll need to establish a new primary key (i.e., a new expense_category_id) before using it as a foreign key (in the new expense record). Such situations can be a challenge when using relational databases.

To demonstrate the mysqli_insert_id() function, I'll modify the PHP page that displays and handles a form for entering expenses. A pull-down menu of existing expense categories will still be created, but the user will have the option of adding a new expense category on the spot. If a new expense category is entered, it will be added and its primary key will be used as the foreign key within the expenses table.

Procedural vs. OOP

The mysqli extension of functions differs from the older mysql set in another way: they can be invoked using either a procedural or object-oriented syntax. Which you use is entirely up to you, but I've stuck with the procedural syntax in this book.

If you want to use the OOP version, that's straightforward enough. A sample section of code might look like this:

$db = new mysqli (`host', `user', `password', `databasename'); $r = $db->query(`SELECT * FROM 'tablename'); while ($row = $r->fetch_array'(MYSQLI_NUM)) { // Use $row. } $r->close(); $db->close();

There are three classes defined in this extension: mysqli, which manages the connection itself; mysqli_result, which is used for handling query results like in the preceding code; and mysqli_stmt, for working with prepared statements.

To use mysqli_insert_id():

Open add_expense.php (Script 7.3) in your text editor.

Change the validation conditional (Script 7.3, line 22) to read (Script 7.4).

[View full width]if ( (isset($_POST[`expense_ category_id']) or !empty($_POST [`expense_category'])) AND 
(strlen($_POST[`expense_amount']) > 0) AND (strlen($_POST[`expense_ description']) > 0) ) {

Script 7.4. The more complex `add_expense2.php` script (as I've called it) can use either a pull-down menu or a new entry for determining the expense category.

[View full width]
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>Enter An Expense</title> 7 </head> 8 <body> 9 <?php 10 11 // ***** add_expense2.php ***** 12 // ***** Script 7.4 ***** 13 // This page displays and handles a form for 14 // inserting records into the expenses table. 15 16 // Include the MySQL information: 17 require_once ('../mysqli_connect.inc.php'); 18 19 if (isset($_POST['submitted'])) { // If the form has been submitted, handle it. 20 21 // Check the required form fields: 22 if ( (isset($_POST['expense_category_id']) or !empty($_POST['expense_category'])) AND (strlen($_POST['expense_amount']) > 0) AND (strlen($_POST['expense_description']) > 0) ) { 23 24 // Start the main query: 25 $q = "INSERT INTO expenses VALUES (NULL, "; 26 27 // Determine if a new expense category was entered: 28 if (!empty($_POST['expense_category'])) { 29 30 //Create a second query: 31 $q2 = "INSERT INTO expense_categories (expense_category) VALUES ('" . addslashes($_POST['expense_category']) . "')"; 32 33 // Execute the second query and react accordingly: 34 if (mysqli_query ($dbc, $q2)) { 35 echo '<b><font color="green">The new expense category has been added!< /font></b><br />'; 36 $q .= mysqli_insert_id($dbc) . ", "; 37 } else { 38 echo '<b><font color="red">The new expense category was not entered into the table!</font></b><br />'; 39 $problem = TRUE; 40 } 41 42 } else { // Finish the expense_category conditional. 43 $q .= "{$_POST['expense_category_id']}, "; 44 } 45 46 // Finish the main query: 47 $q .= "'" . addslashes($_POST['expense_amount']) . "', '" . addslashes ($_POST['expense_description']) . "', NOW())"; 48 49 // Check to see if there was a problem: 50 if (!$problem) { 51 52 // Print a message indicating success or not: 53 if (mysqli_query ($dbc, $q)) { 54 echo '<b><font color="green">The expense has been added!</font></b>'; 55 } else { 56 echo '<b><font color="red">The expense was not entered into the table!< /font></b>'; 57 } 58 59 } else { // If there was a problem: 60 echo '<b><font color="red">The expense was not entered into the table because the expense_category could not be added!</font></b>'; 61 } 62 63 } else { // Print a message if they failed to enter a required field: 64 echo '<b><font color="red">You missed a required field!</font></b>'; 65 } 66 67 } else { // If the form has not been submitted, display it. 68 69 echo 'Enter an expense:<br /> 70 <form action="add_expense2.php" method="post"> 71 <ul> 72 <li>Expense Category: <select name="expense_category_id">'; 73 74 // Display the expense categories: 75 $r = mysqli_query ($dbc, 'SELECT * FROM expense_categories ORDER BY expense_category'); 76 while ($row = mysqli_fetch_array ($r, MYSQLI_NUM)) { 77 echo "<option value=\"$row[0]\">$row[1]</option>\n"; 78 } 79 80 // Tidy up (not required): 81 mysqli_free_result($r); 82 83 // Finish the form: 84 echo '</select></li> 85 or<br /> 86 <li>Enter a new expense category: <input type="text" name="expense_category" size="30" maxlength="30" /></li> 87 </ul> 88 <p>Expense Amount: <input type="text" name="expense_amount" size="10" maxlength="10" /></p> 89 <p>Expense Description: <textarea name="expense_description" rows="5" cols="40">< /textarea></p> 90 <input type="hidden" name="submitted" value="true" /> 91 <input type="submit" name="submit" value="Submit!" /> 92 </form>'; 93 94 } // Finish the main "submit" conditional. 95 96 // Close the connection (not required): 97 mysqli_close($dbc); 98 99 ?> 100 </body> 101 </html>

The change is in the first part of the conditional. Instead of just checking the $_POST[`expense_category_id'] value, the script checks it and $_POST[`expense_category']. As long as either is validthe first for an existing expense category or the second for a new expense categorythat part of the conditional will be true.

Begin rewriting the main query.

$q = "INSERT INTO expenses VALUES (NULL, ";

The query in this script will, in the end, be similar to that in Script 7.3, but it will be built differently to take into account the possibility of a new expense category being entered. On this line I have begun the query.

Add a new expense category if one was entered.

[View full width]if (!empty($_POST[`expense_category'])) {
   $q2 = "INSERT INTO expense_ categories (expense_category) VALUES (`" . addslashes
($_POST [`expense_category']) . "`)";
   if (mysqli_query ($dbc, $q2)) {
      echo `<b><font color="green"> The new expense category has  been added!</font></
b><br />';
      $q .= mysqli_insert_id($dbc) . ", ";
   } else {
      echo `<b><font color="red">The new expense category was not entered into the table! <
/font></b><br />';
      $problem = TRUE;
   }

This code is an application of that in add_expense_category.php (Script 7.2). First it checks if an expense category was entered. If so, it will add that record to the expense_categories table and use the mysqli_insert_id() function to add that expense category's primary key value to the main query. Messages are printed indicating the successful running of the query (Figure 7.13).

Figure 7.13. Dual messages reveal how the script successfully completed each database insertion.

Complete the main query.

[View full width]} else {
  $q .= "{$_POST[`expense_ category_id']}, ";
}
$q .= "`" . addslashes($_POST [`expense_amount']) . "`, `" . addslashes($_POST[`expense_
 description']) . "`, NOW())";

If no expense category was entered, the main query will be appended with the expense_category_id value, which is set by the pull-down menu based upon existing categories.

Check for problems and run the query.

[View full width]if (!$problem) {
  if (mysqli_query ($dbc, $q)) {
     echo `<b><font color="green"> ' The expense has been added! </font></b>';
  } else {
     echo `<b><font color="red">The expense was not entered into the table!</font></b>';
  }
} else {
   echo `<b><font color="red">The expense was not entered into the table because the 
expense_ category could not be added! </font></b>';
}

The $problem variable, established in Step 4, is true if the script was not able to add the new expense category (in which case, the expense itself should not entered). Otherwise, this part of the script behaves as it previously had.

Both here and in Step 4, I just check that the query runs okay rather than invoke the mysqli_affected_rows() function. This is only to simplify the code; you can certainly use that function in a conditional instead.

Add an expense_category input to the form.

[View full width]<li>Enter a new expense category: <input type="text" name="expense_category" size="30" 
maxlength="30" /></li>

In addition to adding this field, I've changed the HTML slightly by adding a bullet list so that the user sees the two possible options as selecting an expense category or adding a new one.

Save the file, upload it to your Web server, and test in a Web browser (Figures 7.14, 7.15, and 7.16).

Figure 7.14. The form is more user-friendly now because it no longer requires the user to create an expense category before entering an expense.

Figure 7.15. If no new expense category is entered...

Figure 7.16. ...the script will function as it had before.

For ease of demonstration, I've chosen to rename this script add_expense2.php to distinguish it from its predecessor. With that in mind, I also changed the form's action attribute. This is an optional change.

Tips

You must call mysqli_insert_id() immediately after an INSERT query and before running any other queries.
The mysqli_insert_id() function will return 0 if no AUTO_INCREMENT value was created.
Remember that when you are dealing with numbers in SQL, they need not be enclosed by quotation marks (in fact, they really shouldn't). For this reason, the expense_category_id values are not enclosed. I do enclose the expense_amount values, though, because they are coming from a text field and could possibly not be numeric, due to user error.