CODEWITHSUNDEEP
phpmysqliprepared-statement
Marco
Marco

Reputation: 2737

Dynamically Bind Params in Prepared Statements with MySQLi

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();

Upvotes: 0

Views: 788

Answers (1)

Michal Brašna
Michal Brašna

Reputation: 2323

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%?

Upvotes: 1

Related Questions