Page 1 of 1
How do I check for 2 or more parameters in an SQL query?
Posted: Fri Nov 21, 2008 8:03 am
by Sindarin
I need to check if ALL of 3 values in the database are what I want them to be THEN throw out the results,
Code: Select all
$query = "SELECT * FROM articles WHERE article_showhome=1 article_expire_date>CURDATE() AND article_expire_time>CURTIME() AND article_expirehome='0' ORDER BY article_importance ASC LIMIT 10";
It seems SQL's AND doesn't work like that of php.
What I need to do in words is,
Select any article from table 'articles' where ALL of the values are as follows,
the article_showhome value must be 1,
IF article_expirehome is equal to 1 THEN check if article_expire_date>CURDATE()
AND article_expire_time>CURTIME()
and
I do NOT want to use DATETIME. I keep them separately.
Thanks,
Re: How do I check for 2 or more parameters in an SQL query?
Posted: Fri Nov 21, 2008 11:04 am
by veridicus
Sounds like you want something like this:
Code: Select all
SELECT * FROM articles WHERE article_showhome=1 AND (article_expirehome='0' OR (article_expirehome='1' AND article_expire_date>CURDATE() AND article_expire_time>CURTIME())) ORDER BY article_importance ASC LIMIT 10
Re: How do I check for 2 or more parameters in an SQL query?
Posted: Fri Nov 21, 2008 11:17 am
by Eran
You are missing one AND operator:
Code: Select all
article_showhome=1 article_expire_date>CURDATE()
Should be:
Code: Select all
article_showhome=1 AND article_expire_date>CURDATE()
Re: How do I check for 2 or more parameters in an SQL query?
Posted: Mon Nov 24, 2008 3:55 am
by Sindarin
You are missing one AND operator:
1. article_showhome=1 article_expire_date>CURDATE()
yeah, experimenting with it, I forgot to copy paste the right one.
Sounds like you want something like this:
1. SELECT * FROM articles WHERE article_showhome=1 AND (article_expirehome='0' OR (article_expirehome='1' AND article_expire_date>CURDATE() AND article_expire_time>CURTIME())) ORDER BY article_importance ASC LIMIT 10
Yes this does the job so far, but articles who have article_expirehome as value 0, try to get compared with CURTIME()/CURDATE() as well and don't show up as their values are probably '0000-00-00' and '00:00:00'. Is there anyway to avoid this comparison so those entries can be displayed as well?
I am trying something like this but it doesn't work,
Code: Select all
$query = "SELECT * FROM articles WHERE article_showhome=1 AND ((article_expirehome='0' AND article_expire_date='0000-00-00' AND article_expire_time='00:00:00') OR (article_expirehome='1' AND article_expire_date>CURDATE() AND article_expire_time>CURTIME())) ORDER BY article_importance ASC LIMIT 10";
entries with article_expirehome='0' do not show at all because their date is 0000-00-00 which is lower than CURDATE.
Re: How do I check for 2 or more parameters in an SQL query?
Posted: Mon Nov 24, 2008 4:10 am
by VladSun
Could you please rewrite your posts with [ sql ] tags and remove the PHP related parts - it will be much easier for us to help you.
Please, explain in general what you are trying to do and describe your tables structure.
Re: How do I check for 2 or more parameters in an SQL query?
Posted: Mon Nov 24, 2008 4:42 am
by Sindarin
Alright, the query I am trying is this:
Code: Select all
SELECT * FROM articles WHERE article_showhome=1 AND ((article_expirehome='0' AND article_expire_date='0000-00-00' AND article_expire_time='00:00:00') OR (article_expirehome='1' AND article_expire_date>CURDATE() AND article_expire_time>CURTIME())) ORDER BY article_importance ASC LIMIT 10
Table Rows
article_showhome //can be 1 or 0, determines if an article will show on homepage or not
article_expirehome //can be 1 or 0, determines if an article expires or not
article_expire_date //expiration date, if article_expirehome is 0 then this value will be 0000-00-00 (never expires)
article_expire_time //expiration time, if article_expirehome is 0 then this value will be 00:00:00 (never expires)
There is a page that lists last 10 entries that will appear on the homepage.
Now the entries that will appear on that page,
must have article_showhome=1 otherwise they are invisible to the user but still exist in the database,
can have an expiration date which is defined by article_expirehome.
IF they don't have an expiration date then article_expirehome=0 and article_expire_date/article_expire_time are simply ignored as they are values of zeros.
IF they do have an expiration date then article_expirehome=1 and article_expire_date/article_expire_time are compared with CURDATE and CURTIME to check if they have expired or not. If they have then they must not show up.
So in the page the entries that can be shown are:
1. entries that are visible and with no expiration date (article_showhome=1,article_expirehome=0)
2. entries that are visible and with expiration date (and not expired!) (article_showhome=1,article_expirehome=1,article_expire_date>CURDATE,article_expire_time>CURTIME)
Re: How do I check for 2 or more parameters in an SQL query?
Posted: Mon Nov 24, 2008 4:52 am
by VladSun
[sql]SELECT * FROM articles WHERE article_showhome=1 AND ( article_expirehome=0 OR ( article_expire_date>CURDATE() AND article_expire_time>CURTIME() ) ) ORDER BY article_importance ASC LIMIT 10[/sql]
Is that what you want.
Also, I suppose that these 0000-00-00, 00:00:00 are indeed NULL values
PS: Don't you like my formatting more than yours

