Daniel Robinson Daniel Robinson - 6 months ago 6
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
idstylist=%s",
$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

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.

Comments