CODEWITHSUNDEEP
phpsqldatabase
Vitali Kloster
Vitali Kloster

Reputation: 59

Specific WHERE clause

I can't figure this one out.

$sql = "SELECT id,goodtill FROM table WHERE id = '$id' ORDER BY id LIMIT 1";

However, I want to make sure that when that $id is 100 its goodtill in the database is > '0'.

There are several $id's that are 100. Some of them have goodtill > '0' and some them have goodtill = '0'. I only want to show the ones that have goodtill > '0' And of course I want all other $id's that are not 100 to show.

I tried WHERE id = '$id OR ($id = '100' AND goodtill > '0') but I am pretty sure it's not how it's done, since it din't do what I wanted it to do.

Upvotes: 0

Views: 54

Answers (3)

Igor Reznikovsky
Igor Reznikovsky

Reputation: 1

Try this:

SELECT id,goodtill FROM table WHERE id = '$id' AND (id != 100 OR (id = '100' AND goodtill > '0')) ORDER BY id LIMIT 1

Upvotes: 0

kkica
kkica

Reputation: 4104

And case when $id=100 then goodtill>0 else 1=1. Instead of the or clause

Upvotes: 0

user229044
user229044

Reputation: 239541

You need to add conditions (in addition to id = $id) for where the ID is not 100, or the goodtill is greater than 0.

SELECT id,goodtill FROM table
  WHERE id = '$id'
  AND (id != 100 OR goodtill > 0)
  ORDER BY id LIMIT 1
  • Given an input $id of 9, this will match all records where id = 9. The where id != 100 is true, and effectively short-circuits the OR goodtill > 0 check.
  • Given an input $id of 100, this will match all records where id = 100 and goodtill > 0. The id != 100 is false, meaning the OR goodtill > 0 condition must pass.

Upvotes: 2

Related Questions