Help with MySQL Query

[gckorn]

---

System: The following message was cut out of thread at: http://www.webmasterworld.com/php/3238928.htm [webmasterworld.com] by eelixduppy - 3:20 pm on May 11, 2008 (est -4)

---

OK. So can anyone say how that will work here. I need to check for and display events that are live this month, next month, etc., going out as many months as necessary. My current code is as follows:

$cond = " (monthname(startdate) = '$month' or monthname(enddate) = '$month' or
(monthname(date_add(startdate, INTERVAL 1 MONTH)) = '$month' and monthname(date_sub(enddate, INTERVAL 1 MONTH)) = '$month'
and monthname(startdate) <> monthname(enddate))) and category = '$cat' and state = 'Maine' ";

$query = "select id, title, linkurl, city, state, postalcode, phone, format(longitude, 6) as longitude,
format(latitude, 6) as latitude, description, date_format(startdate, '%b %d, %Y') as startdate1,
date_format(enddate, '%b %d, %Y') as enddate1, photourl1, category, displayin, startdate, enddate,
date_format(startdate, '%Y') as fyear, datediff(enddate, current_date) as expireddays, author
from maine_festivals where ";

$query .= $cond;
$query .= " order by category, startdate, enddate ";

The problem is that this does will pull an event for its START month, but won't pull/display it for its following months. It only displays again on the END month. For an example, see <snip>... here, event Urban Seen displays in APR, but not again until its closing month in AUG. (Note that this code works properly for 3 month events .. it shows beginning, middle and end month).

I think I need to test for multiple conditions, e.g., condit1, condit2, condit3, etc, for different intervals, e.g, Interval 1, Interval 2, Interval 3, etc., but I don't know how to combine them at the $query level or otherwise.

Thanks in advance,
gck

[edited by: eelixduppy at 7:19 pm (utc) on May 11, 2008]
[edit reason] no URLs, please [/edit]

[cameraman]

It would be easier if you stored the dates as unix timestamps. You might be able to use the BETWEEN [dev.mysql.com] operator but I've never experimented with it.