SQL: Insert: Free PHP & MySQL Tutorial

Topics covered in this PHP & MySQL tutorial:

The INSERT statement, Using phpMyAdmin, Inserting information from a form

Exercise Overview

Interacting with a database wouldn’t be much use if we couldn’t add data to it. Here we’ll learn how to insert records into a database using prepared statements.

1. Open insert-easy.php from the phpclass folder.

   We’re going to write a simple INSERT statement to add some data to the users table. We’ll use prepared statements. Although it’s not necessary for this simple example because we won’t be accepting user input, it’s still good practice to get used to writing them.

2. First we need to add the connection script: At the top of the page, add the following bold code:

   <?php 
   
      require_once('inc/dbConnect.php');
   
   ?>
   

3. Next, we’ll add the INSERT statement. Add the bold code:

   require_once('inc/dbConnect.php');
   
   $sql = "INSERT INTO users (firstName, lastName, email)
          VALUES (?, ?, ?)
          ";
   

   This is the basic syntax of a SQL INSERT statement. It says “insert firstName, lastName, and email into the users table, with the following values.” Because we are using prepared statements we use ? placeholders.

4. We have to make variables to hold our firstName, lastName, and email parameters. This is necessary because the bind_param() function we will use later only accepts variables—not strings. Add the bold code:

   $sql = "INSERT INTO users (firstName, lastName, email)
           VALUES (?, ?, ?)
           ";
   
   $firstName = 'George';
   $lastName = 'Washington';
   $email = 'george@foundingfathers.gov';
   

5. Next we need to initialize the statement and prepare the SQL. Add the bold code:

   $firstName = 'George';
   $lastName = 'Washington';
   $email = 'george@foundingfathers.gov';
   
   $stmt = $conn->stmt_init();
   $stmt->prepare($sql);
   

6. Then we bind the parameters. Add the bold code:

   $firstName = 'George';
   $lastName = 'Washington';
   $email = 'george@foundingfathers.gov';
   
   $stmt = $conn->stmt_init();
   $stmt->prepare($sql);
   
   $stmt->bind_param('sss', $firstName, $lastName, $email);
   

   Remember, the 'sss' in the first portion of bind_param() specifies that each variable is a s tring. There are three s’s because there are three variables.

7. The statement is all prepped and ready to go. We’ll execute it and add some basic error-checking. Add the bold code:

   $stmt = $conn->stmt_init();
   $stmt->prepare($sql);
   
   $stmt->bind_param('sss', $firstName, $lastName, $email);
   $stmt->execute();
   if ($stmt->error) {
       echo $stmt->errno . ": " . $stmt->error;
   }
   

   This executes the query, then checks if there is an error. If there is an error, it outputs the error number ($stmt->errno) and concatenates it with a colon followed by the plain-English error ($stmt->error). (If there was an error the output would read something like: 2031: No data supplied for parameters in prepared statement.)

8. Save the page and then in a browser go to:

   • Mac: localhost:8888/phpclass/insert-easy.php
   • Windows: localhost/phpclass/insert-easy.php

9. If everything works correctly you’ll see a blank page with no errors. Not terribly exciting but we’ve worked some SQL magic in the background and added some data to the database.

Viewing the Table in phpMyAdmin

1. Getting to phpMyAdmin is slightly different whether you’re on a Mac or a PC. Follow the appropriate instructions for your platform:

   Mac
   • Switch to MAMP PRO.
   • Click the WebStart button.
   • In the page that opens, click on Tools and choose phpMyAdmin.
   Windows
   • Open a browser and go to http://localhost
   • On the start page, in the Tools section click phpMyAdmin.

2. On the left, click phpclass_yourname to go to your database.

3. You’ll see the users table. As shown below, click the Browse icon to view the records in the database.

   

   You should see all the rows in the database listed, including the George Washington row you just inserted.

Inserting Information from a Form

We’ll now show a slightly more realistic situation and insert user information that is submitted from a form. We’ve already made the form and set up basic validation on the form data. We’ll take this already sanitized input and put it into the database.

1. At the top, click phpclass_yourname to go to your database.

2. You’ll see the users table. As shown below, click the Structure icon to alter the structure of the table.

   

