PHP检查在数据库中的启动日期前一周是否插入了一定数量的行

Question

first of all: I'm really sorry if the title is vague to you (because it is), but I have no idea how to properly formulate my question in one sentence...

Here's my question: for my school project I have to build a website where you can reservate special tours at a museum. If only 8 or less reservations have been made for one of these tours a week before it takes place, it's canceled. I have seriously no idea how to accomplish this! I'm basically stuck at checking if there are 8 or less reservations a week prior to the event. In my database I have two tables for this: tours and tour_reservations. Here's the table structure to clarify the database setup I'm working with.

tours

http://imgur.com/NxkyVaL,LushMBa

tours_reservations

http://imgur.com/NxkyVaL,LushMBa#1

So, the tour_id in tours_reservations is linked to t_id from tours. What I want to do, is count the rows from one tour (tour_id) in the tours_reservations table and see how many rows have been inserted one week before the day field in the tours table.

I'm using the MVC framework CodeIgniter. Here's a 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 FROM tours");

    return $query->result();
}

public function get_reservations() {
    $query = $this->db->query("SELECT reservation_id FROM tours_reservations");

    return $query->result();
}

The code is obviously incorrect, but this is what I have been able to come up with so far.

I'm sorry for asking such a big question but any help in accomplishing this would be GREATLY appreciated.

Thanks in advance.

Answer 1

Something like this should do the trick:

public function get_tour_date($starting = 7)
{
    //You need to have PHP 5.3 for this
    $date = new DateTime("+ $starting days");
    $day = $date->format('Y-m-d');

    $query = $this->db->query("
                    SELECT t.t_id, count(r.reservation_id) as c 
                    FROM tours t 
                    JOIN tour_reservations r ON t.t_id=r.tour_id 
                    WHERE day = '$day' 
                    GROUP BY t.t_id");

    return $query->result();
}

I haven't tested it but essentially it should return an array of tour_ids that start a week today as well as a count for how many reservations it has.

If you would like to explain any of it I can do.

Hope this helps!