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

Is the Gigabyte RTX 5070 Ti WindForce OC Worth $870?

Hey everyone! I'm considering buying a Gigabyte RTX 5070 Ti WindForce OC 16GB that I found for $870 plus tax at Best Buy. It...

Looking for Advice on My New PC Build After Finishing My Apprenticeship

Hey everyone! I just wrapped up my apprenticeship and decided to treat myself to a new PC build. I've put together a list on...

Can I Easily Fix a Bricked GPU from a BIOS Flash?

Hey everyone, I came across a PNY Verto 4070 ti being sold for $300, but there's a catch: the seller claims they messed up...

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

Online Hash Generator – String to Hash Converter

Need to quickly generate a hash from a string? Whether you're verifying file integrity, securing data, or just experimenting with cryptographic tools, this simple...

Convert CSV To HTML Table

Need to quickly turn CSV data into an HTML table? Whether you're copying data from Excel, Google Sheets, or another spreadsheet, this tool makes...

Student Group Randomizer

Creating fair and balanced groups in the classroom can be time-consuming — especially when you're trying to avoid repetition, manage different skill levels, or...

Random Group Generator

Need to split a list of people, items, or ideas into random groups? Our free Random Group Generator makes it quick and easy. Whether...

Flip Text Upside Down – Free Online Tool

Ever wanted to flip your text upside down just for fun or to grab someone’s attention in a creative way? This free online Upside...

Raffle Ticket Generator

If you're running a fundraiser, charity draw, or local event and need raffle tickets fast, this free online tool lets you generate and print...

Latest Posts

Latest Questions