Multiple WHERE clause (complex query)

[mm0ckr]

Hi Folks,

I am currently hacking Lucid Calendar and need to do a fairly complex query (complex for me anyway ).

I am adding a function to show the next 5 events on my index page. I use the following sql with PHP

$query = "SELECT * FROM calendar_messages ORDER BY msg_year, msg_month, msg_day LIMIT 0,5";

This works in as far as it gives the first 5 entrys in the DB. I want to limit these to the first 5 AFTER todays date. My script has variables for day, month and year

I know I need to use a WHERE clause here but there will have to be one for month, day and year.

I tried many variations of..

$query = "SELECT * FROM calendar_messages ORDER BY msg_year, msg_month, msg_day LIMIT 0,5 WHERE msg_year >= '$year',msg_month >= '$month', msg_day >= '$day'";[/red]

It does not work.

Can anyone helpme to construct this query?

Thanks in advance

Mike

Table Dump...
CREATE TABLE calendar_messages (
msg_id int(11) NOT NULL auto_increment,
msg_month int(2) NOT NULL default '0',
msg_day int(2) NOT NULL default '0',
msg_year int(4) NOT NULL default '0',
msg_title tinytext NOT NULL,
msg_text text NOT NULL,
msg_poster_id varchar(11) NOT NULL default '0',
msg_recurring tinytext,
msg_active int(1) NOT NULL default '0',
PRIMARY KEY (msg_id)
) TYPE=MyISAM;

 

[danielhozac]

Try

$query = "SELECT * FROM calendar_messages ORDER BY msg_year, msg_month, msg_day WHERE msg_year >= '$year',msg_month >= '$month', msg_day  >= '$day' LIMIT 0,5";

I think that if you use the limit clause before the where clause, the where clause will only apply to the rows returned by the limit clause. //Daniel

 

[mm0ckr]

Hi Danial,

Thanks for the reply. Unfortunately it is still not working with

$query = "SELECT * FROM calendar_messages ORDER BY msg_year, msg_month, msg_day WHERE msg_year >= '$year',msg_month >= '$month', msg_day >= '$day' LIMIT 0,5";

Ther Error is 1064, Syntax error near "WHERE msg_year >= '2002' ..., etc etc. The same error is given when using one WHERE (IE just the year) or all 3 (day, month and Year).

I have scoured the web but am unable to find any help at all about how to apply multiple WHERE conditions. The error message suggests that the variables for the date are being used as planned.

Any further help would be greatly appreciated.#

best Wishes

Mike

 

[sleipnir214]

What are those commas in the where clause supposed to do? Shouldn't they be replace with ANDs? That seems to be where the error is happening. ______________________________________________________________________
TANSTAAFL!

 

[mm0ckr]

Thanks for the reply,

You are absolutely right about using AND's in the WHERE.

I have partly solved the problem as it would appear that the syntax error was caused by the ordering of the conditions, i.e the WHERE clause must come before the ORDER BY and LIMIT.

Now I have a new problem!

This is my SQL

$query = "SELECT * FROM calendar_messages WHERE msg_day >= '$day' AND msg_month >= '$month' AND msg_year >= '$year' ORDER BY msg_year, msg_month, msg_year LIMIT 0,5";

I can not work out how to select all events in the future, when I limit to msg_day it will show the next months events but only after the day (eg if today is the 27th) it will show events for this month and next month but only if the day is less than 27.

I think I may be on the wrong track here, all I am trying to achieve is to pull the next 5 records out of the DB after todays date.

Is there any way I can do this without altering my table or will I need to include a time stamp?

Thanks for the help so far, any urther help is appreciated!

Best Wishes,

Mike

 

[sleipnir214]

Why are you comparing the three parts of the date separately? Why not just compare your date column to a given date?

SELECT * from foo where the_date > '2002-05-15' ORDER BY the_date LIMIT 5

for example? ______________________________________________________________________
TANSTAAFL!

 

[mm0ckr]

Quite simply the date is stpored in 3 seperate fields to allow some features such as re-occuring messages.

I think I'll have to put a seperate date field in the table and compare against this.

A lot of the problem is that I am trying to alter someone else's code (Lucid Calendar - now unsupported) and beef it up to my own requirements.

Saying that, I have learned a lot from this problem but I think you are right about comparing dates, a bit like using a sledge hammer to crack a nut.

Thanks to all for your help.

Best wishes

Mike

 

[danielhozac]

You could try this query:

$query = "SELECT * FROM calendar_messages WHERE CONCAT_WS('-', msg_year, msg_day, msg_month) > '$year-$day-$month' ORDER BY msg_year, msg_month, msg_year LIMIT 0,5";

//Daniel

 

[mm0ckr]

Daniel,

Thank You. Works perfectly. I am away to read more about this now, SQL really is powerful when you scratch below the surface

Just one more question. Does anyone know of a really good tutorial about MySQL that goes into a bit more depth than ones on DevShed and sites like it?

Thanks again to all,

Mike