PhpRiot
News Archive
PhpRiot Newsletter
Your Email Address:

More information

Using MySQL prepared statements with PHP mysqli

Note: This article was originally published at Planet PHP on 16 April 6040.
Planet PHP

Starting with PHP mysqli is easy, if one has some SQL and PHP skills. To get started one needs to know about the specifics of MySQL and a few code snippets. Using MySQL stored procedures with PHP mysqli has found enough readers to begin with a aoquickstarta or aohow-toa series. Take this post with a grain of salt. I have nothing against Prepared Statements as such, but I dislike unreflected blind use.

Using prepared statements with mysqli

The MySQL database supports prepared statements. A prepared statement or a parameterized statement is used to execute the same statement repeatedly with high efficiency.

Basic workflow

The prepared statement execution consists of two stages: prepare and execute. At the prepare stage a statement template is send to the database server. The server performs a syntax check and initializes server internal resources for later use.

The MySQL server supports using anonymous, positional placeholder with ?.

$mysqli = new mysqli("localhost", "root", "", "test"); /* Non-prepared statement */ if (!$mysqli-query("DROP TABLE IF EXISTS test") || !$mysqli-query("CREATE TABLE test(id INT)")) echo "Table creation failed: (" . $mysqli-errno . ") " . $mysqli-error; /* Prepared statement, stage 1: prepare */ if (!($stmt = $mysqli-prepare("INSERT INTO test(id) VALUES (?)"))) echo "Prepare failed: (" . $mysqli-errno . ") " . $mysqli-error;

Prepare is followed by execute. During execute the client binds parameter values and sends them to the server. The server creates a statement from the statement template and the bound values to execute it using the previously created internal resources.

/* Prepared statement, stage 2: bind and execute */ $id = 1; if (!$stmt-bind_param("i", $id)) echo "Binding parameters failed: (" . $stmt-errno . ") " . $stmt-error; if (!$stmt-execute()) echo "Execute failed: (" . $stmt-errno . ") " . $stmt-error;

Repeated execution

A prepared statement can be executed repeatedly. Upon every execution the current value of the bound variable is evaluated and send to the server. The statement is not parsed again. The statement template is not transferred to the server again.

$mysqli = new mysqli("localhost", "root", "", "test"); /* Non-prepared statement */ if (!$mysqli-query("DROP TABLE IF EXISTS test") || !$mysqli-query("CREATE TABLE test(id INT)")) echo "Table creation failed: (" . $mysqli-errno . ") " . $mysqli-error; /* Prepared statement, stage 1: prepare */ if (!($stmt = $mysqli-prepare("INSERT INTO test(id) VALUES (?)"))) echo "Prepare failed: (" . $mysqli-errno . ") " . $mysqli-error; /* Prepared statement, stage 2: bind and execute */ $id = 1; if (!$stmt-bind_param("i", $id)) echo "Binding parameters failed: (" . $stmt-errno . ") " . $stmt-error; if (!$stmt-execute()) echo "Execute failed: (" . $stmt-errno . ") " . $stmt-error; /* Prepared statement: repeated execution, only data transferred from client to server */ for ($id = 2; $id execute()) echo "Execute failed: (" . $stmt-errno . ") " . $stmt-error; /* explicit close recommended */ $stmt-close(); /* Non-prepared statement */ $res = $mysqli-query("SELECT id FROM test"); var_dump($res-fetch_all());


array(4) { [0]= array(1) { [0]= string(1) "1" } [1]= array(1) { [0]= string(1) "2" } [2]= array(1) { [0]= string(1) "3" } [3]= array(1) { [0]= string(1) "4" } }

Every prepared statement occupies server resources. Statements should be closed explicitly immediately after use. If not done explicitly, the statement will be closed when the statement handle is freed by PHP.

Using a prepared statement is not always the most efficient way of executing a statement. A prepared statement executed only once causes more client-server round-trips than a non-prepared statement. This is why the SELECT is not run as a prepared statement above.

Also, consider the use of the MySQL multi-INSERT SQL

Truncated by Planet PHP, read more at the original (another 11707 bytes)