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 🙂

Material for this lesson:

Nguồn: https://svdpch.org/

Xem thêm bài viết khác: https://svdpch.org/cong-nghe/

48 thoughts on “40: What are Prepared Statements and how to use them | PHP tutorial | Learn PHP programming”

  1. 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!

    Reply
  2. 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.

    Reply
  3. I have a question that 'Is this prepared statement required only when taking input from the user or is it necessary while displaying any data without user input. Thanks

    Reply
  4. Initalize is the connection to DB , prepare is a check to see you can proceed with bind, bind paramenters is the actual insert, execute.

    Reply
  5. 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

    Reply
  6. This is a good tutorial very similar to what is being taught on W3 Schools example on procedural prepared statements. I can see that you have used the W3 Schools for your research and reading as well as the php net because W3 Schools example is the same as php net examples.

    Reply
  7. 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! 😀

    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 "mysqli_stmt_get_result($stmt)" 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.
    (https://www.php.net/manual/en/class.traversable.php)

    — – – – – – – –

    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:
    1) https://www.php.net/manual/en/mysqli.stmt-init.php
    2) https://www.php.net/manual/en/class.mysqli-stmt.php
    3) https://www.php.net/manual/en/mysqli-stmt.prepare.php
    4) https://www.php.net/manual/en/mysqli-stmt.bind-param.php
    5) https://www.php.net/manual/en/mysqli-stmt.execute.php
    6) https://www.php.net/manual/en/class.mysqli-result.php

    Reply
  8. I got stuck at setting the $data variable. What's the point of going through all this if you're gonna' hard code a parameter for the database value?

    Reply
  9. I am reading that there is no such thing as a date label, only strings =s integers = i and so on but no dates…What if I wanted to insert the 3D data format or had the table configured with a datetime columb?

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

    Reply
  11. Question:

    Do I need to initialize the statement before the prepare method?

    I have this and it seems to work.

    If($stmt = mysqli_prepare($dbc, $query).

    What is the difference from:

    $stmt = mysql_stmt_init($dbc)

    Mysqli_stmt_prepare($stmt)

    Thanks in advance. Your videos are very helpful for learning PHP. I am trying to learn it!

    Reply
  12. 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

    Reply
  13. When you create $data = "admin" ;
    It's mean when you echo it
    echo $data;
    So the show $data
    Admin
    Why you create $data when you have admin name on database you get admin name on database why you create $data can you explain it for me

    Reply
  14. In the first part of this episode, you mentioned that you take the parameter from var $data. In real life, How do you actually get it?

    Reply
  15. for beginners : I know it's hard, take all the time you need for this one, cause it is freaking important, other wise you gonna get hacked !

    Reply
  16. what type of parameter use for date data type and how to insert using prepare statement into database?
    please i need your help

    Reply
  17. 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.

    Reply
  18. @12:14 I got an error because I forgot to include this at the beginning of the document:

    <?php
    include_once: 'includes/dbh.inc.php';
    ?>

    I hope this can help someone

    Reply
  19. loving this tutorial here in 2019 but I'm hoping the further I go into the ending of this series I'll know the purposes of mysqli functions

    Reply
  20. 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?

    Reply
  21. sir why this code give me a error $pre_stmt = $this->con->prepare("select id from user where id=?") ; what is the error with this code

    Reply
  22. very convoluted👎 it's very confusing. how we can pass around $stmt like this from function to function without returning any results back to this variable?! how does it work?! $stmt goes into a function, then we use in another function! it doesn't make any sense!

    Reply

Leave a Comment