I realised today that some of the events that should have been showing up on a website weren't, and eventually realised the reason was because that 'term_taxonomy_id' in the 'wp_term_relationships' table is not always the same value as the category_id. Strangely those two fields matched for the first year the site was running but have now fallen out of sync. So, I know need to match the category_id to the terms_id field in the 'wp_term_taxonomy' table, which is linked to the 'wp_term_relationships' table via the 'term_taxonomy_id' field. Does that all make sense? That was just the intro!
So, I've tried adding an extra join to my custom select query but now it's returning no values. I've tried all kinds of different joins but still no luck. Does anyone know what I'm doing wrong? Here's the SQL query below, which includes the extra LEFT JOIN for this new field.
$category_ID = get_category_id(single_cat_title("", false));
$sql = "SELECT post_id,
DATE_FORMAT(start,'%m/%d/%Y') AS eventStart,
DATE_FORMAT(end,'%m/%d/%Y') AS eventEnd,
DATE_FORMAT(CURDATE(),'%m/%d/%Y') AS today,
DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 7 DAY),'%m/%d/%Y') AS endWeek
FROM wp_ec3_schedule s
JOIN wp_posts p ON p.ID = s.post_id
JOIN wp_term_relationships t ON t.object_id = s.post_id
LEFT JOIN wp_term_taxonomy x ON x.term_taxonomy_id = t.term_taxonomy_id
WHERE post_status = 'publish' AND term_taxonomy_id = $category_ID AND end > CURDATE()";
$sql .= ' ORDER BY start ASC';
$sql .= ' LIMIT 0, 10';
Once I have this query working I was then going to change 'term_taxonomy_id' to 'term_id' in my WHERE clause, which should then bring all the correct values. But for now, I just want to get some records back. What's wrong with my query?