Need some help converting to a prepared statement

[casey]

I’m trying to convert a working mysql statement to a prepared statement. This is my first one and I am confused.

Regular:

<?php

$db = new mysqli("localhost","xxx","xxx","xxx");

if ($db->connect_error) {
    die("Connection failed: " . $db->connect_error);
}

$searchTerm = $_GET['term'];

$query = $db->query("SELECT service_address FROM addresses WHERE service_address LIKE '%".$searchTerm."%'");

$serviceAddress = array();
if($query->num_rows > 0){
    while($row = $query->fetch_assoc()){
        $data['id'] = $row['id'];
        $data['value'] = $row['service_address'];
        array_push($serviceAddress, $data);
    }
}

echo json_encode($serviceAddress);
?>

Prepared:

<?php
$mysqli = new mysqli("localhost","xxx","xxx","xxx");
if($mysqli->connect_error) {
  exit('Error connecting to database');
}
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli->set_charset("utf8mb4");

$arr = [];
$stmt = $mysqli->prepare("SELECT service_address FROM addresses WHERE service_address LIKE ?");
$stmt->bind_param("s", $_GET['term']);
$stmt->execute();
$result = $stmt->get_result();
$serviceAddress = array();
while($row = $result->fetch_assoc()) {
	$data['id'] = $row['id'];
	$data['value'] = $row['service_address'];
	array_push($serviceAddress, $data);
}
echo json_encode($serviceAddress);
$stmt->close();
?>

[phdr]

I am confused.

The person(s) who designed the mysqli prepared query interface was confused as well. You should forget about the mysqli extension and use the much simpler and more consistent PDO extension.

The most immediate problem is you need to include any % wild-card characters as part of the bound input parameter, and since you cannot directly do that with the mysqli extension without using another variable, this is another reason to switch to the much better designed PDO extension.

When you switch to use PDO, when you make the connection, set the error mode to exceptions, set emulated prepared queries to false, and set the default fetch mode to assoc. Also, use implicit binding, by supplying an array of the input values to the ->execute([…]) method call.

Edit: another problem is you are not SELECTing the id column (in both versions of the posted code.)