40: What are Prepared Statements and how to use them | PHP tutorial | Learn PHP programming

What are Prepared Statements and how to use them. Today we will learn how to connect to our database using Prepared Statements, which is also a better method of preventing SQL injection. The basic idea behind Prepared Statements, is to create placeholders in our SQL statements when we send them to our database. Then later we fill in the placeholders with parameters that the user send from a form.

➤ GET ACCESS TO MY LESSON MATERIAL HERE!

First of all, thank you for all the support you have given me!

I am really glad to have such an awesome community on my channel. It motivates me to continue creating and uploading content! So thank you!

I am now using Patreon to share improved and updated lesson material, and for a small fee you can access all the material. I have worked hard, and done my best to help you understand what I teach.

I hope you will find it helpful :)

[ammarsiddiqui]

These small and interactive lessons are the best way to learn PHP and I think we need to start showing these videos in schools these days. Thank you for your effort in creating these tutorials, I learn more from you than I do from my Computer Science teachers.

[eb]

EXPLANATION !

It is a long text, but I hustled through the information out there myself just to make it easy for you guys.
Prepare yourself to read it slowly and try to understand every sentence:

The function "mysqli_stmt_init($conn)" creates (="instantiates") and returns an object of type "mysqli_stmt"
which Daniel stored in that variable "$stmt". It is still a "virgin" and not preparded yet but is instantiated just
for that only purpose - to be prepared now. With the function "mysqli_stmt_prepare($stmt, $sql)", alongside
with the passed arguments "$stmt" and the query "$sql", we let the object "$stmt" prepare itself with the
passed query. Bear in mind: The variable "$sql" holds not the usual SQL-Statement, but is now a SQL-Statement
with that questionmark, a placeholder so to say.
Also the prepare-function does not only execute the preperation, but also returns a boolean. It returns true if
succeeded and false if failed to prepare, which for most parts all these functions do (Look it up in the php-manual.
I listed the links at the end of my comment. So take a look at e.g. link no. 3 and there check the part "Return Value").

(Btw. I am still confused by the fact that the php-manual says strictly to not include the semicolon
in the passed query, but in this example Daniel did. Maybe it works with it as well...)

Now if the preperation succeeded, before we execute the query, we have one thing left to do:
We have to bind our variable to the placeholder of the prepared query statement, which in this case is the "?".
(In the manual of "mysqli_stmt_prepare" it says: "The parameter markers must be bound to application variables
using mysqli_stmt_bind_param() and/or mysqli_stmt_bind_result() before executing the statement or fetching rows.")

With the function "mysqli_stmt_bind_param( , , )" we bind our variable to the "?" of our prepared statement.
So in order to do so, we pass three arguments:
1) the mysqli_stmt object "$stmt"
(which up to this point has been prepared with the "$sql" query, which had the questionmark in it as a placeholder),
2) the type of the variable we want to replace the placeholder with
(which itself needs to be put in quotationmarks, as the type-argument needs to be a string. Check the 4th. link below
and then go to the topic "Parameters" and in there look at the "types") and
3) the variable itself we want to replace the placeholder with.

Aaaand finally we can execute the query! :D

But let's briefly recap again first:
The mysqli_stmt object was created, then prepared with the placeholder-sql-statement,
then the placeholder got filled with our variable and is now ready for take off!!!

We execute it with the function "mysqli_stmt_execute( )" and pass the $stmt as an argument:
mysqli_stmt_execute($stmt);

Now with the function we create and get back a mysqli_result object. Daniel stored it
as "$result". Look into the php-manual link no. 6 below. There you can see the methods/functions this class/object delivers.
We can now use the function "fetch_assoc($result)" by passing the result-object as an argument and afterwards loop
through the associative array we got back, which rows we get spit out one by one.

Extra Info: If a class implements "Traversible" it means simplified, that we can use a foreach loop on it.

-- - - - - - - -

