CODEWITHSUNDEEP
phpmysqlif-statementpdo
purpletree
purpletree

Reputation: 1943

MySQL Query - WHERE and IF?

SOLVED: Datatypes in the table for country and OS were set to VARCHAR so in my where, I changed = 0 to = '0'. Thanks to raina77ow for pointing this out! Appreciate it.

I'm not quite sure how to write this query. Basically, I'm going to have a table with two columns (OS and country_code) - more columns too, but those are the conditional ones. These will be either set to 0 for all, or specific ones, separated by commas.

Now, what I'm trying achieve is pull data from the table if the OS and country_code = 0, or if they contain matching data (separated by commas).

Then, I have a column for time. I want to select rows where the time is GREATER than the time column, unless the column time_t is set to false, in which case this shouldn't matter.

I hope I explained it right?

This is what I kind of have so far:

$get = $db->prepare("SELECT * FROM commands 
    WHERE country_code = 0 OR country_code LIKE :country_code 
    AND OS = 0 OR OS LIKE :OS 
    AND IF (time_t = 1, expiry > NOW())
");
$get->execute(array(
    ':country_code' => "%{$data['country_code']}%",
    ':OS' =>  "%{$data['OS']}%"
));

EDIT: This is the code I'm using now, but I still can't seem to get the OS and Country_code part working:

$get = $db->prepare("SELECT * FROM commands 
WHERE (country_code = 0 OR country_code LIKE :country_code) 
AND (OS = 0 OR OS LIKE :OS) 
AND (time_t <> 1 OR expiry > NOW())
");

$get->execute(array(
':country_code' => "%{$data['country_code']}%",
':OS' =>  "%{$data['OS']}%"
));

Data sent to the DB would be: OS => Windows 7 country_code => GB ONLY one would be being sent, not more than one. The issue I'm having right now is, it's working for OS and for the time thing, but it's not working for the country. I even tried a raw query, when the data in the table is "FR, SA" and I did LIKE '%GB%' it still returned the row

Upvotes: 1

Views: 174

Answers (1)

raina77ow
raina77ow

Reputation: 106385

It's a question of logical operators' precedence: AND is higher than OR in the ladder, so your original query will be executed as...

WHERE country_code = 0 OR (country_code LIKE ... AND OS = 0) OR OS LIKE...

Use parenthesis to separate your conditions, like this:

WHERE (country_code = 0 OR country_code LIKE ...) 
  AND ( OS = 0 OR OS LIKE ...)

And the final filter condition in this query, I suppose, should be written as...

AND ( time_t <> 1 OR expiry > NOW() )

UPDATE: ... and it's actually should be country_code = '0' or something like this in your code. You're checking the VARCHAR column, remember.

Upvotes: 1

Related Questions