Managing multiple SQL statements in loop

Question

I have 3 sql statements that I need to print to a html table.

1: selects all employee initials that has made an order between two dates.

2: sums up the count of each employee's orders between two dates.

3: sums up each employee's total amount of money spent between two dates.

All data is in a table: Orders(orderNr, itemNr, orderAmount, employee, date, price)

Now, I have to print all three into a table, and I am struggling to figure out how to loop through them correctly while having all the right resultsets.

The table should show: Employee - Amount of orders - Total price

Any help is greatly appreciated.

Hopefully you have the employee ID stored as reference in the other tables? Because IF SO, then you just loop through the first result set and call all data from the other result sets that match the employee ID. — Epodax
– Epodax, Commented Aug 14, 2015 at 9:45
As Epodax said call from ID's or look into mySQL JOIN to conform your query and return all the data in one hit — Izion
– Izion, Commented Aug 14, 2015 at 9:46
I have all the employee initials from an external mssql db the company uses. I have two mysql databases myself, one for the orders and one with the list of all the items available. I just cant figure out a way to properly loop through the employee while having the count and sum values available. — H4rdstyler
– H4rdstyler, Commented Aug 14, 2015 at 9:49
Well ive only built the first one, which returns all employee initials. Im unsure of how to select the sum and max values where employee=x, because ive read that calling sql statements inside loops is a bad practice. — H4rdstyler
– H4rdstyler, Commented Aug 14, 2015 at 9:52

Arun Krish · Accepted Answer · 2015-08-14 10:38:28Z

1

Try this

   SELECT employee, COUNT(DISTINCT(orderNr)) AS orders_count, SUM(price) AS total_price FROM Orders WHERE
   date BETWEEN '$startdate' AND '$enddate' GROUP BY employee

edited Aug 14, 2015 at 10:38

answered Aug 14, 2015 at 9:55

Arun Krish

2,1531 gold badge12 silver badges15 bronze badges

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

1 Comment

H4rdstyler Over a year ago

How do you call the count and sums then? I first run select, then query, and then fetch array. If i fetch array with $row, what calls do i have to make to get the results for sum and count?

Collectives™ on Stack Overflow

Managing multiple SQL statements in loop

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related