I'm refactoring procedural code, switching to prepared MySQL statements using mysqli_query()
. I do understand writing OO would be better, but it's out of scope for this task.
Use the below function as follows:
list($result, $numrows, $err) = my_mysqli_stmt($db_handler, $query, $params);
Returns array with three values, $result
, $numrows
and $err
.
$result
is either false on error, or contains empty or multi-dimensional array.
$numrows
returns number of affacted rows, number of returned rows or 0 on error.
$err
returns mysqli_error()
or mysqli_stmt_error()
error.
function my_dbstmt($handler, $query, $params=array())
{
$stmt = mysqli_prepare($handler, $query);
if($stmt===false)
{
return array(false, 0, mysqli_error($handler));
}
//First two parameter of mysqli_stmt_bind_param
$refarg = array($stmt, array2typestring($params));
//create array of parameters' references
foreach ($params as $key => $value)
{
$refarg[] = &$params[$key];
}
if(!empty($params))
{
//bind parameters with dynamic length
call_user_func_array("mysqli_stmt_bind_param", $refarg);
}
$exec = mysqli_stmt_execute($stmt);
if($exec===false)
{
return array(false, 0, mysqli_stmt_error($stmt));
}
$result = mysqli_stmt_get_result($stmt);
if($result===false)
{
//not a result, but successfully executed
$numrows = mysqli_stmt_affected_rows($stmt);
return array(true, $numrows, null);
}
else
{
mysqli_stmt_store_result($stmt);
$data = array();
while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC))
{
$data[] = $row;
}
$numrows = count($data);
return array($data, $numrows, null);
}
}
For completeness (detail of above referenced function:
function array2typestring($array)
{
$types = array();
foreach ($array as $key => $value)
{
$types[] = 's';
}
$types = implode('', $types);
return $types;
}
Example usage:
$db_handler = mysqli_connect("p:$dblocal", $dbuser, $dbpass) or die();
$query = "SELECT title FROM books WHERE id=?";
$params = array($bookid);
list($result, $numrows, $err) = my_dbstmt($db_handler, $query, $params);
Result:
$result
holds multi-dimensional array with books' title (just one in this case). $numrows
is 1. $err
is null.
Catching errors would work as follows:
if($result===false) {print("Error finding book")}
elseif($numrows==0) {print("Book not found")}
elseif($numrows>0) {print("Book found")}