Use more placeholders in WHERE statement after IN statement with arrays

Tags: , ,



I need more placeholders to be added to the following query. Is this possible? I have no idea where to begin and I can not find any options on the Internet.

The query now:

$in2  = str_repeat('?,', count($arrayid) - 1) . '?';
$sql2 = "SELECT COUNT(id) AS totalacc FROM account WHERE id IN ($in2) ";
$stmt2  = $mysqli->prepare($sql2);
$types2 = str_repeat('i', count($arrayid));
$stmt2->bind_param($types2,...$arrayid);
$stmt2->execute();
$stmt2->bind_result($row['totalacc']);
    while($stmt2->fetch()) $totalacc = $row['totalacc']; 

The query I am aiming for:

$countname1 = '(Hallo)';
    $countname = trim(filter_var("%{$countname1}%", FILTER_SANITIZE_STRING));
$in2  = str_repeat('?,', count($arrayid) - 1) . '?';
$sql2 = "SELECT COUNT(id) AS totalacc FROM account WHERE id IN ($in2) AND name LIKE ?";
$stmt2  = $mysqli->prepare($sql2);
$types2 = str_repeat('i', count($arrayid));
$stmt2->bind_param($types2,s,...$arrayid,$countname); // Will never work, but how to do this? 
$stmt2->execute();
$stmt2->bind_result($row['totalacc']);
    while($stmt2->fetch()) $totalacc = $row['totalacc']; 

Answer

The easiest solution would be to use PDO instead of mysqli. This would be so much easier.

If you are stuck with mysqli then you can achieve a similar thing by simply ignoring the types and appending your result to the array.

$countname1 = '(Hallo)';
$countname = "%{$countname1}%";
$in2 = str_repeat('?,', count($arrayid) - 1) . '?';
$sql2 = "SELECT COUNT(id) AS totalacc FROM account WHERE id IN ($in2) AND name LIKE ?";
$stmt2 = $mysqli->prepare($sql2);

$arrayid[] = $countname;
$stmt2->bind_param(str_repeat('s', count($arrayid)), ...$arrayid);
$stmt2->execute();
$stmt2->bind_result($totalacc);
$stmt2->fetch();

You could even write a function to abstract from all of this code.



Source: stackoverflow