Trouble with looping from a mysql query

General PHP Help
#1

Hi. Can you explain why I am getting this behavior with my loops?

Order 145 has 2 items. Order 146 has one item. Order 147 has 2 items, and order 149 has 3 items.

Here’s the results I’m getting:

current modal content for order 145: Profit: 4.97 test test test test test
current modal content for order 146: Profit: 14.57 test test test test test
current modal content for order 145: Profit: 2.25 test test test test test
current modal content for order 147: Profit: 19.97 test test test test test
current modal content for order 147: Profit: 19.97 test test test test test Profit: 24.95 test test test test test
current modal content for order 149: Profit: 29.55 test test test test test
current modal content for order 149: Profit: 29.55 test test test test test Profit: -5.50 test test test test test
current modal content for order 149: Profit: 29.55 test test test test test Profit: -5.50 test test test test test Profit: 29.94 test test test test test

And here are the results I am expecting:

current modal content for order 145: Profit: 4.97 test test test test test Profit: 2.25 test test test test test
current modal content for order 146: Profit: 14.57 test test test test test
current modal content for order 147: Profit: 19.97 test test test test test Profit: 24.95 test test test test test
current modal content for order 149: Profit: 29.55 test test test test test Profit: -5.50 test test test test test Profit: 29.94 test test test test test

Here’s my code, and I’ve tried multiple other varieties of swapping around the wording and loops but nothing gives me the result I expect. The code on the page is very long so I will just paste the relevant part:

/*Inside a While Loop fetching results from database:*/
if ($current_modal != $row['sale_id']) {
$modal_data_content = '';
}
$modal_data_content .= '
                    <tr>
                        <td>Profit: '.$profit.'</td>
                        <td>test</td>
                        <td>test</td>
                        <td>test</td>
                        <td>test</td>
                        <td>test</td>
                    </tr>';
$current_modal = $row['sale_id'];
echo 'current modal content for order '.$sale_id.': '.$modal_data_content. '<br>';
#2

What’s the query that is getting those results?

#3

I’ll show you the query now, but it’s well constructed and shouldn’t be changed:

$sql = "SELECT 

sales.sale_id AS sale_id,
sales.sale_date AS sale_date,
sales.client_id AS client_id,
sales.sale_total AS sale_total,
inventory.inventory_id AS inventory_id,
inventory.sales_price AS sales_price,
sales_products.qty AS qty,
sales_products.price_sold_at AS price_sold_at,
sales_products.profit AS profit,
CONCAT(clientele.first_name, ' ', clientele.last_name) AS full_name,
inventory.product_name AS product_name


FROM sales_products 
LEFT JOIN sales ON sale_id = sale_reference 
LEFT JOIN clientele ON clientele.client_id = sales.client_id 
LEFT JOIN inventory ON inventory.inventory_id = sales_products.inventory_id 
-- LEFT JOIN products_and_packaging ON products_and_packaging.name = inventory_id.product_id 
WHERE sales.admin_user_id = ?
";
#4

I could simply add a
GROUP BY sales_id
clause inside the SQL, but that would give me incorrect results because I need a detailed result. The problem I’m facing is trying to group the product details under each order, but instead, it keeps iterating each product under a new line instead of grouping it under a single order.

#5

Move that line into the if statement as well, so it only outputs when the sale_id has changed.

#6

If I do that, no results show:

current modal content for order 145:
current modal content for order 146:
current modal content for order 145:
current modal content for order 147:
current modal content for order 149:
#7

So the content variable is in an inner scope and needs to be moved outside of the loop so it can be built on. Likewise, the current sales Id needs to be set outside and before the if statement as well so it can index where it is in the loop.

#8

To use the if ($current_modal != $row['sale_id']) { one-shot logic, your query will need an ORDER BY term, to get the same sale_id rows adjacent in the result set.

It is usually simpler and clearer if you index/pivot the data, using the sale_id as the main array index, when you retrieve the data, then simply loop over the arrays of rows of data for each sale_id value to produce the output. Also, by having the data for each sale_id as a sub-array, you can use php’s array functions to do things like get a sum of values in a particular column.

#9

I tried playing around with the scope of the variable $modal_data_content , but of course if I remove it from the while loop which spits out the sql results - that would only catch the first or last field in the result set. How would you presume that I should go about with changing the scope?

Sponsor our Newsletter | Privacy Policy | Terms of Service