Overview of This Lesson

Refresh this page because I am probably still making changes to it.

Resources

In the previous lesson we learned how to use a connection string to connect to our database. Recall that the PDO() constructor takes our connection string and returns a database connection object that we can use to execute commands on our database:

<?php
    // connect.php

    require_once("config.php");

    function connect($host, $db, $user, $pass) {
        try {
            return new PDO("mysql:host=$hostname;dbname=flintsto_Summer22", $user, $passwd);
        } catch (PDOException $ex) {
            exit("Connection error: " . $ex->getMessage());
        }
    }
    return connect($hostname, "login_Summer22", $user, $passwd);
?>

We also learned how to prepare and execute query statements using the PHP prepare() and execute() methods:

<?php
    $statement = $dbConn->prepare($sqlQuery);
    $execOk = $statement->execute();
    if ($execOk) {  
        // do whatever you like with the results
    } else {
        // error executing query:
        print_r($dbConn->errorInfo());  // for debugging
        $statement->debugDumpParams();  // for debugging
    }
?>

Let's now apply this by using these code segments to create and execute INSERT statements on our database.

Basic SQL INSERT Queries

INSERT queries add records to your table. A basic INSERT query has the following syntax:

INSERT INTO tableName (fieldName1, fieldName2, ..., fieldNameN)
VALUES (value1, value2, ..., valueN);

In the query, the fieldName items represent the names of the fields/columns in which you want to insert values. The value items represent the actual values going into each of the fields. The values must go in the same order as the fields: value1 goes into fieldName1, value2 goes into fieldNamew, etc. Here's an example:

INSERT INTO Music (artist, title, price)
VALUES ("Dropkick Murphys", "Rose Tattoo", 10.0);

The statement above will insert a new record into the Music table with the value "Dropkick Murphys" in the artist field, the value "Rose Tattoo" in the title field, and the value 10.0 in the price field. Note that String values must be enclosed in single or double quotes if you're typing the query out by hand.

You can try an INSERT query in PHPMyAdmin: Go to your Inventory table and click on the SQL tab. Type your INSERT query in the text field. For example, try this:

INSERT INTO Inventory (name, price, quantity) VALUES ("Strawberries, litre", 7.50, 14);
entering a query using PHPMyAdmin SQL tab
Enter your query in the text field

To execute the query, click the GO button in the bottom-right corner. If you typed the query correctly with correct syntax, you'll see that it executed successfully and one row or record was inserted into your inventory table.

message: 1 row inserted; inserted at row id 3
Results of a successful insert query

You can click the Inventory table in the left-hand column to verify that your record was added.

Parameterized Queries

Often, the values that you want to use in your INSERT query come from form input data. For example, perhaps you want to have the user enter some inventory data on a form like this one:

<form name="inv" method="post">
  <div><label for="name">Item Name:
  <input type="text" id="name" name="name" required
    size="50" maxlength="255"></label>
    </div>
  <div><label for="weight">Price:
  <input type="text" id="price" name="price" size="5" 
    required pattern="(\d*\.)?\d+"></label>
  </div>
  <div><label for="qty">Quantity
    <input type="number" id="qty" name="quantity" min="1" 
      max="100" value="10" required ></label>
  </div>
    
  <div><button type="submit">Submit</button>
    <button type="reset">Reset</button>
  </div>
</form>

Once the user submits the form, you want to use the form data in an INSERT query (once you confirm the data is valid, of course).

On the server side, you might be tempted to create an INSERT query by concatenating the valid input values into the statement:

$sql = "INSERT INTO inventory (name, price, quantity) 
    VALUES ('$name', $price, $quantity);";

When retrieving data from the user for SQL statements, you must be careful of SQL Injection Attacks. Injection attacks occur when a user attempts to inject malicious SQL code by adding it to your input fields.

For example, let's say the user enters "foo" for the item name, 2 for the quantity, and the following in your price text field:

0); DELETE FROM inventory;

When the $price value is parsed into the SQL statement, you get:

INSERT INTO inventory (name, price, quantity) VALUES 
('foo', 2, 0); DELETE FROM inventory;

When this is executed by the SQL server, it will first do the INSERT statement, then it will execute the DELETE statement, which will delete all the records in your inventory table!

Even if a user didn't know the names of your tables, they can still do harm. Imagine you were querying your users table for a valid login entered by a user. Your form probably has a user name and password field. Someone could type ' OR 1' in the field. This would build the query:

$sql = "SELECT * FROM users WHERE username='$username';";

as...

SELECT * FROM users WHERE username='' OR 1'';";

