mysql - PHP check if a certain number of rows are inserted one week before launch date in database -

first of all: i'm sorry if title vague (because is), have no idea how formulate question in 1 sentence...

here's question: school project have build website can reservate special tours @ museum. if 8 or less reservations have been made 1 of these tours week before takes place, it's canceled. have no idea how accomplish this! i'm stuck @ checking if there 8 or less reservations week prior event. in database have 2 tables this: tours , tour_reservations. here's table structure clarify database setup i'm working with.

tours

http://imgur.com/nxkyval,lushmba

tours_reservations

http://imgur.com/nxkyval,lushmba#1

so, tour_id in tours_reservations linked t_id tours. want do, count rows 1 tour (tour_id) in tours_reservations table , see how many rows have been inserted 1 week before day field in tours table.

i'm using mvc framework codeigniter. here's piece of code i've been playing with:

controller

public function check_sold_ticket() {     $date = $this->tour_model->get_tour_date();      $last_week = strtotime('-1 week');      $result = $this->tour_model->get_reservations();      if ($date > $last_week && count($result) < 8) {         echo "do something";     }     else {         echo "whoops";     }       $this->load->view('tour/check_sold_tickets'); } 

model

public function get_tour_date() {     $query = $this->db->query("select day tours");      return $query->result(); }  public function get_reservations() {     $query = $this->db->query("select reservation_id tours_reservations");      return $query->result(); } 

the code incorrect, have been able come far.

i'm sorry asking such big question in accomplishing appreciated.

thanks in advance.

something should trick:

public function get_tour_date($starting = 7) {     //you need have php 5.3     $date = new datetime("+ $starting days");     $day = $date->format('y-m-d');      $query = $this->db->query("                     select t.t_id, count(r.reservation_id) c                      tours t                      join tour_reservations r on t.t_id=r.tour_id                      day = '$day'                      group t.t_id");      return $query->result(); } 

i haven't tested should return array of tour_ids start week today count how many reservations has.

if explain of can do.

hope helps!