MySqli with foreach loop

[lional]

Hi

I am trying to do a foreach loop withing a mysqli query

 

I had it working with mysql but am struggling doing it with mysqli

 

here is my code so far

$query = mysqli_query($conn,'SELECT * FROM cooking_classes WHERE class_id IN (' . 
			foreach ($_SESSION['cart'] as $key => $value) {
			$query .= $key . ',';
			}
			$query = substr ($query, 0, -1) . ')';
			$result = mysqli_query($query) or die(mysqli_error());

Thanks in advance

 

Lional

[cyberRobot]

You can try something like this (untested):

<?php
$query = 'SELECT * FROM cooking_classes WHERE class_id IN (' . implode(', ', array_keys($_SESSION['cart'])) . ')';
$result = mysqli_query($conn, $query) or die(mysqli_error());
?>

[mac_gyver]

and since these key values likely came from user supplied data, you should use a prepared query to supply the data values at the time the query gets executed in order to avoid sql injection.

 

this would involve dynamically building the IN (....) term with the correct number of place holders and dynamically binding the input data. doing this with mysqli is a pita. using the PDO extension, instead of mysqli, is all around easier and more consistent.

[Psycho]

and since these key values likely came from user supplied data, you should use a prepared query to supply the data values at the time the query gets executed in order to avoid sql injection.

 

this would involve dynamically building the IN (....) term with the correct number of place holders and dynamically binding the input data. doing this with mysqli is a pita. using the PDO extension, instead of mysqli, is all around easier and more consistent.

 

When dealing with an indeterminate number of variables, using prepared queries is cumbersome. Although some would disagree, a possible solution would be to force the values to be integers to ensure they are safe to use in a query. Non-numerical values will default to 0, which (depending on the context) would not match any values anyway.

 

 

<?php
$classIDs = array_filter(array_keys($_SESSION['cart']), 'intval');
$query = 'SELECT * FROM cooking_classes WHERE class_id IN (' . implode(', ', $classIDs) . ')';
$result = mysqli_query($conn, $query) or die(mysqli_error());
?>

[Jacques1]

Casting an SQL integer into a PHP integer can lead to truncation, because MySQL can go up to 64 bit unsigned, whereas PHP is limited to either 32 bit signed or 64 bit signed (depending on the platform). A better (but still hacky) approach would be to check if the string only contains decimal digits.

 

However, a prepared statement is indeed the way to go:

$class_ids = [10, 20, 30, 40];

$cooking_classes_stmt = $database_connection->prepare('
  SELECT
    class_id -- select *specific* columns, not just "*"
  FROM
    cooking_classes
  WHERE
    class_id IN ('.implode(',', array_fill(0, count($class_ids), '?')).')
');

$stmt_params = [];
$stmt_params[] = str_repeat('i', count($class_ids));
for ($i = 0; $i < count($class_ids); $i++)
{
    $stmt_params[] = &$class_ids[$i];
}

call_user_func_array([$cooking_classes_stmt, 'bind_param'], $stmt_params);

$cooking_classes_stmt->execute();

$cooking_classes_stmt->bind_result($class_id);

while ($cooking_classes_stmt->fetch())
{
    var_dump($class_id);
}