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 …

Original source


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

  1. For me mysqli_stmt_bind_param() doesn't work, mysqli_stmt_prepare() doesn't work, all of them don't work for me except mysqli_stmt_init(). There isn't any syntax highlighting for them either.

  2. Q Again! It seems that I finished coding properly after fixing many mistakes. I submitted the button then got return successful on url. But when I check the DB. It stored new data as '?'. There is no syntax error. I thought something wrong with following part:

    else {
    mysqli_stmt_bind_param($stmt,"sssss", $first, $last, $email, $uid, $pwd);
    mysqli_stmt_execute($stmt);
    }

    Is ' mysqli_stmt_bind_param' not working properly?
    I coded exactly same as yours. Thanks.

  3. Thanks for tutrial. But I am stack at 12:16 getting error" Parse error: syntax error, unexpected '$sql' (T_VARIABLE)'" . It seems no problem with my code on the indicated line which is following.

    $sql ="SELECT * FROM users WHERE user_uid=? ;";

    I put php code on top of index.php
    <?php
    include_once 'includes/dbh.inc.php';
    ?>
    I was OK with previous episode connecting DB.
    I have no idea about what is wrong. Any suggestion is appreciated.

  4. Hey, why do we need to mention the datatype "s" to send string data in the query to the database when we have already set the parameters in the database to store string values as "text" or "varchar"

  5. ASP.Net is so much easier when it comes to working with the database. You just go to Visual Studio, make a few clicks to connect to db visually using the visual menu… and then a few more clicks to select data. Done! And it's already super secure and fast. Here I already lost you with all the parameters, placeholders and millions of built-in functions somewhere around 6th minute of this video…

  6. Huge Fan, quick question. When trying to use "mysqli_stmt_get_result($stmt);" on line 26 of your example program my code breaks and doesn't execute anything after. When looking up documentation I found a different way using bind_result but still I was wondering why that call wasn't working for me. Anyone else have this issue?

  7. mmtuts I need help, for a register script… I've three types of columns in my database, does it matter what type I use as date type assigned to the date in de bind param if I use text, int and datetime? Can I always use "s" or do I need to use another type? Thanks

  8. Loving your tutorials, I've learnt so much!
    I have a question though, what if I want to select and display more? For example like user, subject and comment but i want to display all the comments in the database, not just a specific one?

  9. Great video. I managed to set up a simple function for the entire sequence if anyone's interested.

    https://hastebin.com/obadeponom.php

    Btw @OP, you mention the difference between Object-Oriented Programming and Procedural Programming. My impression is that you can choose which version you want to use. Is it bad practise to combine them like I have? Are there cases where one will not work whereas the other would?

  10. Hey guys! I am in need of some help here and I got the following code but it is not updating…..

    <?php

    session_start();

    $paidbydate = date('Y-m-d H:i:s', strtotime("+2 day"));

    $emailreminder = 1;

    $emailreminderreset = 0;

    $subscriptionplandelete = 0;

    $paidbydatedelete = 0;

    $subscriptionplandatedelete =0;

    $expirydatedelete = 0;

    $feesdelete = 0;

    $totalfees = 0;

    $overduedelete = 0;

    $activatedelete = 0;

    if(!isset($_SESSION['u_uid'])) {

    header("Location: index.php?notlevel1user");

    exit();

    } else {

    include_once 'includes/dbh.php';

    //Created a template

    $sql = "SELECT * FROM memberships WHERE user_uid = ?;";

    //Create a prepared statement

    $stmt = mysqli_stmt_init($conn);

    //Prepare the prepared statement

    if (!mysqli_stmt_prepare($stmt, $sql)) {

    echo 'SQL statement failed';

    } else {

    //Bind parameters to the placeholder

    mysqli_stmt_bind_param($stmt, "s", $_SESSION['u_uid']);

    //Run parameters inside database

    mysqli_stmt_execute($stmt);

    $result = mysqli_stmt_get_result($stmt);

    while($row = mysqli_fetch_assoc($result)) {

    $_SESSIONID = $row['user_uid'];

    if ($row['subscriptionplan'] === 'Level 1' && $row['activate'] ==0) {

    header("Location: index.php?level1=not activated");

    exit();

    } else {

    if ($row['subscriptionplan'] === 'Level 1' && $row['activate'] == 1 && $row['emailreminder'] == 0 && date("Y-m-d H:i:s") > $row['paidbydate'] && $row['paid'] == 0) {

    $sql = "UPDATE memberships

    SET paidbydate = ?, emailreminder = ?

    WHERE user_uid = ?;

    ";

    $stmt = mysqli_stmt_init($conn);

    if(!mysqli_stmt_prepare($stmt, $sql)) {

    echo "SQL error";

    } else {

    mysqli_stmt_bind_param($stmt, "sis", $paidbydate, $emailreminder, $_SESSIONID);

    mysqli_stmt_execute($stmt);

    }

    header("Location: index.php?level1=overdue");

    exit();

    }

    }

    }

    }

    }

  11. After line 21, you have an else there but how can I add another else if after that? I need to do more if statements after that else loop… I can't get my prepared statment to update and would appreciate some help here… thanks!

  12. Would this be a similar method to doing an update? because my last if statement won't run and I think I did the update wrong…

    <!DOCTYPE html>

    <html>

    <head>

    <title></title>

    <meta charset="utf-8">

    <link rel="stylesheet" type="text/css" href="style.css">

    </head>

    <body>

    <?php

    ob_start();

    session_start();

    if(!isset($_SESSION['u_uid'])) {

    header("Location: index.php?notlevel1user");

    exit();

    } else {

    include_once 'includes/dbh.php';

    //Created a template

    $sql = "SELECT * FROM memberships WHERE user_uid = ?;";

    //Create a prepared statement

    $stmt = mysqli_stmt_init($conn);

    //Prepare the prepared statement

    if (!mysqli_stmt_prepare($stmt, $sql)) {

    echo 'SQL statement failed';

    } else {

    //Bind parameters to the placeholder

    mysqli_stmt_bind_param($stmt, "s", $_SESSION['u_uid']);

    //Run parameters inside database

    mysqli_stmt_execute($stmt);

    $result = mysqli_stmt_get_result($stmt);

    while ($row = mysqli_fetch_assoc($result)) {

    if ($row['subscriptionplan'] !== 'Level 1') {

    header("Location: index.php?level1=error");

    exit();

    } else {

    if($row['subscriptionplan'] === 'Level 1' && $row['activate'] == 0) {

    header("Location: index.php?level1=notactivated");

    exit();

    } else {

    if($row['subscriptionplan'] === 'Level 1' && $row['activate'] == 1 && $row['paid'] == 0 && date("Y-m-d") > $row['paidbydate'] && $row['emailreminder'] == 0) {

    $sql = "UPDATE memberships

    SET paidbydate = now() + interval '2' day, emailreminder = 1

    WHERE user_uid = ?;

    ";

    $stmt = mysqli_stmt_init($conn);

    //Prepare the prepared statement

    if (!mysqli_stmt_prepare($stmt, $sql)) {

    echo 'SQL statement failed';

    } else {

    //Bind parameters to the placeholder

    mysqli_stmt_prepare($stmt, $sql);

    mysqli_stmt_bind_param($stmt, "s", $_SESSION['u_uid']);

    //Run parameters inside database

    mysqli_stmt_execute($stmt);

    header("Location: index.php?level1=overdue");

    exit();

    if($row['subscriptionplan'] === 'Level 1' && $row['activate'] == 1 && $row['paid'] == 0 && date("Y-m-d") > $row['paidbydate'] && $row['emailreminder'] == 1) {

    $sql = "UPDATE memberships

    SET subscriptionplan = '', subscriptionplandate = '', paidbydate = '', emailreminder = ''

    WHERE user_uid = ?;

    ";

    $stmt = mysqli_stmt_init($conn);

    //Prepare the prepared statement

    if (!mysqli_stmt_prepare($stmt, $sql)) {

    echo 'SQL statement failed';

    } else {

    //Bind parameters to the placeholder

    mysqli_stmt_bind_param($stmt, "s", $_SESSION['u_uid']);

    //Run parameters inside database

    mysqli_stmt_execute($stmt);

    header("Location: index.php?level1=cancelled");

    exit();

    }

    }

    }

    }

    }

    }

    }

    }

    }

  13. Hi again and I am enjoying your tutorials but I am trying to do a website and almost got it to work but am having some problems here.. I am trying to follow your prepared statement and keep that following line

    // if (!mysqli_stmt_prepare($stmt, $sql)) {

    // echo "SQL statement failed";

    // } else {

    But I have the following code and need another else statement right before the if statement… I need to do something like this

    // if (!mysqli_stmt_prepare($stmt, $sql)) {

    // echo "SQL statement failed";

    // } else {

    //Bind parameters to the placeholder

    mysqli_stmt_bind_param($stmt, "s", $_SESSION['u_uid']);

    mysqli_stmt_execute($stmt);

    header("Location: index.php?level1=overdue");

    exit();

    } else {

    if($subscriptionplan === 'Level 1' && date("Y-m-d") > $paidbydate && $activate == 1 && $emailreminder == 1) {

    $sql = "UPDATE memberships

    SET subscriptionplan = '', paidbydate = '', expirydate = '', fees = '', totalfees = '', emailreminder = 0, activate = 0, overdue = 0, paid = ''

    WHERE user_uid = ?;

    But I have an error because there are two else..

Leave a Reply