Re: How do I check for 2 or more parameters in an SQL query?
Posted: Mon Nov 24, 2008 5:13 am
by Sindarin
Code: Select all
SELECT
*
FROM
articles
WHERE
article_showhome=1
AND
(
article_expirehome=0
OR
(
article_expire_date>CURDATE() OR article_expire_date=CURDATE()
AND
article_expire_time>CURTIME() OR article_expire_time=CURTIME()
)
)
ORDER BY
article_importance ASC
LIMIT
10";
It seems it needs to check for equal values in date/time as well so I added 2 extra OR. It works well so far. I hope it's bug free, I am still testing it.
PS: Don't you like my formatting more than yours
I do the same, but the damn editor keeps scrambling it.

Re: How do I check for 2 or more parameters in an SQL query?
Posted: Mon Nov 24, 2008 5:15 am
by VladSun
Sindarin wrote:It seems it needs to check for equal values in date/time as well so I added 2 extra OR.
Use >= operator

Re: How do I check for 2 or more parameters in an SQL query?
Posted: Mon Nov 24, 2008 5:25 am
by Sindarin
Strangely that doesn't work...

Re: How do I check for 2 or more parameters in an SQL query?
Posted: Mon Nov 24, 2008 6:17 am
by VladSun
Sindarin wrote:Strangely that doesn't work...

Your query, please?
Re: How do I check for 2 or more parameters in an SQL query?
Posted: Mon Nov 24, 2008 6:44 am
by Sindarin
Tried that and doesn't work, but the one with OR does,
Code: Select all
SELECT
*
FROM
articles
WHERE
article_showhome=1
AND
(
article_expirehome=0
OR
(
article_expire_date>=CURDATE()
AND
article_expire_time>=CURTIME()
)
)
ORDER BY
article_importance ASC
LIMIT
10";
Re: How do I check for 2 or more parameters in an SQL query?
Posted: Mon Nov 24, 2008 7:39 am
by VladSun
AND/OR etc operators in SQL have the same precedence like in PHP

OR has lower precedence than AND ... Use brackets

Re: How do I check for 2 or more parameters in an SQL query?
Posted: Tue Nov 25, 2008 5:41 pm
by VladSun
I think you didn't understand me... Your query is wrong because:
[sql] article_expire_date>CURDATE() OR article_expire_date=CURDATE() AND article_expire_time>CURTIME() OR article_expire_time=CURTIME()[/sql]
===
[sql] article_expire_date>CURDATE() OR ( article_expire_date=CURDATE() AND article_expire_time>CURTIME() ) OR article_expire_time=CURTIME()[/sql]
So, if article_expire_time=CURTIME(), it will always return true regardless of article_expire_date ...