Daniel Robinson Daniel Robinson - 2 years ago 87
MySQL Question

sql query how to work out highest earner in a day

Im currently looping through the stylists for a particular date and trying to find which one has made the most money. I'm struggling to get past the part i'm up to. I have looped through my query but now I don't know what to do with it. Once I have my array of price how do i order it like I need to?

foreach ($staffArray as $staff) {
$query = sprintf(
"SELECT SUM(bookingPrice) as price
FROM booking
WHERE idsystem=%s AND
idbranch=%s AND
bookingDate=%s AND
idStatus=%s AND
$this->db->GetSQLValueString($this->mysystem, "int"),
$this->db->GetSQLValueString($this->branch, "int"),
$this->db->GetSQLValueString($this->dateToday, "date"),
$this->db->GetSQLValueString('1', "int"),
$this->db->GetSQLValueString($staff, "int")
$result = $this->db->query($query);
$row = $this->db->fetch_assoc($result);

Answer Source

To get this particular code working, you could do something like:

$prices = array(); // here we're going to store your data
foreach ($staffArray as $staff) {
    $query = sprintf("...", ...); 
    $result = $this->db->query($query);
    $row = $this->db->fetch_assoc($result);
    $prices[/* name of stylist */] = /* value to sort on */;
asort($prices); // Sort the array, keep the keys
foreach ($prices as $stylist => $price) {
    echo "$stylist: $price";

However, it would be better to use one query for everything instead of that foreach loop, to let MySQL sort instead of PHP, and to use prepared statements instead of sprintf.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download