MySQLi binding example

Updated pritaeas 3 Tallied Votes 1K Views Share

The following snippet shows how you can use binding in your queries when using MySQLi. For starters, here's the table structure I've used:

CREATE TABLE `mytable` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `level` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)

The snippet shows how to use binding for an INSERT and an UPDATE query. The same method can be used for a SELECT. Note that you can only use it to bind values to a query, you cannot use this to dynamically insert a table- or column name.

Comments and questions appreciated, as always.

// this example is an addition to using-mysqli.php
// the table structure used is in sql/using-mysqli-binding.sql

// the obvious lines will not be commented, as they are
// already explained in the other code snippet.

$mysqli = new mysqli('localhost', 'myuser', 'mypassword', 'mydatabase');

if ($mysqli->connect_errno) {
    echo '<p>There was an error connecting to the database!</p>';
    echo $mysqli->connect_error;

// insert a record
// to use binding, use a question mark where you would normally insert a value
$query = 'INSERT INTO `mytable` (`name`, `email`, `dob`, `level`) VALUES (?, ?, ?, ?)';

// prepare the query for binding
$statement = $mysqli->prepare($query);

// now bind the actual values to variables (see it as replacing the question marks)
// 'sssi' defines the type for each of the question marks in order
// options are s(string) i(integer) d(double) b(blob)
$statement->bind_param('sssi', $name, $email, $dob, $level);

// you can now give values to your variables
// whether this happens now, or before the query doesn't matter,
// as long as it happens before execute.
$name = 'pritaeas';
$email = '';
$dob = '2013-08-30';
$level = 0;

// execute the query
$result = $statement->execute();
if ($result)
    $lastId = $mysqli->insert_id;
    echo '<p>Rows inserted: ' . $mysqli->affected_rows . '</p>';
    echo '<p>Last ID: ' . $lastId . '</p>';
    echo '<p>Insert failed</p>';

// update the last record.
// the same method works for update queries too.


$query = 'UPDATE `mytable` SET `level` = ? WHERE `id` = ?';

$statement = $mysqli->prepare($query);

// binding two integers now,
// both already have a value
$statement->bind_param('ii', $level, $lastId);

$result = $statement->execute();
if ($result)
    echo '<p>Rows updated: ' . $mysqli->affected_rows . '</p>';
    echo '<p>Update failed</p>';
    echo $mysqli->error;

Linddha 0 Light Poster

Sorry but when i try to use my code i got an error
Fatal error: Call to a member function bind_param() on a non-object in D:\X.A.M.P.P\htdocs\webcom\account\modul\aksiajax.php on line 223

could you tell me what's wrong??

pritaeas 2,130 ¯\_(ツ)_/¯ Moderator Featured Poster

Call to a member function bind_param() on a non-object

That would mean your MySQLi object is not valid. Hard to say without your code.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.