SQL/PHP: Update result from select query

Question

I've been trying to get this working forever now and I can't seem to get it to work. I need to update a column in my tours table if there are less than 8 reservations for it in the tours_reservations table when the tour is next week. I have a select statement that gives me the result I want (the tour that needs to be updated) but I have no idea how to process this result in my update statement. Here's my select statement:

$date = new DateTime("+ $starting days");
    $day = $date->format('Y-m-d');

    $this->db->query("SELECT t.t_id, t.name, t.status, COUNT( r.reservation_id ) AS c
                      FROM tours t
                      JOIN tours_reservations r ON t.t_id = r.tour_id
                      WHERE DAY =  '$day'
                      GROUP BY t.t_id
                      HAVING COUNT( c ) <=8");

With this I get all events that start 7 days from now which have 8 or less reservations. The tours table has a status field which will be updated to "cancelled". How do I process this in an update query? Any help would be greatly appreciated.

Thanks in advance.

Answer 1

Assuming your initial query is correct.

$query = $this->db->query("SELECT t.t_id, t.name, t.status, COUNT( r.reservation_id ) AS c
                      FROM tours t
                      JOIN tours_reservations r ON t.t_id = r.tour_id
                      WHERE DAY =  ?
                      GROUP BY t.t_id
                      HAVING COUNT( c ) <=8", array($day));

foreach ($query->result() as $row)
{
   $this->db->query("UPDATE tours SET status = 'cancelled' WHERE t_id = ? ", array($row->t_id));
}

Also added the parameter binding.

Edit: there are better ways to do this ofcourse without executing the update in a loop.