lional Posted March 10, 2016 Share Posted March 10, 2016 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 Quote Link to comment Share on other sites More sharing options...
cyberRobot Posted March 10, 2016 Share Posted March 10, 2016 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()); ?> Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted March 10, 2016 Share Posted March 10, 2016 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. 1 Quote Link to comment Share on other sites More sharing options...
Psycho Posted March 10, 2016 Share Posted March 10, 2016 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()); ?> Quote Link to comment Share on other sites More sharing options...
Jacques1 Posted March 10, 2016 Share Posted March 10, 2016 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); } Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.