Three SQL commands – INSERT, UPDATE and DELETE – usually form the basis of any content management system.
These SQL commands are all going to be the recipients of user derived data most often from HTML forms but possibly through querystrings.
As such the mysqli prepare statement with its separation of the query from the data is a good secure approach to take.
INSERT
Assuming a HTML form of method $_POST with the appropriate fields in it the following would insert a new record in a table called ‘movies’.
$stmt = $mysqli->prepare("INSERT INTO movies(filmName, filmDescription,filmImage,filmPrice,filmReview) VALUES (?, ?, ?, ?, ?)"); $stmt->bind_param('sssdi', $_POST['filmName'], $_POST['filmDescription'], $_POST['filmImage'], $_POST['filmPrice'], $_POST['filmReview']); $stmt->execute(); $stmt->close();
Notice the use of the flags ‘sssdi’ in the bind_param() method and the position placeholders (?) in the prepare statement.
For a reminder as to how these relate take at look at this interactive example.
Getting Auto Increment Key Values with insert_id
When using SQL INSERT you may have set up your database table with an auto_increment field to ensure your primary keys remain unique. You may need to know what this value is as soon as the database creates it. In this scenario the prepare statement insert_id property can be used. Create a variable from this property after the execute() method but before the close() method of the prepare statement. An amended version of the above that captures the new primary key value would look as follows:
$stmt = $mysqli->prepare("INSERT INTO movies(filmName, filmDescription,filmImage,filmPrice,filmReview) VALUES (?, ?, ?, ?, ?)"); $stmt->bind_param('sssdi', $_POST['filmName'], $_POST['filmDescription'], $_POST['filmImage'], $_POST['filmPrice'], $_POST['filmReview']); $stmt->execute(); $newId = $stmt->insert_id; $stmt->close();
UPDATE
An UPDATE example would work in the same fashion this time the SQL has a WHERE clause to identify which record to update.
$stmt = $mysqli->prepare("UPDATE movies SET filmName = ?, filmDescription = ?, filmImage = ?, filmPrice = ?, filmReview = ? WHERE filmID = ?"); $stmt->bind_param('sssdii', $_POST['filmName'], $_POST['filmDescription'], $_POST['filmImage'], $_POST['filmPrice'], $_POST['filmReview'], $_POST['filmID']); $stmt->execute(); $stmt->close();
DELETE
Finally a DELETE statement. Like the UPDATE a WHERE clause ensures the correct record is removed.
$stmt = $mysqli->prepare("DELETE FROM movies WHERE filmID = ?"); $stmt->bind_param('i', $_POST['filmID']); $stmt->execute(); $stmt->close();
$stmt=$conn->prepare(“UPDATE contenido SET Contenido=?,
Titulo=?
WHERE id = ?”);
$stmt->bind_param(‘ssi’, $body, $title, $id);
$stmt->execute();
I have this code but it doesn’t work, can you help me?
As far as I can tell that looks fine – assuming you have a mysqli connection called ‘$conn’ and a table called ‘contenido’ with field ‘Contenido’, ‘Titulo’ and ‘id’. I would really need more to go on to help you here.
the problem was that one param of the previous page wasn’t pass the value to the class but it is fixed now. thank you very much for your answer (sorry if i make mistakes in my write i am still learning english)
And you blog is great
Some really useful information here. Thanks for posting.
Great tutorial. Is there a graceful way to bind a large number of fields and values, perhaps using a loop? In my case, there are 61, which makes for a rather long string.
Hi Geno – yes always a tricky one that. I have played around with various techniques and came up with this version. It does use PDO rather than mysqli but it does reduce all the tedious placeholder typing.
Inserting Multiple Values with PDO and a Loop.
There is a way to loop through your parameters. The following was written using PHP.
Pass your statement, types and params like so;
$statement = “SELECT col_one, col_two FROM table WHERE col_one=?”;
$types = “s”;
$params = “foo”;
The important feature here is the … in the bind_param call, called splat.
http://php.net/manual/en/migration56.new-features.php
Very handy.
public function ExecutePreparedSelectQuery($statement, $types, $params)
{
$array = [];
if($stmt = $this->db->prepare($statement))
{
$stmt->bind_param($types, …$params);
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc())
{
$array[] = $row;
}
}
return $array;
}
Thanks for this input. Works like a charm
vary useful post,thanks a lot.
This was very, very helpful. I am moving from my long loved procedural to OOP. Things like this make my life so much easier. The way you have presented the info made it very easy to study and compare. Your text is clear and easy to read. I have one nagging feeling and its the same feeling that keeps me up at night some times worrying. What is the best way to escape these to help prevent attacks?
should we use something like
foreach($_POST as $unclean) {
$mysql = dbconnect();
$cleaned[] = $mysql->real_escape_string($unclean);
}
Thanks for your comments. Firstly your example uses mysql – use either mysqli or PDO with the prepare statements. Secondly look at these ways of filtered your inputs – Validating, Sanitizing and Escaping
hello , thanks for this tuto , please if you can help me in this fatal error with delete statement
public function deleteArticle($id){
$mysqli = parent::$cnx;
$q = ‘DELETE FROM artcile WHERE id_article = ?’;
$stmt = $mysqli->prepare($q);
$stmt->bind_param(‘i’,$id);
$stmt->execute();
if($stmt->errno){
return false;
}else{
return true;
}
}
___________________
if(isset($_POST[‘delete’])){
$cbox = $_POST[‘checkbox’];
$dd = count($cbox)-1;
for($i=0;$ideleteArticle($cbox[$i])){
echo ‘opération echou’;
}
}
}
_________________
Fatal Error : Call to a member function bind_param() on a non-object
I wonder is it the loop? Should it be:
Great Blog!
I have this code but it doesn’t work, can you help me?
Título
Data
prepare(“SELECT idnews, titulonews, datanews FROM novidades ORDER BY datanews ASC”);
$stmt->bind_param(‘iss’, $_GET[‘idnews’], $_GET[‘titulonews’], $_GET[‘datanews’]);
$stmt->execute();
$stmt->bind_result($idnews, $titulonews, $datanews);
$stmt->store_result();
$numRows = $stmt->num_rows;
echo “Nº de registos: $numRows”;
while ($stmt->fetch()) { ?>
<input name="del" value ="”type=”submit” />
prepare(“DELETE FROM novidades WHERE idnews = ?”);
$stmt->bind_param(‘i’, $_POST[‘idnews’]);
$stmt->execute();
$stmt->close();}
?>
Many thanks
What are you trying to do here. If you just want to delete from the table novidades why not just use a DELETE statement.
Just thanks for great writing and working examples!
How would I alter the Insert statement to upload an image as well as adding the data? I am trying to move from MySQL to MySQLi
$sql=”select id,name,age from student id=?”;
$stmt=$conn->prepare($sql);
$stmt->bind_param(“i”, $num);
$stmt->execute();
$stmt->bind_result($id,$name,$age);
if i use the statement like
$sql=”select * from student id=?”;
then how can i bind the result in single array?
You need to put the fetch in a loop see Multi Row Prepare Statements
I have php quiz app .its working perfectly but i want view previous scores,and i want to store scores like this:
user_id=1,category_id=2,question_id=1,2,3,answer_id=2,4,3,time_taken=2,5,8;
in my quiz app having 4 multiple choices, user selected the option and then click next each time post the values like above.how to store values only above 3 columns seaperated by comma.each time post above all rows but once user_id,category_id values are stored, only remainig rows are stored values separated by comma.
Have you looked at arrays for storing this kind of data?
I ve just started doing this type of coding.. ur code works perfectly for me.. but i really dont get these “issssi” “sssdi” things at all .. why we use these ?? and why their are varieties of these ?? and does their purpose vary from statement to statement ???
Take at look at this page:
http://www.mustbebuilt.co.uk/php/using-object-oriented-php-with-the-mysqli-extension/
and this one
http://www.mustbebuilt.co.uk/demo/php/prepare-visual.php
Might help.
It is to do with the data type that you wish to bind to your query. s = string, d = double, i = integer, b = blob (binary data)
STAFF MANAGEMENT SYSTEM
ENTER DETAILS
STAFF ID:
<input type="text" name="$STAFF_ID" value="”/>
FIRST NAME:
LAST NAME:
E-MAIL ADDRESS:
DATE OF BIRTH:
DEPARTMENT:
JOB TITLE:
OFFICE:
SALARY:
BUSINESS PH:
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "inventory";
// Create connection
$conn = mysqli_connect($servername, $username, $password,$dbname);
// Check connection
if (!$conn){
die("ERROR: Could not connect. " . mysqli_connect_error());
}
echo "Connected successfully”;
// extract form values
$STAFF_ID = $_POST[‘STAFF_ID’];
$STAFF_FNAME = $_POST[‘STAFF_FNAME’];
$STAFF_LNAME = $_POST[‘STAFF_LNAME’];
$STAFF_EMAIL = $_POST[‘STAFF_EMAIL’];
$STAFF_DOB = $_POST[‘STAFF_DOB’];
$STAFF_DEPARTMENT = $_POST[‘STAFF_DEPARTMENT’];
$STAFF_TITLE = $_POST[‘STAFF_TITLE’];
$STAFF_OFFICE = $_POST[‘STAFF_OFFICE’];
$STAFF_SALARY = $_POST[‘STAFF_SALARY’];
$STAFF_PHONE = $_POST[‘STAFF_PHONE’];
// build query
$sql = “INSERT INTO FACULITY(STAFF_ID,STAFF_FNAME,STAFF_LNAME,STAFF_EMAIL,STAFF_DOB,STAFF_DEPARTMENT,STAFF_TITLE,STAFF_OFFICE,STAFF_SALARY,STAFF_PHONE)
VALUES(‘$STAFF_ID’,’$STAFF_FNAME’,’$STAFF_LNAME’,’$STAFF_EMAIL’,
‘$STAFF_DOB’,’$STAFF_DEPARTMENT’,
‘$STAFF_TITLE’,’$STAFF_OFFICE’,’$STAFF_SALARY’,’$STAFF_PHONE’)”;
if(mysqli_query($conn, $sql)){
echo “Thank you for registering.Your ID is $STAFF_ID”.””;
}else{
echo “ERROR: Could not able to execute $sql. ” . mysqli_error($conn);
}
// close connection
mysqli_close($conn);
?>
hello sir/madam,i couldnt add records..please could you help me
Hi,
It would have been nice if you added an example on this page with the equivalent of mysqli_fetch_array()
I have bookmarked your page but this one which is really needed is missing and many people will use it.
ie what is the equivalent in prepared statements of:
$talent = escape_string($_GET[‘talent’]);
$query1 = query(“SELECT * FROM table_name WHERE gallery_id = $id);
confirm($query1);
while($row = mysqli_fetch_array($query1)){
$image = $row[‘gallery_pic1’];
}
Hope this comment will reach you!
You should really use a prepare statement for this such as:
$stmt = $mysqli->prepare(“INSERT INTO movies(filmName,
filmDescription,filmImage,filmPrice,filmReview) VALUES (?, ?, ?, ?, ?)”);
$stmt->bind_param(‘sssdi’, $_POST[‘filmName’],
$_POST[‘filmDescription’],
$_POST[‘filmImage’],
$_POST[‘filmPrice’],
$_POST[‘filmReview’]);
$stmt->execute();
$stmt->close();
Hi,
Thank you for the reply, but my query is a SELECT with a loop.
How would you put this one in a prepared statement via mysqli please?
This cheatsheet might give you what you need.
Superb!!!
Thank you so much! I can’t thank you enough!
im trying to update my table.. and i used oop for updating… and i encountered error like this!
( ! ) Fatal error: Call to a member function bind_param() on a non-object in C:\wamp\www\IS\admin\admin_profile.php on line 29… what would be the cause? can some try to fix or give a solution ? tia
Have you checked that the SQL statement you want to run is correct. You could test this by running it directly in the SQL tab in phpMyAdmin. Also check to see that the connection is correct.
Hello mate… great tutorial… please I have a table with 10 row. with edit and delete fa-icon next to it. if I wanted to edit each row and delete each.
How do I place the query does it need to be on different page? if yes how do I get the edit button to trigger the edit page query, if not different page can I just wrap the edit query with the edit icon and is it the same with delete.
Thanks.
Try this tutorial of mine on github. Might help. It uses PDO which is very similar to mysqli (better I would say)
https://github.com/mustbebuilt/wad-pdo-pt2-cms
Just a heads up, I still pop back here to use these examples.
Thanks again.