INSERT, UPDATE and DELETE with mysqli

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

33 Comments

  1. dara
    Jan 29, 2013 @ 04:39:52

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

    Reply

    • admin
      Jan 29, 2013 @ 21:31:50

      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.

      Reply

  2. dara
    Jan 30, 2013 @ 04:29:47

    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

    Reply

  3. Techheadz
    Jul 13, 2013 @ 10:35:47

    Some really useful information here. Thanks for posting.

    Reply

  4. geno
    Jul 30, 2013 @ 01:06:40

    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.

    Reply

    • admin
      Jul 30, 2013 @ 11:27:17

      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.

      Reply

    • Mr. Big
      May 30, 2016 @ 17:15:42

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

      Reply

  5. Johannes
    Sep 10, 2013 @ 11:41:44

    Thanks for this input. Works like a charm

    Reply

  6. kasun
    Sep 19, 2013 @ 10:28:02

    vary useful post,thanks a lot.

    Reply

  7. bob
    Oct 09, 2013 @ 12:16:27

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

    Reply

    • admin
      Oct 23, 2013 @ 21:40:29

      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

      Reply

  8. abelkhalaqe
    Jan 23, 2014 @ 11:54:04

    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

    Reply

    • admin
      Jan 23, 2014 @ 13:32:13

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

      Reply

  9. Jorge
    Jul 12, 2014 @ 02:18:09

    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

    Reply

    • admin
      Jul 16, 2014 @ 11:19:27

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

      Reply

  10. randall
    May 12, 2015 @ 01:35:46

    Just thanks for great writing and working examples!

    Reply

  11. Martin
    Aug 30, 2015 @ 06:13:12

    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

    Reply

  12. ranjith
    Sep 23, 2015 @ 11:02:47

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

    Reply

  13. Naveen
    Jan 29, 2016 @ 17:50:48

    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.

    Reply

    • admin
      Jun 14, 2016 @ 14:10:40

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

      Reply

  14. sarmistha
    Mar 10, 2016 @ 07:07:40

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

    Reply

  15. Monika Degei
    Jun 11, 2016 @ 01:26:05

    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

    Reply

  16. Benoit
    Jun 14, 2016 @ 12:05:16

    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!

    Reply

  17. admin
    Jun 14, 2016 @ 14:18:16

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

    Reply

    • Benoit
      Jun 14, 2016 @ 16:24:19

      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?

      Reply

      • admin
        Jun 14, 2016 @ 20:09:32

        This cheatsheet might give you what you need.

        Reply

        • Benoit
          Jun 15, 2016 @ 10:41:11

          Superb!!!

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

          Reply

  18. mclarz
    Sep 25, 2016 @ 04:27:57

    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

    Reply

    • admin
      Sep 25, 2016 @ 14:05:45

      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.

      Reply

  19. Olaskee
    Dec 08, 2016 @ 08:28:21

    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.

    Reply

Leave a Reply

*