Do I need to type similar SQL query for every query?

I have a page where I want to use a very similar SQL query a number of times. The only difference will be that the WHERE clause will be slightly different, i.e. sometimes it will be “WHERE term_taxonomy_id = 4”, sometimes “WHERE term_taxonomy_id = 7” and so on.

At the moment I have written out the whole query each time, but do I need to do this? Is there a way that I can use the same piece of code for all the queries, and just add the extra WHERE clause?

Here’s the original 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' AND term_taxonomy_id = 6 ";
$clause = array();
for( $i = 0; $i < 7; $i++ ) {
$clause[] = "DATE_ADD(CURDATE(), INTERVAL $i DAY) BETWEEN DATE(start) AND DATE(end)";
}

$sql .= "AND (" . implode(' OR ', $clause) . ")"; 
$sql .= ' ORDER BY start DESC';
$result = mysql_query($sql) or trigger_error($sql . ' has failed. <br />' . mysql_error()); //pull data from database.

You could wrap it in a simple function to return the query for use each time:


function my_taxonomy_query($term_taxonomy_id) {
   $term_taxonomy_id = (int)$term_taxonomy_id;

$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' AND term_taxonomy_id = $term_taxonomy_id ";

$clause = array();
for( $i = 0; $i < 7; $i++ ) {
$clause[] = "DATE_ADD(CURDATE(), INTERVAL $i DAY) BETWEEN DATE(start) AND DATE(end)";
}

$sql .= "AND (" . implode(' OR ', $clause) . ")"; 

   return $sql;
}

To use:


$sql = my_taxonomy_query(6);

BTW, what is the purpose of the $clause section? Are you just looking for all posts (meeting other criteria) that are to be published in the next 7 days?
If so why not use a single clause specifying 7 days, rather than 7 clauses specifying 1 day?

Hey man, cheers for the help. I’m going to try this out now.

The reason for the clause is because later I am outputting the events grouped by each day. There’s a load of code for this, but this is how it starts:

$dates = array(); //define dates array.
for($i = 0; $i < 7; $i++) {
$dates[] = date('m/d/Y', strtotime("+$i days")); //fill dates array with every date from now until 7 days from now.
}
foreach($dates as $days) { //go through dates array.

Do you think it’s necessary to also have the clause as well as using this array later. My skills with php and MySQL are still basic, and so some of my code is trial and error rather than actual knowing it’s the best way of doing something?

Would it be easier if I gave you the entire code?

Best,
Russ

I have a page where I want to use a very similar SQL query a number of times. The only difference will be that the WHERE clause will be slightly different, i.e. sometimes it will be “WHERE term_taxonomy_id = 4”, sometimes “WHERE term_taxonomy_id = 7” and so on.

This sentence suggests you are looking for this kind of statement:


....WHERE term_taxonomy_id IN (4,7)";