Where if else cancel

[ayok]

Hi,

I'm trying to use if in mysql query, but can't get it work.

So the case is i want to select rows when siteid is not zero, if it's zero then I need to cancel the where.

I've tried

SELECT * FROM items WHERE IF(siteid > 0,$siteid,0) = $siteid

With this query I got good result when $siteid is not 0, but it returns empty when $siteid = 0, which actually select siteid = 0. What I need is no more where, so it should just returns all.

 

Could anybody help me here?

 

Thanks

ayok

[awjudd]

You can either change your PHP code to just not include the WHERE clause if $siteid = 0 (better approach) or you could do as follows:

 

$query="SELECT *
FROM items
WHERE $siteid<>0 AND siteid=$siteid";

 

~awjudd

[Jessica]

That won't return all of them if $siteid is 0, which is what he said he needs.

You'll have to use PHP to only add the where clause if $siteid > 0

[awjudd]

I missed an OR ... but yet again, this is the wrong approach.

$query="SELECT *
FROM items
WHERE ($siteid<>0 AND siteid=$siteid) OR $siteid=0";

 

~awjudd

[Jessica]

That still won't work, as he said when $siteid is zero he gets no results.

[awjudd]

If $siteid is 0, then 0=0 which is TRUE, so it will return all of the rows.

 

~awjudd

[Jessica]

Ah, I see what you did there.

[ayok]

Thanks awjudd.. not exactly what I'm expected, but you've given me an idea to solve.