3. You’ll see the structure of the table. We want to add 3 fields at the end of the table. As shown below, enter 3 into the Add field, then hit Go.

   

4. Set the following:

   Name	Type	Length/Values	null
   publications	VARCHAR	255	checked
   comments	TEXT		checked
   subscribe	TINYINT	1	checked

   To break this down:

   • publications will be a VARCHAR field with a max length of 255 characters.
   • comments will be a TEXT field. TEXT fields are for storing longer blocks of text such as what may be entered into a comments field. The maximum length for a TEXT column is 65,535 bytes, or around 64kb. That’s a decent length of text. For applications that require more storage (such as a content management system) you can use MEDIUMTEXT (stores around 16MB) or LONGTEXT (stores around 4GB).
   • subscribe will be a TINYINT field with a max length of 1. TINYINT is good for storing very small numbers. In this case subscribe will only be true (1) or false (0) so we want to use the smallest size possible.
   • NULL: Because all of the fields are optional we want the option of allowing NULL values. A NULL value can be thought of to mean that the user didn’t answer the question. In the case of our form, for example, we ask which publications they read. If they don’t answer the question at all the field should have NULL value. If they did answer the question but didn’t choose any of the values then that could mean that they don’t read any of the publications we list. A subtle distinction, but an important one!

5. In the bottom right, click Save. Your columns will be added.

Setting Up the PHP

1. Switch back to your code editor.

2. Open form.php from the form-insert folder.

3. In a browser go to:

   • Mac: localhost:8888/phpclass/form-insert/form.php
   • Windows: localhost/phpclass/form-insert/form.php

4. Fill out the form and click Sign me Up!.

   This page already has the validation and input sanitization programmed for you. All we need to do is write the code that adds a record to the database.

5. Open form-action.php from the form-insert folder.

   This is the page that contains the validation code. We’ll add the insert code to this page.

6. Scroll down to around line 66 and find the comment:

   //insert into database
   

7. Below that line, add the following bold code:

   //insert into database
   require_once('form-insertUser.php');
   

8. Save the page.

9. Close it, we’re done with it for now.

10. Create a new file.

11. Save it as form-insertUser.php into the form-insert folder.

12. The first thing to do in this new file is link to the database connection script we made earlier. Add the following bold code:

    <?php 
    
       require_once('../inc/dbConnect.php');
    
    ?>
    

13. Next we’ll write the SQL INSERT statement. We’ll add the columns that we just made to the statement. Add the following bold code:

    require_once('../inc/dbConnect.php');
    
    $sql = "INSERT INTO users (firstName, lastName, email, publications, comments, subscribe)
           VALUES (?, ?, ?, ?, ?, ?)
           ";
    

    This will insert our values into the users table. Remember the ? are all placeholders for the prepared statement we are writing. We save the SQL string into a variable called $sql.

14. Next we have to initialize the connection and prepare the $stmt object. Add the following bold code:

    require_once('../inc/dbConnect.php');
    
    $sql = "INSERT INTO users (firstName, lastName, email, publications, comments, subscribe)
         VALUES (?, ?, ?, ?, ?, ?)
         ";
    
    $stmt = $conn->stmt_init();
    $stmt->prepare($sql);
    

15. Now we bind the parameters using the bind_param() function. First specify what type of data we are inserting (in this case it will only be strings or integers), and what the name of the variables are. Add the following bold code:

    require_once('../inc/dbConnect.php');
    
    $sql = "INSERT INTO users (firstName,lastName,email,publications,comments,subscribe)
         VALUES (?, ?, ?, ?, ?, ?)
         ";
    
    $stmt = $conn->stmt_init();
    $stmt->prepare($sql);
    $stmt->bind_param('sssssi', $firstName, $lastName, $email, $publications, $comments, $subscribe);
    

    The 'sssssi' specifies the data of each variable. An s represents s tring, and an i represents i nteger. The variables names come from the validation script we wrote. Notice that they are not $_POST variable but instead have their own names. The form-action.php script gives them these names and sanitizes the user input.

16. Finally, we can execute the statement and check for errors. Add the following bold code:

    $stmt->prepare($sql);
    $stmt->bind_param('sssssi', $firstName, $lastName, $email, $publications, $comments, $subscribe);
    $stmt->execute();
       if ($stmt->error) {
          echo $stmt->error;
          exit();
       }
    

    This executes the $stmt object and checks for an error. If there is an error it will output the error information and abort the script.

