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







Source : http://www.mustbebuilt.co.uk/php/insert-update-and-delete-with-mysqli/