[SOLVED] MySQL select statement help

[patricio555]

I am using a calendarix calendar on my band site and I found that I could create an "upcoming events" mini table listing events that match a specified category (ie "shows"). (I don't like their mini calendar - it just doesn't work for what I need - overcomplicated.)

 

In the calendar db, there is a table called calendar_events and in that table there are two columns "day" and "month" which are stored as integers. I have built a simple select statement and a loop to print the various records in a small table on the side of the page.

 

I am trying to figure out how to construct a query with a where clause that limits the entries to the events "greater than or equal to" today's date, and I am having no luck.

I have tried using the php function iDate with the format (dm) which should give me the day and month in integer format. What I cannot figure out is how to split the day and month into the select query (ie SELECT * FROM calendar_events WHERE day>= ? AND month>= ? AND category="2" ORDER BY date)

 

The question marks above are where I cannot figure out what to use. I tried using .iDate(d) and .iDate(m) in their place but this does not seem to work.

 

I am kind of a noob, but I know enough to get myself into trouble. I am hoping some kind soul will help me out on this one.

 

Thanks in advance.

[ManiacalV]

Try something like this maybe?

 

<?PHP
$date_arr = getdate();

$query = "  SELECT * FROM calendar_events WHERE day >= '" . $date_arr['mday'] . "' AND month >= '" . $date_arr['mon'] . "' AND category = '2' ORDER BY date";

echo $query;
?>

[patricio555]

Thanks for your reply. While your idea didn't work, it did lead me to discover what was going wrong...now it's just a matter of figuring out how to fix it.

 

It doesn't work because I am comparing the "day" column against today's date. Thusly, if one show is on 5/22 and today is 4/30 then it will return no records because 22<30. (Though they'd be fine on the 1st!).

 

My idea is that if I could somehow get the yearday (0-365) from the month(int) and day(int) columns, then I could compare that against today's date using getdate(yday)....only problem is, I cannot figure out how to do this. I am pretty terrible at math.

 

Right now, it is set up to show all records >= current month, which is fine, except events don't expire when they have passed - they hang around til the end of the month. Not much wrong with that, but the proble is just buggin me!

 

 

 

[patricio555]

GOT IT!

 

Here is the successful query for anyone interested:

 

$sql = "SELECT * FROM calendar_events

WHERE (day >= '" .date('j', time()) ."' AND month= '".date('n', time())."' and cat='2' ) OR

(cat='2' AND month > '".date('n', time())."') ORDER BY  month,day ASC

LIMIT 0 , 30";