How to properly use for loop when fetching data in mysql and storing it in array then implode it to display in my jquery data table?

Question

My query for that is this: $get_product = $mysqli->query("SELECT b.menu_name AS store, a.category AS category, a.product AS product, a.description AS description, a.stamp AS stamp, a.id AS id, a.image AS image FROM lfood a JOIN branch_map b ON a.menu_map = b.id WHERE a.status = 1 ".$searchQuery." ORDER BY ".$columnName." ".$columnSortOrder." LIMIT ".$row." , ".$rowperpage." ");

Then i fetch them like this:

$data = array();
while ($row = $sms_subs->fetch_assoc()) {
    $menu_id[]      = $row['id'];
    $image[]        = $row['image'];
    $store[]        = $row['store'];
    $category[]     = $row['category'];
    $product[]      = $row['product'];
    $description[]  = $row['description'];
    $stamp[]        = $row['stamp'];
}

for ($i=0; $i < count($menu_id); $i++) {
    $get_price = $mysqli->query("SELECT * FROM lfood_price WHERE menu_id = '".$menu_id[$i]."'");
    while ($row_price = $get_price->fetch_assoc()) {
        $price[] = $row_price['label'].' : '.number_format($row_price['price']);
    }
    $new_price = implode("<br>", $price);
    $data[] = array(
        "store"         => $store[$i],
        "category"      => $category[$i],
        "product"       => $product[$i],
        "description"   => $description[$i],
        "price"         => $new_price,
        "stamp"         => date("F d Y h:i:s A", strtotime($stamp[$i])),
        "actions"       => "sample"
    );
}

I have another table that stores all the prices of my product. And the result weren't what it want, This is the actual result

On my second row of data it displays all records of prices even it doesn't belong to that id. My thought of that is the improper use of my for when fetching prices. Please someone help / enlighten the proper use of for loop in regards of my problem. Thank you so much!

Jordan Jordan · Accepted Answer · 2020-07-23T01:38:19

The issue is $new_price = implode("<br>", $price);

Walk through the for loop: First time through it finds prices for shrimp and olive oil. The price array is now ["FS:570", "PS:1085"] and then it's imploded and saved to the data array. The second time through it gets the prices for New Zealand Muscles. The price array is now ["FS:570", "PS:1085", "FS(16pcs):695", "PS(32pcs):1315"]. It's imploded and saved to the data array.

The fix would be to empty out $price each time through the loop.

for ($i=0; $i < count($menu_id); $i++) {
    $price = [];
    $get_price = $mysqli->query("SELECT * FROM lfood_price WHERE menu_id = '".$menu_id[$i]."'");
    while ($row_price = $get_price->fetch_assoc()) {
        $price[] = $row_price['label'].' : '.number_format($row_price['price']);
    }
    $new_price = implode("<br>", $price);
    $data[] = array(
        "store"         => $store[$i],
        "category"      => $category[$i],
        "product"       => $product[$i],
        "description"   => $description[$i],
        "price"         => $new_price,
        "stamp"         => date("F d Y h:i:s A", strtotime($stamp[$i])),
        "actions"       => "sample"
    );
}

However, you should probably look into multidimensional arrays, foreach loops, and mysql joins. All of those would greatly simplify your code.

How to properly use for loop when fetching data in mysql and storing it in array then implode it to display in my jquery data table?

1 Answers