使用MySQLi动态绑定准备语句中的参数

Question

So like the title says, i've search here and tried almost everything, with no success.

I've try to test something very simple before going more deep, to make sure it works. But even at its simplest, i always get 0 results and i know there are 67 results.

What's wrong with my code?

Thanks

$conn = connect(); // connect to the db

$a_bind_params = array('love', 'circle');
$a_param_type = array('s', 's');

$totalKeywords = count($a_bind_params);

$q = 'SELECT id, name
    FROM album
    WHERE name LIKE ?';

for ($i = 1; $i < $totalKeywords; $i++) {
    $q .= ' AND name LIKE ?';
}

echo $q; // for testing purposes: verify that query is OK

// bind parameters.
$param_type = '';
$n = count($a_param_type);

for($i = 0; $i < $n; $i++) {
    $param_type .= $a_param_type[$i];
}

/* with call_user_func_array, array params must be passed by reference */
$a_params = array();
$a_params[] = & $param_type;

for($i = 0; $i < $n; $i++) {
  /* with call_user_func_array, array params must be passed by reference */
  $a_params[] = & $a_bind_params[$i];
}

$stmt = $conn->prepare($q);

/* use call_user_func_array, as $stmt->bind_param('s', $param); does not accept params array */
call_user_func_array(array($stmt, 'bind_param'), $a_params);


$stmt->execute();
$stmt->store_result();
$num_rows = $stmt->num_rows;

echo $num_rows; // how many found ?

$stmt->bind_result($id, $name);

while($stmt->fetch()) {
    echo $name;
}

$stmt->free_result();
$stmt->close();
$conn->close(); 

Answer 1

There are not 67 rows with query

SELECT id, name
FROM album
WHERE name LIKE 'love' AND name LIKE 'circle'

There should be OR instead of AND in WHERE clause.

Possibly, you may also need %love% and %circle%?