Hi,
I have a SQL query which I have posted below. On a page on my site I want to use this query many times, each time with a slightly different WHERE clause, so that one time it will say “WHERE cat_id = 6” the next time “WHERE cat_id = 7” and so on. The only thing is that I am wary that having lots of queries will take up a lot of memory and make the page load very slowly.
Is there anyway that I can re-use parts of the query that have already been processed, but then just add the WHERE clause each time for the data. Basically, what’s the best way of doing this in terms of performance?
Thanks! Here’s the code:
$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
WHERE post_status = 'publish' ";
$clause = array();
for( $i = 0; $i < 365; $i++ ) {
$clause[] = "DATE_ADD(CURDATE(), INTERVAL $i DAY) BETWEEN DATE(start) AND DATE(end)";
}
$sql .= "AND (" . implode(' OR ', $clause) . ")";
$sql .= ' ORDER BY start DESC';
$perf_result = mysql_query($sql) or trigger_error($sql . ' has failed. <br />' . mysql_error());