结合这两个查询

Question

I have 2 queries.

public function fetchEvents(){

    $events = $this->cmsdb->query("SELECT * FROM tickets_events WHERE `active` = 1", true); //true just means if only one row is returned it will be key [0] so I can loop no matter how many rows returned

if(empty($events)){
    return array();
    }
    foreach ($events as $k => $event) {
        $events[$k]['items'] = $this->cmsdb->query("SELECT *, FROM_UNIXTIME(date, '%W, %D %M %Y') as dateString
                               FROM tickets_items WHERE eventId = '" . intval($event['id']) . "'
                               AND public = 1 AND confirmed = 1 AND date > '" . time() . "'
                               ORDER BY date ", true);
    }
    return $events;
}

I've simplified the first query, I pass some arguments to the function to filter what events it returns, but that part isn't relevant to this question.

The problem I have if I want to get every event and it's items, it's doing a lot of queries (there are 691 events currently). Is there anyway I can combine these queries together?

Answer 1

You can JOIN the tables together to get the same answer. It should be a lot better performing, as well:

SELECT 
     i.*
    ,FROM_UNIXTIME(i.date, '%W, %D %M %Y') as dateString
FROM tickets_items  i
JOIN tickets_events e
  ON i.eventId = e.id
WHERE i.public    = 1
  AND i.confirmed = 1
  AND e.active    = 1
  AND date > CURRENT_TIMESTAMP
ORDER BY date