Jump to content
lional

MySqli with foreach loop

Recommended Posts

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

  • Like 1

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.