The best way to get your head around sth. you dont understand in php is to check the php manual.
It helped me a lot even thou I am as well new to php!!!

Look it up in that order:

[ddfgrtsd]

If anybody is getting question marks inside the database just remove the quotation marks around the question marks inside the VALUES parentheses.

Thanks you for your awesome content!

This is really educational and easy to follow!

I LOVE IT!

[Kevin-Woods]

Just a heads up - this tutorial begins with an introduction. At 02:30 it jumps, without commenting, into a separate document (index2.php) just to explain the code and using one variable to keep things simple. At 12:53 the lesson jumps back to the original example we've been using in the previous lesson. Here it lays out the same code, now within signup.inc.php, but this time using five variables (first, last, email, uid, pwd) in line with the form.

Took me a while to grasp! The diversion to index2.php was very confusing at first but now much clearer.

Massive thanks Dani for putting these episodes out, I've wanted to learn PHP for years. This is the first time someone has really clearly laid it out and dismantled the brick wall piece by piece.

[Cyber]

I see people saying it's hard to follow and all that but really man don't listen. I've followed your channel for a while now, some videos are "hard" some are "easy", don't try to attack just one audience. Always a pleasure watching your videos, they help a lot.

[jamiewright]

I have been struggling all afternoon to get my prepared statement to work and within 15 mins you have helped me understand how it all works and where my errors were, life saver!!!

[simopelle]

*init:* creates an object of mysqli_stmt class
*prepare:* assigns $sql to the statement _(like query($sql) in the previous video)_
*bind_params:* replaces the placeholders with real data
*execute:* runs the query in SQL database _(like $conn->query($sql) in the previous video)_
*result:* returns the resulting array from select queries


To get a result (like on $return in the previous video)_ you can just assign to the variable the output of *$stmt->execute()*.


P.S. I used _object oriented style_, you can replace *$stmt->command()* with *mysqli_stmt_command($stmt)* if you wanna use _procedural style_

[KAIKOjanai]

This series of tutorials has been very useful with excellent pace, allows beginner in programming like me to follow easily, until this episode.
I understand that there might not be too much behind the codes to explain so Daniel may want to quickly go through what to do, but it quickly became overwhelming. Maybe a summary on each newly introduced function would help?

[elel]

My last tutorial of the day and the most complex so far. I will need to start fresh tomorrow by revisiting this one.

[amr]

this course got from very easy to extremely hard very quickly :(

[lawrencemichael]

This video was kind of hard but after watching it 2-3 times it's actually very great.
Excellent!

[mojeDIY]

very useful, important information, that's what I always wanted to understand until now!

[jaiminpatel]

Thank You Daniel... You teach well and it feels that you teach for us and not for publicity.

[Broly-eflp]

Thank you so much for your time and effort on this series. It has helped me a ton with some of my Master's courses, especially since I don't have a computer related undergrad/background.

[pianoLee-sxdx]

At 12:24, why does this work when you haven't included the dbh.inc.php file in include? I couldn't see where you have placed the include file here but I tried it without it and it didn't work..Do you still need to include the mysqli_real_escape_string?

[kvazaios]

Probably the most useful video of the series

[familystucky]

official killer serie right there, it just feels good to inhale all that power

[RenderStream]

Best part, there is a beutiful teacher inside you. Thanks for the easy tutorials.

[ericsimmons]

Thank you so much for this tutorial. I was able to use the concepts and code to create a PayPal listener that works like a charm!!! Thank you so much.

[ricekrissy]

I'm glad im not the only one who got confused by this tutorial. All the videos except for this one were clear to me. Im so confused about the purpose of a prepared statement and how to use it. Im not grasping how you are using it at all. Since i was confused i went on w3schools and that's saying prepared statements are a way to execute the same/similar sql statements repeatedly with high efficiency and you never said anything like that. I'm very appreciative for these free lessons I'm just really confused. I see you have a patreon that's suppose to give updated help. Is there something in there that answers what the difference between procedural and object?