PHP : Function Reference : MySQL Improved Extension : mysqli_prepare PHP Examples Tutorials References

Code Examples / Notes » mysqli_prepare

adam

The purpose of prepared statements is to not include data in your SQL statements. Including them in your SQL statements is NOT safe. Always use prepared statements. They are cleaner to use (code easier to read) and not prone to SQL injections. Escaping strings to include in SQL statements doesn't work very well in some locales hence it is not safe.

admin

Sorry for the typo it was performed on mysql 5.0 ofcourse.

codefiend

Note that single-quotes around the parameter markers _will_ prevent your statement from being prepared correctly. Ex: <?php $stmt = $mysqli->prepare("INSERT INTO City (District) VALUES ('?')"); echo $stmt->param_count." parameters\n"; ?> will print 0 and fail with "Number of variables doesn't match number of parameters in prepared statement" warning when you try to bind the variables to it. But <?php $stmt = $mysqli->prepare("INSERT INTO City (District) VALUES (?)"); echo $stmt->param_count." parameters\n"; ?> will print 1 and function correctly. Very annoying, took me an hour to figure this out.

nom0ny

It must be noted in the Description whether developers should call mysqli_stmt_close prior to executing mysqli_prepare again on the same statement variable. Example, Script A calls mysqli_stmt_close twice: <?php /* Script A -- We are already connected to the database */ $stmt = mysqli_prepare($link, "INSERT INTO table VALUES (?, ?, 100)"); /* Query 1 */ mysqli_stmt_bind_param($stmt, "si", $string, $integer); mysqli_stmt_execute($stmt); mysqli_stmt_close($stmt); // CLOSE $stmt $stmt = mysqli_prepare($link, "INSERT INTO table VALUES ('PHP', ?, ?)"); /* Query 2 */ mysqli_stmt_bind_param($stmt, "ii", $integer, $code); mysqli_stmt_execute($stmt); mysqli_stmt_close($stmt); // CLOSE $stmt /* Script A -- Continues on... */ ?> Next, we have Script B, calling mysqli_prepare again before issuing mysqli_stmt_close on the prior statement. <?php /* Script B -- We are already connected to the database */ $stmt = mysqli_prepare($link, "INSERT INTO table VALUES (?, ?, 100)"); /* Query 1 */ mysqli_stmt_bind_param($stmt, "si", $string, $integer); mysqli_stmt_execute($stmt); $stmt = mysqli_prepare($link, "INSERT INTO table VALUES ('PHP', ?, ?)"); /* Query 2 */ mysqli_stmt_bind_param($stmt, "ii", $integer, $code); mysqli_stmt_execute($stmt); mysqli_stmt_close($stmt); // CLOSE $stmt /* Script B -- Continues on... */ ?> Which method is more efficient and should be used by developers?

joe

in response to J dot andrew's type function.... switch ( gettype( $var ) ) { case 'integer': return 'i'; break; case 'double': return 'd'; break; case 'string': return 's'; break; case 'boolean': case 'array': case 'object': case 'resource': case 'NULL': default: return NULL; break; } // could re-write to return first character, or remove breaks. Meh. why do a string comparison? you could do something like if is_int($QueryVar) $QueryVarType = "i"; elseif is_string($QueryVar) $QueryVarType = "s"; elseif is_double($QueryVar) $QueryVarType = "d"; else die ("Invalid non-scalar value")

david kramer

I don't think these are good examples, because the primary use of prepared queries is when you are going to call the same query in a loop, plugging in different values each time. For instance, if you were generating a report and needed to run the same query for each line, tweaking the values in the WHERE clause, or importing data from another system.

ulf wostner

Here is an example using bind_param and bind_result, showing iteration over a list of cities. Note that there's some bug-potential in cases where the query returns NULL for some parameter value, but the bind_result variables still might be bound. So, we use a conditional to spray the spot first. $mysqli->select_db("world"); $template = "SELECT District, CountryCode FROM City WHERE Name=?"; printf("Prepare statement from template: %s\n", $template); $cities = array('San Francisco', 'Lisbon', 'Lisboa', 'Marrakech', 'Madrid'); printf("Cities: %s\n", join(':', $cities)); if ($stmt = $mysqli->prepare($template)) { foreach($cities as $city) { // bind the string $city to the '?' $stmt->bind_param("s", $city); $stmt->execute(); // bind result variables $stmt->bind_result($d,$cc); // 'Lisbon' is not found in the world.City table, but 'Lisboa' is. // Using a conditional we avoid putting Lisbon in California. if($stmt->fetch()) { printf("%s is in %s, %s\n", $city, $d, $cc); } } $stmt->close(); } With the conditional statement we get the desired result: Prepare statement from template: SELECT District,CountryCode FROM City WHERE Name=? Cities: San Francisco:Lisbon:Lisboa:Marrakech:Madrid San Francisco is in California, USA Lisboa is in Lisboa, PRT Marrakech is in Marrakech-Tensift-Al, MAR Madrid is in Madrid, ESP But, without the conditional statement we would put Lisbon in California: San Francisco is in California, USA Lisbon is in California, USA Lisboa is in Lisboa, PRT Marrakech is in Marrakech-Tensift-Al, MAR Madrid is in Madrid, ESP

mysqli_prepare

Prepare a SQL statement for execution (PHP 5)
mysqli_stmt mysqli_prepare ( mysqli link, string query )

Example 1535. Object oriented style

Example 1536. Procedural style

Code Examples / Notes » mysqli_prepare

Change Language