PHPize Online / SQLize Online  /  SQLtest Online

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