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:
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:
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:
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.
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:
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:
? (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.
: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, 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 even 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:
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.
Add the following PHP code at the top of the page:
Use your connect.php to get a PDO connection object,
if the object doesn't already exist.
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
If there are no errors, insert the data as an inventory record into
the Inventory table of your database.
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!
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.
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.
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.
