Array

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.

Related Articles

Related Questions

C# How to pass ulong argument to unit test via TestCase decorator

I have a unit test where I want to pass some arguments into the test method via the TestCase decorator. The argument I need...

Can google home turn on my smart tv?

If I purchase a Google hub and set up everything I need to use Google Home to turn my house into a smart house....

Can amazon smart plug work with google home?

I am looking to purchase some smart plugs and I see that there is an amazon branded smart plug. It is advertised to work...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Latest Tools

Bitrate Converter

Below you will find a bitrate converter. This tool will allow you to enter a bitrate value, in one of many different formats and...

Aesthetic Text Generator

There are various ways to make your social media profile seem more unique, some of which are not as easy to implement as others....

Aspect Ratio Calculator For Images

Aspect ratio is the ratio between the height and width of an image. If you want to resize an image by 100 pixels, you...

Add Text To Image

Use this free tool to add text to an image. Simply select the image file that you want to overlay text onto and you...

JavaScript Multi-line String Builder

Javascript did not always support multi-line strings. If you attempted to create a string variable using quotes, putting a line break into the source...

GUID Generator

GUID generation is relatively simple. It is a system that is used to generate unique values without having to keep track of the IDs...

Latest Posts

Latest Questions