Since the expression "1" is always true, the select query will return all the records in the users table (the ending '' would be ignored since it's just a null value)!

To prevent SQL Injection attacks, you need to make sure any special characters that are normally used for SQL statements are escaped. This would include the single-quotes, double-quotes, and a variety of other special characters. PDO's prepared statements allow you to use parameterized queries. These are SQL queries that use parameters instead of variables. You then use special functions to add the values that go into the parameters. These values are escaped automatically so that any quotes or special characters become part of the value (e.g. a " becomes \") so that injection attacks become impossible.

There are a number of ways to create a parameterized query, depending on what you want and what kind of data you're working with.

The first thing you need to do is build your query and substitute variables or inputs with parameters. There are 2 types of parameters you can use:

  1. ? (the question mark) - this is the generic (un-named) parameter symbol. The first ? is considered parameter 1, the next is parameter 2, the next is parameter 3, etc.
  2. :paramName (a parameter name, preceeded by a colon) - this is referred to as a named parameter. Some find it easier to use named parameters because you don't have to remember what order they're in, like you do with un-named parameters.

Here's an example of our INSERT query using both un-named and named parameters:

INSERT INTO inventory (name, price, quantity) VALUES (?, ?, ?);
INSERT INTO inventory (name, price, quantity) VALUES (:name, :price, :qty);

The next step is to bind the parameter values to the actual named or un-named parameters in the SQL statement.

Recall that the execute() method we learned in the previous section executes an SQL query. It receives a prepared statement object for the query to execute, but can also receive an array of values that should replace any parameters in the query. Any quotes required around the values (e.g. for String values) will automatically be added, so they don't need to be part of your SQL statement. The execute() function will escape any special characters in your input values and apply them to the query, then execute the query. It returns a result based on the type of query that was executed. For example, with INSERT queries, it returns the number of records/rows that were successfully inserted.

<?php
  $sql = "INSERT INTO inventory (name, price, quantity) VALUES (?, ?, ?);";
  $statement = $dbConn->prepare($sql);
  $params = array($name, $price, $quantity);  // or [] instead of array()
  $result = $statement->execute($params);
?>

Or, if using named parameters:

<?php
  $sql = "INSERT INTO inventory (name, price, qty) VALUES (:name, :price, :qty);";
  $statement = $dbConn->prepare($sql);
  $params = array(    // or $params = [
    ":name" => $name, 
    ":price" => $price,
    ":qty" => $quantity
    
  );  // or ] if you used $params = [ above
  $result = $statement->execute($params);
?>

If you like, you don't neve need to save the parameters to an array, you can do it dynamically:

<?php
    $sql = "INSERT INTO inventory (name, price, quantity) VALUES (:name, :price, :qty);";
    $statement = $dbConn->prepare($sql);
    $result = $statement->execute([   // note the square brackets = array
        ":name" => $name, 
        ":price" => $price,
        ":qty" => $quantity
       ]);  // close both square and round brackets
  ?>

The code segments above create the SQL query, prepare a statement, then create an array out of three user inputs (name, quantity, price). Then we pass the array to the execute method, which puts the array values into the prepared statement, and then executes the statement. Note that when using un-named parameters, the array values must be in the exact same order as the parameters. For example, name is the first element of the $params array, so it will go in the first parameter. Note also that when you're using named parameters, you must use an associative array for $params that uses the named params as keys and their values as values.

Processing Results of an Insert Query

When you perform an insert, update, or delete query on a database, the database usually responds with a message such as "X Rows Affected" where "X" is the number of records that were inserted/updated/deleted. In PHP, if you need to know the number of rows affected after executing an insert, update, or delete query you can use the PDOStatement's rowCount() function.

You can use this to make sure your query was successful:

<?php
    $sql = "... a query that deletes several records at once...";
    $statement = $dbConn->prepare($sql);
    $result = $statement->execute($params);
    if ($result) {
        echo "<p>".$statement->rowCount()." Record(s) Deleted Successfully</p>";
    } else {
        echo "<p>Error deleting records.  Contact system administrator.</p>";
    }
?>

Try putting all the pieces together with this basic demonstration:

  1. Create a page index.php that contains a form with fields for the inventory item name, price, and quantity. All three fields are required. Price should be a valid floating point value. Quantity should be a valid integer. The action attribute should also be set to execute the newInventory.php page: it would make sense that each time a user inserts a record, they will likely want to insert another new record immediately after. Since we're performing database access, we should use the POST method on our form.
  2. Add the following PHP code at the top of the page:
    1. Use your connect.php to get a PDO connection object, if the object doesn't already exist.
    2. If this page was requested with a POST request (I gave you a hint about this in a previous lesson), retrieve and validate the inputs:
      • name should be sanitized and it should contain a string with 1 or more characters but no more than 255. If the name is too big, truncate it to 255 characters; if the name is less than 1 character, it's an error.
      • price should be sanitized/validated as a floating point value that is 0 or greater
      • quantity should be sanitized/validated as an integer value that is greater than 0
    3. If there are no errors, insert the data as an inventory record into the Inventory table of your database.
  3. If there are any errors, display appropriate error messages above the form. Errors should only appear if they are relevant to the most recent set of inputs.

If you get stuck, you can check your answer and compare it to my suggested answer. Note that this shows only one way to do this task! There are several alternative solutions!

code for insert exercise
code for insert exercise
code for insert exercise
code for insert exercise
code for insert exercise
code for insert exercise
code for insert exercise

Exercises

1. In the previous lesson you added the Courses table. Create a form on a page newCourse.php that allows a user to enter the data for a course. The form action should be set to insertCourse.php. When the form is submitted, ensure the data is valid. If there are any errors, display them on the form page. If there are no problems, insert a new record into the Course table.

form with fields for course code, title, credits, grade
New Course Form

After a record is inserted, display the entire course record that was just added. Your results page should also include a link that goes back to the newCourse.html form.

newly added course info
New Course Successfully added to Courses table

2. You can easily create several similar programs: Think of some data you'd like to keep track of: your books or games? Your contacts? Your homework assignments? A grocery list? Your budget?

Create a datbase table for the thing you want to track.

Create a form so you can input each value.

Write the code to insert a record when the user submits the form.