How to run similar SQL query multiple times without losing too much memory!

[slaterino]

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());

[trq]

one time it will say "WHERE cat_id = 6" the next time "WHERE cat_id = 7"

 

Just get all your data in one query. Running multiple queries like that is ridiculous.