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

Function Keys Reversing Between Fn Actions And Normal

My keyboard has the usual F1 to F12 keys along the top. I use these for shortcuts in various applications. These keys also have...

Whirlpool Oven F6E6: Appliance Manager 1 Board Communication

I have a brand new Whirlpool oven W11I OM1 4MS2 H or (859991549450). I bought it alongside the microwave combi oven. I have had...

Whats the difference between the Tapo P100 and the P105?

There are a few different Tapo smart plugs. The P100 and P110 differ based on the smart power monitoring feature but where does the...

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

AI Content Detector

We've got this awesome free tool that'll help you figure out if that content you're looking at was written by a human or some...

Image Saturation

Are you looking for an easy-to-use, free app to modify your image saturation levels and make your pictures truly pop? Look no further! Our...

Pixelate Image Tool

Introducing the ultimate free online image pixelator tool that allows you to easily transform your images into stunning pixel art in just a few...

Image RGB Level Adjustment Tool

Introducing the ultimate image color adjustment tool for all your photo editing needs. Our free online tool lets you take full control of your...

Image Color Inverter

Looking for a quick and efficient way to convert your images into negatives? Our Free Image to Negative Converter is the answer! Our online...

Negative Image to Color Image Converter

Welcome to our Negative Image to Color Image Converter, a free and easy-to-use tool that helps you convert your old negative images into vibrant,...

Latest Posts

Latest Questions