what is the right syntax for an IF in a mySQL query?

marcnyc

What would the correct syntax be to add an if statement inside a mysql query?

sort of

SELECT reviews2_reviews.id as id, reviews2_countries.country as country
FROM reviews2_reviews, reviews2_countries
WHERE reviews2_reviews.id_category = reviews2_countries.id
AND IF reviews2_reviews.id_category = reviews2_category.id

Thanks for any help

Weedpacket

Originally posted by marcnyc
What would the correct syntax be to add an if statement inside a mysql query?

sort of

SELECT reviews2_reviews.id as id, reviews2_countries.country as country
FROM reviews2_reviews, reviews2_countries
WHERE reviews2_reviews.id_category = reviews2_countries.id
AND IF reviews2_reviews.id_category = reviews2_category.id

Thanks for any help

What do you mean "IF"? If you mean you want the reviews2_reviews.id_category to be the same as the reviews2_countries.id AND the reviews2_reviews.id_category to be the same as the reviews2_category.id, then the query is

SELECT reviews2_reviews.id as id, reviews2_countries.country as country
FROM reviews2_reviews, reviews2_countries
WHERE reviews2_reviews.id_category = reviews2_countries.id
AND reviews2_reviews.id_category = reviews2_category.id

Kudose

There is no "AND IF"

Either it IS or it isnt (OR)

SELECT * FROM table WHERE stuff='$stuff' AND morestuff='$morestuff'

alternative route

SELECT * FROM tablename WHERE stuff='$stuff' OR morestuff='$morestuff'

bubblenut

Not quite true kudose. Granted it may not be called an IF but the CASE WHEN THEN ELSE syntax (See here) has much the same effect. Seems to be pretty quick as well, I used it in a fuzzy search app a while ago, although brining program logic into your SQL is questionable.
HTH
Bubble

marcnyc

I will try to be more specific:

what I want to "say/ask" to the database is this:

SELECT columnnames
FROM tablenames
WHERE certain column names match other colum names
AND IF (only IF) certain other colum names match certain other colum names && a few other conditions are true...

In other words if the IF statement would exist for sql my complete query would be this one:

$sql = ' SELECT reviews2_reviews.id as id, artist, work_title, reviews2_countries.country as country, reviews2_category.category as category
FROM reviews2_reviews, reviews2_countries, reviews2_category
WHERE reviews2_reviews.id_category = reviews2_countries.id
AND (IF reviews2_reviews.id_category = reviews2_category.id && category NOT LIKE "music%" && category NOT LIKE "book%" && category NOT LIKE "magazine_%")

In other words I only want to get values whose value in the id_category column (from the reviews2_reviews table) matches the value in the id (from the reviews2_countries table) AND IF (BUT ONLY IF) the value of the same id_category column (from the reviews2_reviews table) matches the value in the id column (from the reviews2_category table) AND the value in the category column does not start with either of the three strings (book, magazine and music_)

Hope this explains and doesn't confuse things...

marcnyc

another way for me to explain my case is this (maybe it'll make more sense):

condition ONE = reviews2_reviews.id_category = reviews2_countries.id

condition TWO = reviews2_reviews.id_category = reviews2_category.id

condition THREE = category NOT LIKE "music%" && category NOT LIKE "book%" && category NOT LIKE "magazine_%"

basically what I want from my query is that:
condition ONE be true
and
condition TWO as well as condition THREE be true

if condition ONE is true and condition TWO is true also I don't want the results, but IF condition TWO together WITH (not alone) condition THREE are true then I want the results

if condition ONE = true & (condition TWO = true & condition THREE = true) - YES

Hope somebody understands what I mean...

Weedpacket

Ah, so you don't mean AND at all, you mean OR!

(this OR (NOT this AND that AND the other)

But see also bubblenut's post.

marcnyc

No I mean AND, not OR!
There are 3 conditions, say A, B and C.
What I want to do would be written as following in PHP:

if ( $B & $C ) {
if ( $A ) { do it! }
}

Basically it's an AND but I wanna DO IT only if A is true and B and C together are true...

Marc

yousaf931

This will be the syntax of your query:


SELECT reviews2_reviews.id as id, reviews2_countries.country as country 
FROM reviews2_reviews, reviews2_countries 
WHERE reviews2_reviews.id_category = reviews2_countries.id 
AND (reviews2_reviews.id_category = reviews2_categor1.id or reviews2_reviews.id_category = reviews2_categor2.id or reviews2_reviews.id_category = reviews2_category3.id)

Cheers,
yousaf FAYYAZ.

Weedpacket

Originally posted by marcnyc
No I mean AND, not OR!
There are 3 conditions, say A, B and C.
What I want to do would be written as following in PHP:

if ( $B & $C ) {
if ( $A ) { do it! }
}

Basically it's an AND but I wanna DO IT only if A is true and B and C together are true...

Marc

So you want to do if if A is true and B and C are true. So you want to do it if A and B and C are true. So then it goes back to my first post.

SELECT blah WHERE $B AND $C AND $A, and what is the IF supposed to achieve?

Clarify. There are eight possibilities for A, B, C to be true or false. For which combinations precisely do you want the results?

marcnyc

You're right, I am sorry, what I said makes no sense...
What I meant to say before I confused myself and a bunch of other people is that... This is so hard to make sense of it I don't even know how to explain it anymore...

reviews2_reviews.id_category = reviews2_countries.id
will always be a match
reviews2_reviews.id_category = reviews2_category.id
will also always be a match

what I need is this:

if reviews2_reviews.id_category = reviews2_countries.id then
then I want the data
UNLESS
( reviews2_reviews.id_category = reviews2_category.id && category LIKE "music%" category LIKE "book%" category LIKE "magazine_%" )

Even better, please take a look at the attached file and think of it this way:

Mary is assigned reviews2_reviews.id=1 and is assigned to reviews2_reviews.id_category=2 which means she will be filed under the category with reviews2_category.id=2, which is reviews2_category.category="music_else".

In much the same way Robert is assigned reviews2_reviews.id=2 and is assigned to reviews2_reviews.id_category=6 which means he will be filed under the category with reviews2_category.id=6, which is reviews2_category.category="magazine_2".

and so on...

in my PHP code I have a variable $c carrying a numerical value that sometimes represents the reviews2_category.id and other times represents the value of reviews2_countries.id

I want to pull a list from the database that will have ALL the names whose $c value is a value of reviews2_countries.id

To do that in the SQL query I have to use WHERE to match reviews2_reviews.id_category to reviews2_countries.id
hence:
reviews2_reviews.id_category = reviews2_countries.id

Unfortunately I cannot stop here because that will also show me all the names of the people whose $c value is not the value of a country but is the value of a category.

To eliminate those people from the list I have to first match reviews2_reviews.id_category to reviews2_category.id
hence:
reviews2_reviews.id_category = reviews2_category.id
and after I have done that IF (and here is why I thought I need an IF) the "category" value associated in reviews2_category.id contains the text strings "music" or "book" or "magazine_" then I know that I can trash that value...

I hope that makes more sense... The more I talk about it the less sense it makes to me...

marcnyc

I just had two ideas after posting my last message... I went for one and it worked...
I have given up on this... I mean, I have taken a different approach, made my life easier by using one additional variable, so I worked out my problem from a different angle because this was getting too confusing and crazy for me and maybe it couldn't have been done at all because the matching id numbers would have made it impossible...
anyway, I didn't wanna delete the post because somebody else might find some interesting ideas in it (especially that link to the mysql syntax page)