How do I check for 2 or more parameters in an SQL query?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Sindarin
Forum Regular
Posts: 521
Joined: Tue Sep 25, 2007 8:36 am
Location: Greece

How do I check for 2 or more parameters in an SQL query?

Post 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,
User avatar
veridicus
Forum Commoner
Posts: 86
Joined: Fri Feb 23, 2007 9:16 am

Re: How do I check for 2 or more parameters in an SQL query?

Post 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
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: How do I check for 2 or more parameters in an SQL query?

Post 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()
User avatar
Sindarin
Forum Regular
Posts: 521
Joined: Tue Sep 25, 2007 8:36 am
Location: Greece

Re: How do I check for 2 or more parameters in an SQL query?

Post 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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: How do I check for 2 or more parameters in an SQL query?

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Sindarin
Forum Regular
Posts: 521
Joined: Tue Sep 25, 2007 8:36 am
Location: Greece

Re: How do I check for 2 or more parameters in an SQL query?

Post 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)
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: How do I check for 2 or more parameters in an SQL query?

Post 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 ;)
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Sindarin
Forum Regular
Posts: 521
Joined: Tue Sep 25, 2007 8:36 am
Location: Greece

Re: How do I check for 2 or more parameters in an SQL query?

Post 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. :)
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: How do I check for 2 or more parameters in an SQL query?

Post 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 ;)
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Sindarin
Forum Regular
Posts: 521
Joined: Tue Sep 25, 2007 8:36 am
Location: Greece

Re: How do I check for 2 or more parameters in an SQL query?

Post by Sindarin »

Strangely that doesn't work... :(
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: How do I check for 2 or more parameters in an SQL query?

Post by VladSun »

Sindarin wrote:Strangely that doesn't work... :(
Your query, please?
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Sindarin
Forum Regular
Posts: 521
Joined: Tue Sep 25, 2007 8:36 am
Location: Greece

Re: How do I check for 2 or more parameters in an SQL query?

Post 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";
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: How do I check for 2 or more parameters in an SQL query?

Post by VladSun »

:twisted:
AND/OR etc operators in SQL have the same precedence like in PHP ;)
OR has lower precedence than AND ... Use brackets ;)
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: How do I check for 2 or more parameters in an SQL query?

Post 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 ...
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply