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();

34 Thoughts to “INSERT, UPDATE and DELETE with mysqli”

  1. dara

    $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?

    1. admin

      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.

  2. dara

    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

  3. Some really useful information here. Thanks for posting.

  4. geno

    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.

    1. admin

      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.

    2. Mr. Big

      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;
      }

  5. Johannes

    Thanks for this input. Works like a charm

  6. vary useful post,thanks a lot.

  7. bob

    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);
    }

    1. admin

      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

  8. 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

    1. admin

      I wonder is it the loop? Should it be:

      if(isset($_POST['delete'])){
         $cbox = $_POST['checkbox'];
         $dd = count($cbox)-1;
         for($i=0;$i<=$dd;$i++){
               $ideleteArticle($cbox[$i]);
      	 echo 'opération echou';
          }
      }
      
      
  9. Jorge

    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

    1. admin

      What are you trying to do here. If you just want to delete from the table novidades why not just use a DELETE statement.

  10. randall

    Just thanks for great writing and working examples!

  11. Martin

    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

  12. ranjith

    $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?

    1. admin

      You need to put the fetch in a loop see Multi Row Prepare Statements

  13. Naveen

    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.

    1. admin

      Have you looked at arrays for storing this kind of data?

  14. sarmistha

    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 ???

    1. admin

      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)

  15. 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

  16. Benoit

    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!

  17. admin

    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();

    1. Benoit

      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?

      1. admin

        This cheatsheet might give you what you need.

        1. Benoit

          Superb!!!

          Thank you so much! I can’t thank you enough!

  18. mclarz

    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

    1. admin

      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.

  19. Olaskee

    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.

    1. admin

      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

  20. Bob

    Just a heads up, I still pop back here to use these examples.

    Thanks again.

Leave a Comment