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
40: What are Prepared Statements and how to use them | PHP tutorial | Learn PHP programming
28 responses to “40: What are Prepared Statements and how to use them | PHP tutorial | Learn PHP programming”
Great video, but I think the steps presented on w3 schools are a bit more straight forward: https://www.w3schools.com/php/php_mysql_prepared_statements.asp
Nonetheless, thank you for this solid video!
Here I am xD
When I press the Button "Sign Up" I get a message saying "Object not found!" I checked my code and everything is good. My code is not working since Tutorial 39.
Any idea why is this happening?
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.
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.
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.
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.
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"
OMG finally video longer than my pony tail
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…
so what i'm gonna gonna do is gonna gonna insert a prepared statement.
u saved my day thank you
well done. kindly can show us How to Create an online forum that can be accessed by members to hold conversations and share their ideas. please
why only one time insertion operation perform using prepare statement in php? Plz give the solution for it.
It would have been helpful to make a video on mysqli functions.
How do you use more than one sql query with prepared statement? Can you just continue with it or must you do this outside the braces?
Very well explained, thank you!
Thank you bro. Can't donate much for your excellent videos cuz I am a student living on a tight budget. I will surely do when I find a job lol
You are writing $data='Admin' but I want don't give the value by myself I want to fetch the data from the database inside $data.
Confusing as fuck. I get errors, many others are using @stmt-> Prepare etc. Why shouldnt you?
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?
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
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?
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?
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();
}
}
}
}
}
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!
Do you need to use prepare statement for updating query?
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();
}
}
}
}
}
}
}
}
}
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..