17. Save the page.

18. In a browser go to:

    • Mac: localhost:8888/phpclass/form-insert/form.php
    • Windows: localhost/phpclass/form-insert/form.php

19. Fill out the entire form and click Sign Me Up!.

    You’ll see the thank you page, but notice that at the top there is a notice such as:

    Notice: Array to string conversion in /Applications/MAMP/htdocs/phpclass/form-insert/form-insertUser.php on line 12
    

    Notice also that the page did not stop executing even though we said to exit() if there was an error. Technically this was not an error, but a notice. A notice tells you that you’re doing something not totally correct, but it is correct enough to let the page still execute.

    In our case, we are inserting an array where a string should be. The What do you read? checkbox selector in the form is an array, but in the script we are inserting it as a string.

20. Let’s go to phpMyAdmin and see what’s being inserted into the database.

    Mac
    • Switch to MAMP PRO.
    • Click the WebStart button.
    • In the page that opens, click on Tools and choose phpMyAdmin.
    Windows
    • Open a browser and go to http://localhost
    • On the start page, in the Tools section click phpMyAdmin.

21. On the left click phpclass_yourname to go to your database.

22. You’ll see the users table. Click the Browse icon to view the records in the database.

23. You’ll see all the records in the database. Scroll down to the last entry and look in the publications column. It will say Array. It should be listing the publications they read instead. What we’ll do is use a function to convert the array into a list.

24. Switch back to your code editor.

25. You should still be in form-insertUser.php.

26. We want to loop through the array of form values and check if each value is an array. If it is an array, convert it to a list string. Luckily the form-action.php script already has an array of all the form values called $expected. We’ll loop through this array. At the top of the page add the following bold code:

    <?php 
    
       foreach ($expected as $value) {
    
       }
    
       require_once('../inc/dbConnect.php');
    

    This loops through the $expected array.

27. First let’s see what this loop is outputting. Add the following bold code:

    foreach ($expected as $value) {
       echo $value;
       echo '<br>';
    }
    

    This outputs $value and adds a line break after each entry.

28. Save the page and then in a browser go to:

    • Mac: localhost:8888/phpclass/form-insert/form.php
    • Windows: localhost/phpclass/form-insert/form.php

29. Fill out the entire form and submit it.

    You’ll see a list of the names of each form field:

    firstName
    lastName
    email
    publications
    comments
    subscribe
    

    Notice that each one is just the name of the field.

30. Switch back your code editor.

31. Now we can check to see if $value is an array. Delete the two echo lines we just added and replace them with the following bold code:

    <?php 
    
       foreach ($expected as $value) {
          if ( is_array(${$value}) ) {
    
          }
       }
    
       require_once('../inc/dbConnect.php');
    

    ${$value} takes the string that $value outputs and evaluates it like a variable. So publications becomes $publications.

32. Finally we can use the function implode() to take the array and convert it to a list. Add the following bold code:

    foreach ($expected as $value) {
       if ( is_array(${$value}) ) {
           ${$value} = implode(", ", ${$value});
       }
    }
    

    This says to redefine the variable to a list. implode() takes two parameters, the first is the delimiter. In our case we want a comma-delimited list followed by a space. The second is the array variable we want to implode.

33. Save the page and then in a browser go to:

    • Mac: localhost:8888/phpclass/form-insert/form.php
    • Windows: localhost/phpclass/form-insert/form.php

34. Fill out the entire form and under What do you read? check on both options. You shouldn’t see any errors or notices—only the thank you page.

35. Go to phpMyAdmin:

    Mac
    • Switch to MAMP PRO.
    • Click the WebStart button.
    • In the page that opens, click on Tools and choose phpMyAdmin.
    Windows
    • Open a browser and go to http://localhost
    • On the start page, in the Tools section click phpMyAdmin.

36. On the left, click phpclass_yourname to go to your database.

37. You’ll see the users table. Click the Browse icon to view the records in the database.

38. Now in the publications column you shouldn’t see Array, but will instead see a nice comma-delimited list of publications such as:

    Daily Racing Form, Elle
    

39. Switch back to your code editor.

40. Close any open files. We’re done for now.