<?php
$start = microtime(true);
$db = $mysqli; // this service provides a predefined $mysqli variable while we are using $db for the connection
#Create a temporary table
$db->query("CREATE temporary TABLE tmp_mysqli_helper_test
(id int auto_increment primary key, name varchar(9))");
# populate it with sample data
$sql = "INSERT INTO tmp_mysqli_helper_test (name) VALUES (?),(?),(?)";
$stmt = prepared_query($db, $sql, ['Sam','Bob','Joe']);
echo "Affected rows: $stmt->affected_rows\n";
echo "Last insert id: $db->insert_id\n";
# Getting rows in a loop
$sql = "SELECT * FROM tmp_mysqli_helper_test WHERE id > ?";
$result = prepared_select($db, $sql, [1]);
while ($row = $result->fetch_assoc())
{
echo "{$row['id']}: {$row['name']}\n";
}
# Getting one row
$id = 1;
$sql = "SELECT * FROM tmp_mysqli_helper_test WHERE id=?";
$row = prepared_select($db, $sql, [$id])->fetch_assoc();
echo "{$row['id']}: {$row['name']}\n";
# Update
$id = 1;
$new = 'Sue';
$sql = "UPDATE tmp_mysqli_helper_test SET name=? WHERE id=?";
$affected_rows = prepared_query($db, $sql, [$new, $id])->affected_rows;
echo "Affected rows: $affected_rows\n";
# Getting an array of rows
$start = 0;
$limit = 10;
$sql = "SELECT * FROM tmp_mysqli_helper_test LIMIT ?,?";
$all = prepared_select($db, $sql, [$start, $limit])->fetch_all(MYSQLI_ASSOC);
foreach ($all as $row)
{
echo "{$row['id']}: {$row['name']}\n";
}
function prepared_query($mysqli, $sql, $params, $types = "")
{
$types = $types ?: str_repeat("s", count($params));
$stmt = $mysqli->prepare($sql);
$stmt->bind_param($types, ...$params);
$stmt->execute();
return $stmt;
}
function prepared_select($mysqli, $sql, $params = [], $types = "") {
return prepared_query($mysqli, $sql, $params, $types)->get_result();
}
$time1 = microtime(true) - $start;
printf("Цикл 1 выполнялся %.6F сек.\n", $time1);