Changing a foreach to a while loop to save the mysql queries

Question

I have this loop at the moment, but it involves calling the database 5 to 10 times. Is there a way I can just call it once, perhaps with a while loop, or any other alternatives anyone can think of, as it is slowing down the action.

foreach($_SESSION['cart_array'] as $each_item) {
   $item_id = $each_item['item_id'];
   $each_quantity = $each_item['quantity'];
   $item_qry = mysql_query("SELECT * FROM `products` WHERE `products_id`=$item_id");
   $item_row = mysql_fetch_assoc($item_qry);
   $product_price = $item_row['products_price'];
   $total_price += $product_price * $each_quantity;
}

bwoebi · Accepted Answer · 2013-07-26 00:15:08Z

2

yes, you could just select all the rows at once:

$total_price = 0;
if (!empty($_SESSION["cart_array"])) {
    $query = "SELECT `products_price`, `products_id` FROM `products` WHERE `products_id` IN (";
    $query .= implode(", ", array_map(function ($item) { return $item["item_id"]; }, $_SESSION['cart_array']));
    $query .= ")";
    $result = mysql_query($query);

    while ($row = mysql_fetch_assoc($result))
        $items[$row["products_id"]] = $row["products_price"];

    foreach ($_SESSION["cart_array"] as $item)
        $total_price += $item["quantity"] * $items[$item["item_id"]];
}

edited Jul 26, 2013 at 0:15

answered Jul 25, 2013 at 23:58

bwoebi

23.8k5 gold badges64 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Source Over a year ago

I renamed $data to $item, as I assumed that was an error, but I get " Notice: Undefined offset: 275 in C:\xampp\htdocs\customers\msl\test-array3.php on line 19"

Scuzzy Over a year ago

You could also build up a CSV list of values and use WHERE products_id IN (1,2,3,4,5)

Source Over a year ago

@Scuzzy what would be the loop then?

Aleksandar Popovic · Accepted Answer · 2013-07-26 00:15:43Z

0

One query solution:

$item_id_array = array();
$item_to_quant = array();
foreach($_SESSION['cart_array'] as $each_item)
{
    $item_id_array[] = $each_item['item_id'];

    $item_to_quant[$each_item['item_id']] = $each_item['quantity'];
}

$item_qry = mysql_query("SELECT * FROM `products` WHERE `products_id` IN (" . implode(',', $item_id_array) . ")");


$total_price = 0;
$results     = array();

while ($item_row = mysql_fetch_assoc($item_qry))
{
    //add total price to result array
    $item_row['total_items_price'] = $item_row['products_price'] * $item_to_quant[$item_row['products_id']];

    $results[] = $item_row;

    $total_price += $item_row['total_item_price'];
}

You get results array and total price var as results.

answered Jul 26, 2013 at 0:15

Aleksandar Popovic

9288 silver badges17 bronze badges

3 Comments

Source Over a year ago

Would this produce a faster result from my initial foreach?

bwoebi Over a year ago

@Source it's ± as fast as my implementation

Aleksandar Popovic Over a year ago

Is one query, should be faster if you have more than one product. But not that fast for single product :) Also make sure you have index on products_id field in DB.

Collectives™ on Stack Overflow

Changing a foreach to a while loop to save the mysql queries

2 Answers 2

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related