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

Database interaction without the ability to add data would be fundamentally limiting. In this comprehensive tutorial, we'll master inserting records into a MySQL database using prepared statements—a secure approach that protects against SQL injection attacks while maintaining code efficiency. Even when dealing with static data, prepared statements represent the professional standard and should become second nature in your development workflow.

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

   We'll begin with a straightforward INSERT statement to add data to the users table using prepared statements. While this simple example doesn't involve user input, developing muscle memory with prepared statements is crucial—they're the foundation of secure database operations and a non-negotiable skill in professional PHP development.

2. First, establish the database connection by adding the connection script at the top of the page:

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

3. Next, we'll construct the INSERT statement with parameter placeholders. Add the following code:

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

   This demonstrates the fundamental syntax of a SQL INSERT statement: specify the target table and columns, then define the values. The question mark placeholders (?) are essential for prepared statements, creating a template that separates the SQL structure from the actual data—a critical security measure.

4. Create variables to store the data parameters. This step is mandatory because the bind_param() function requires variable references, not literal strings:

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

5. Initialize and prepare the statement object for execution:

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

   The preparation phase compiles the SQL statement and optimizes it for execution, while maintaining the separation between code and data that makes prepared statements so secure.

6. Bind the parameters to their respective placeholders:

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

   The first parameter 'sss' specifies data types: each s represents a string. This type specification allows MySQL to optimize storage and validates data integrity. Match the number of type specifiers to your parameters—three variables require three type indicators.

7. Execute the prepared statement and implement error handling:

   $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 and implements basic error detection. If an error occurs, the code outputs both the error number ($stmt->errno) and a human-readable description ($stmt->error). A typical error might read: "2031: No data supplied for parameters in prepared statement"—invaluable for debugging during development.

8. Save the file and test it in your browser:

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

9. A successful execution displays a blank page with no error messages. While visually unremarkable, this represents successful database manipulation—your INSERT statement has executed and added the record to the users table.

Viewing the Table in PhpMyAdmin

Now let's verify our database operations using phpMyAdmin, the web-based MySQL administration tool that provides a visual interface for database management.

1. Access phpMyAdmin through your development environment:

   Mac Users:
   • Switch to MAMP PRO
   • Click the WebStart button
   • In the control panel, navigate to Tools and select phpMyAdmin
   Windows Users:
   • Open your browser and navigate to http://localhost
   • In the XAMPP control panel, locate the Tools section and click phpMyAdmin

2. In the phpMyAdmin interface, click phpclass_yourname in the left sidebar to access your database.

3. Locate the users table and click the Browse icon to view all records:

   

   You should see all existing database records, including the George Washington entry you just inserted. This visual confirmation validates that your INSERT operation executed successfully.

Inserting Information from a Form

Now we'll tackle a more realistic scenario: processing user-submitted form data and inserting it into the database. This represents the typical workflow in web applications where users provide information through HTML forms. We'll work with a pre-built form that includes validation and input sanitization—essential security measures for production applications.

1. In phpMyAdmin, click phpclass_yourname to return to your database overview.

2. Access the table structure by clicking the Structure icon next to the users table:

   

3. We need to expand the table structure to accommodate additional form fields. In the Add field, enter 3 and click Go:

   

4. Configure the new fields with the following specifications:

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

   Understanding these field types is crucial for efficient database design:

   • publications: A VARCHAR(255) field suitable for storing publication names or short lists. VARCHAR is ideal for variable-length strings up to the specified limit.
   • comments: A TEXT field designed for longer content blocks. TEXT columns can store up to 65,535 bytes (approximately 64KB)—sufficient for substantial user comments. For applications requiring larger storage capacity, consider MEDIUMTEXT (16MB) or LONGTEXT (4GB).
   • subscribe: A TINYINT(1) field perfect for boolean values. TINYINT uses minimal storage while providing reliable true (1) or false (0) functionality.
   • NULL values: All fields allow NULL to distinguish between "no response" and "empty response"—an important data integrity concept. For instance, NULL in publications means the user skipped the question entirely, while an empty string means they answered but selected no publications.

5. Click Save to add the new columns to your table structure.

Setting up the PHP

