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

My Neighbor is Stealing My Wi-Fi and It’s Driving Me Crazy!

I've realized that my neighbor has been stealing my Wi-Fi for nearly a year now! It all started when I saw duplicate devices showing...

Why is my home internet suddenly blocking websites?

I've noticed that my home internet seems to be filtering websites unexpectedly. For instance, every time I try to access certain sites, like Archive...

Can I Add Another SSD to My MSI MPG X570 Motherboard?

I've got a prebuilt PC and I'm not entirely sure how everything works, but I'm looking to add an additional SSD. My motherboard is...

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

Scavenger Hunt Team Randomizer

Planning a scavenger hunt and need to split participants into random teams? Whether you're organizing a school activity, a corporate team-building event, or a...

File Hash Generator Online – Get Instant MD5 and SHA-256 Hashes

Whether you are validating downloads, checking for corruption, or comparing files for duplicates, having a fast and secure way to generate file hashes is...

Visual CSS Editor for Modern Glass UI Effects

Modern UI design is all about clean, layered aesthetics, and few styles deliver this better than glassmorphism. If you're designing sleek user interfaces and...

Fast and Accurate Tap BPM Counter – Free Web Tool

Whether you're producing music, DJing live, or just figuring out the tempo of a song, knowing the BPM (beats per minute) can be critical....

Glassmorphism CSS Generator with Live Preview

Glassmorphism is one of the most visually striking design trends in modern UI. Its soft, frosted-glass effect adds depth and elegance to web interfaces,...

Add Custom Speech and Caption Boxes to Any Image Online

Creating comic-style images used to require complex design tools or specialist software. Whether you're making memes, teaching graphics, social media posts or lighthearted content,...

Latest Posts

Latest Questions