PHP field comparision with related fields in database

Question

I have 3 fields in database:

levelOne , levelTwo, levelThree // Table Name = levels

With values:

levelOne    = 300,
levelTwo    = , // This field is empty
levelThree  = , // This field is empty

Now I have a variable which have to be compared with the respective fields.

$var = 200;

This $var should only be compared with levelOne, not levelTwo and levelThree. And so on for all

What should the query look like?

Possible cases of fields in database:

Case 1:

levelOne    = 300,
levelTwo    = , // This field is empty
levelThree  = , // This field is empty

Case 2:

levelOne    = 500,
levelTwo    = 700,
levelThree  = , // This field is empty

Case 3:

levelOne    = 500,
levelTwo    = 700,
levelThree  = 100,

Now $var should be compared with the last most non-empty level

Example:

In case 1 $var should be compared with levelOne.

In case 2 $var should be compared with levelTwo.

In case 3 $var should be compared with levelThree.

Basically I have 3 levels of categories.

User will post a listing with any level of categories.

Now a buyer will post presets of any category he wants to bid on.

Now if he provided levelOne category in his preset, it will match with all the listings having levelOne category.

Now if he provided levelTwo category in his preset, it will match with all the listings having levelTwo category.

Now if he provided levelThree category in his preset, it will match with all the listings having levelThree category.

So thats how these bids will automatically post on the related listings.

Thats my goal.

And if comparison is done i.e. var = 200 with any of the last non empty field what result you are expecting ? — Abhik Chakraborty, Jan 08 '14 at 09:49
@AbhikChakraborty Its a filter system of categories, there are three level of categories, each provided level should be compared with its own category level in database. If it did got matches, i will fetch that record — Hassan Sardar, Jan 08 '14 at 09:51
ok but by comparing you may need to check =, > , < etc which one you are trying to achieve ? — Abhik Chakraborty, Jan 08 '14 at 09:53
Proper [sample code](http://sscce.org/) (here, SQL statements) is more useful than any ad hoc schema and sample data format. Please use `CREATE TABLE` and `INSERT ... VALUES` for [samples](http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx). Desired results don't need to be presented as sample code, as results are the output of code and not code themselves. — outis, Jan 08 '14 at 10:07
I have the sneaking suspicion the table should be [normalized](https://en.wikipedia.org/wiki/Database_normalization). — outis, Jan 08 '14 at 10:18
@outis There is nothing to normalize here. Its a simple and straight forward table. with each level of categories — Hassan Sardar, Jan 08 '14 at 10:19
@CodeHunter: to the contrary, the 3 cases for categories appears to fit the "1, 2, ... or N" pattern. In other words, a repeating group, which violates (some formulations of) 1NF. However, I can't say for certain without knowing more about the data model (especially the role of categories). It might do some good to see if there's a relevant question on [dba.SE](http://dba.stackexchange.com/) or here on SO. — outis, Jan 10 '14 at 00:16

Uriziel · Answer 1 · 2014-01-08T10:11:27.377

0

Coalesce selects first non-null value from the given list.

SELECT * FROM levels l 
WHERE :var = COALESCE(l.lelvelThree, l.levelTwo, l.levelOne);

This query will compare levels' fields in given order.

edited Jan 08 '14 at 10:11

answered Jan 08 '14 at 09:49

Uriziel

732
5
17

1

Please don't simply post the code. Give some explanation or information or usage about your code. For example, see [this answer](http://stackoverflow.com/a/16893057/756941). – Nazik Jan 08 '14 at 10:08
1

Here you go. Maybe I found that too vague to explain, sorry. – Uriziel Jan 08 '14 at 10:12

PHP field comparision with related fields in database

1 Answers1