With our database structure updated, let's implement the PHP code to handle form submission and data insertion. This process demonstrates real-world form processing techniques used in professional web applications.

1. Return to your code editor to begin the implementation.

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

3. Preview the form in your browser:

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

4. Complete the form with sample data and click Sign me Up!

   This form includes pre-built validation and input sanitization—critical security measures that clean user input before database insertion. Our task is to add the database insertion functionality to this existing, secure foundation.

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

   This file contains the validation logic that processes and sanitizes form submissions. We'll integrate our database insertion code here.

6. Navigate to approximately line 66 and locate the comment:

   //insert into database

7. Below this comment, add the include statement:

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

   This modular approach separates concerns—keeping validation logic separate from database operations for better code organization and maintainability.

8. Save and close form-action.php.

9. Create a new file and save it as form-insertUser.php in the form-insert folder.

10. Begin the file with the database connection requirement:

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

    Note the relative path (../) since we're in a subdirectory relative to the database connection file.

11. Define the INSERT statement for all form fields:

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

    This expanded INSERT statement handles all six fields from our form. The question mark placeholders maintain the prepared statement security model while accommodating the additional data fields.

12. Initialize the statement object:

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

13. Bind the parameters with appropriate data type specifications:

    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 type string 'sssssi' specifies five strings followed by one integer. The variables referenced here are created by the validation script in form-action.php, which sanitizes the original $_POST data and assigns it to clean variable names.

14. Execute the statement with comprehensive error handling:

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

    This error handling approach immediately displays any SQL errors and halts script execution—essential for identifying and resolving database issues during development.

15. Save the file and test the complete workflow:

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

16. Complete all form fields and submit. You'll see the thank you page, but notice a PHP notice at the top:

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

    This notice indicates we're attempting to insert an array where a string is expected. The checkbox field "What do you read?" returns an array of selected values, but our database expects a string. The page continues executing because this is a notice (warning) rather than a fatal error.

17. Let's examine what's being stored in the database. Return to phpMyAdmin:

    Mac:
    • Switch to MAMP PRO
    • Click WebStart
    • Navigate to Tools → phpMyAdmin
    Windows:
    • Go to http://localhost
    • Click phpMyAdmin in the Tools section

18. Click phpclass_yourname to access your database.

19. Click the Browse icon for the users table.

20. Examine the most recent entry's publications column—it displays "Array" instead of the actual publication names. We need to convert the array into a readable, comma-separated string.

21. Return to your code editor and open form-insertUser.php.

22. Add array processing logic at the beginning of the file. The form-action.php script provides an $expected array containing all form field names, which we'll use to process potential arrays:

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

    This loop iterates through each expected form field name.

23. To understand the loop's output, temporarily add debugging code:

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

24. Test this debug output:

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

25. Submit the form to see the field names displayed:

    firstName
    lastName
    email
    publications
    comments
    subscribe

    This confirms our loop is accessing each form field name correctly.

26. Replace the debug code with array detection logic:

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

    The ${$value} syntax uses variable variables—it converts the string value (e.g., "publications") into a variable name (e.g., $publications) for evaluation.

27. Implement array-to-string conversion using PHP's implode() function:

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

    The implode() function converts arrays into strings using a specified delimiter. Here, we're creating comma-separated lists that are database-friendly and human-readable.

28. Test the complete implementation:

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

29. Complete the form, selecting multiple options under "What do you read?" The submission should now display only the thank you page without errors or notices.

30. Verify the results in phpMyAdmin:

    Mac:
    • Open MAMP PRO
    • Click WebStart
    • Navigate to Tools → phpMyAdmin
    Windows:
    • Go to http://localhost
    • Access phpMyAdmin from Tools

31. Navigate to phpclass_yourname and browse the users table.

32. The publications column should now display properly formatted, comma-separated values such as:

    Daily Racing Form, Elle

    This demonstrates successful array processing and database insertion.

33. Return to your code editor and close all files—you've successfully implemented secure form processing with database insertion.

You've now mastered the essential skills of database insertion using prepared statements, from simple static data to complex form processing with array handling. These techniques form the foundation of secure, professional web application development and represent industry-standard practices for database interaction in modern PHP applications.