SQL Prepared Statements Using PHP PDO Queres

In this tutorial I am going to go through all the basic SQL commands using PDO prepared statements. The use of prepared statements is 1 very simple way you can try and defend against SQL Injection.

First thing you want to do is set up a connection to the database. The code for this is more or less the same as always.

$db = new PDO('mysql:host= host-name ;dbname= db-name ;charset=UTF-8', 'username', 'password');

SELECT

If you wish to query a database for a single row Eg. During a user login to check password and user name you can run the query and check the result very easily to see if it returned a value. Rather than insert the variables directly into the SQL string like usual you use placeholders (:Placeholder). The reason this is good is because the system now knows that whatever data is assigned to the placeholders (Eg. Malicious SQL code) that this code is not a part of the SQL statement and therefore won’t be executed as SQL code.

$stmt = $db->prepare("SELECT * FROM table-name WHERE Username=:username AND Password = :password");
$stmt->execute(array(':Username' => $Username, ':password' => $Password));
$row = $stmt->fetch();

Now all you have to do is to check if it returned a result and if it did display the username. If you want to output any other details from the returned user simply put the column name inside the square brackets just as you would for POST or SESSION variables. $row is either true or false if true then you know the result was returned.

if($row)
{
    echo $row['Username'];
}

When you want to return multiple results Eg. Print out the names of all the registered users in the database. This time there are no variables to needed in the SQL statement so no need to have the array of variables inside the execute. When there are multiple results use the $stmt->fetchAll() function.

$stmt = $db->prepare("SELECT * FROM table-name");
$stmt->execute();
$result = $stmt->fetchAll();
foreach( $result as $row ) 
{
        echo $row["Username"];
}

If for any reason you need to get the row count $numRows = $stmt->rowCount(); will return the number of rows.

INSERT

The insert command works the exact same way as above simply change the string for the SQL command.

$stmt = $db->prepare("INSERT INTO table-name(UserName, Password, Email) VALUES(:UserName, :Password, :Email)");
$stmt->execute(array(':UserName' => $UserName, ':Password' => $Password, ':Email' => $Email));

If you need to get the auto-incremented ID for the last inserted row rather than have to perform a SELECT use $db->lastInsertId(); to return the ID for the row you just inserted.

DELETE

DELETE is just like insert nothing new and fancy.

$stmt = $db->prepare("DELETE FROM table-name WHERE Username=:username ");
$stmt->execute(array(':username' => $Username,));

UPDATE

Updates are the same as you might expect by now.

$stmt = $db->prepare("UPDATE table-name SET Username= :username WHERE UserID= :userid");
$stmt->execute(array(':username' => $Username, 'userid' => $UserID));

JOIN

Using joins can be confusing at the best of times but once you can get your head around them they are great to know as they can cut the amount of queries on a page down significantly. With a bit of thought the concept of it becomes very simple. When 2 tables have a relationship Eg. Users table and a Comments table, 1 User leaves many comments so if you wanted to display all the comments on a page and include the username of the person who made the comment rather than have to query the users table and the comments table use a join. (Putting a username field in the comments table is a solution but data redundancy is a bad choice and since joins are quite easy to do there is no reason to do this).
In order for you to be able to do a join in the first place there needs to be a foreign key relationship (doesn’t need to be specified in the DB there just simply needs to be a reference Eg. Comments table has a UserID column which references the UserID primary key in the Users table).

$stmt = $db->prepare("SELECT * FROM Comments JOIN Users ON Comments.UserID = Users.ID WHERE Comments.PostID = :id");
$stmt->execute(array(':id' => $PostID));
$result = $stmt->fetchall();

This code will return objects that are a combination of rows from the users table and rows from the comments table. Accessing this data is the same as before using the $row object except now you can use the column names from both tables to get the data. Take note that you will need to use the ‘AS’ value if there are duplicate column names.