Hello everyone.

Im building a banner exchange script from scratch. Im currently working on the script that would load a random banner.

The query to do this looks something like this:

SELECT * FROM `banners`
WHERE `credits` != '0'
AND `size` = '468x60'
AND `freeze` = '0'
AND `approved` = '1'
AND `category` = 'A'
OR `category` = 'B'
ORDER BY RAND() LIMIT 1;

I have filled my banners table with lots of banners with 0 credits, but in both categories A & B.

So, when i execute this script, it should return no banners at all (because, none of the banners have any credits).

But this is not the case, the query managed to pull random banners (when it's not supposed to).

So, why isnt the WHERE `credits` != '0' clause not working?

So, can anybody enlight me on this issue please? I haven't got a clue why it keeps messing up.

you need to use parenthesis () to group those conditions.

SELECT * FROM `banners`
WHERE `credits` != '0'
AND `size` = '468x60'
AND `freeze` = '0'
AND `approved` = '1'
AND (`category` = 'A' OR `category` = 'B')
ORDER BY RAND() LIMIT 1;

thanks for your suggestion.

I added the parenthesis ()to group the condition, but, it didnt work :(

Without posting your schema, its hard to know exactly but we can probably guess that the credits column is an integer or more likely a float or currency value.

If so, why are you testing for a string? In other words: WHERE credits != 0 (not credits != '0' as you had done).

If indeed the credits column is some type of numeric (not string) data, WHERE credits > 0 might be a safer approach for you.

What type of field is credits?

Peace,

Sorry about the confusion. I kinda messed up the format of my query with the parenthesis.

I fixed it now, it works fine ^^;

$category_mixed_array = split(";",$category_mixed);
$category_mixed_array_size = sizeof($category_mixed_array) - 1;
$category_query .= '(';
for($i = 0; $i < $category_mixed_array_size; $i++)
{
if($i != 0){ $or = " OR "; }
$category_query .= $or . "`category` = '" . mysql_real_escape_string($category_mixed_array[$i]) . "'";
}
$category_query .= ') ';

This was the code that made it worked.

Although this is not necessary for this query, in the future if you have a large number of categories it may become helpful to craft your query this way instead of multiple OR's:


SELECT * FROM `banners`
WHERE `credits` != '0'
AND `size` = '468x60'
AND `freeze` = '0'
AND `approved` = '1'
AND `category` IN ('A','B','C','D','E')
ORDER BY RAND() LIMIT 1;

Make it <> , It will work.

Thanks

Hello everyone.

Im building a banner exchange script from scratch. Im currently working on the script that would load a random banner.

The query to do this looks something like this:

SELECT * FROM `banners`
WHERE `credits` != '0'
AND `size` = '468x60'
AND `freeze` = '0'
AND `approved` = '1'
AND `category` = 'A'
OR `category` = 'B'
ORDER BY RAND() LIMIT 1;

I have filled my banners table with lots of banners with 0 credits, but in both categories A & B.

So, when i execute this script, it should return no banners at all (because, none of the banners have any credits).

But this is not the case, the query managed to pull random banners (when it's not supposed to).

So, why isnt the WHERE `credits` != '0' clause not working?

So, can anybody enlight me on this issue please? I haven't got a clue why it keeps messing up.