3

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`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

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.

Edited by pritaeas

<?php
// 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;
    die();
}

// 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 = 'pritaeas@example.com';
$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>';
}
else
{
    echo '<p>Insert failed</p>';
    die($mysqli->error);
}

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

$level++;

$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>';
}
else
{
    echo '<p>Update failed</p>';
    echo $mysqli->error;
}

$mysqli->close();
?>
2
Contributors
2
Replies
97
Views
4 Years
Discussion Span
Last Post by pritaeas
0

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

0

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.

Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.