CODEWITHSUNDEEP
phpmysql
Gopal Satpati
Gopal Satpati

Reputation: 136

Using PHP Mysql how can I get which condition satisfied from multiple OR condition in a sql query?

I have below query:

$sql = "SELECT * 
        FROM `tbl_user` 
        WHERE `Code1`='114' 
        OR `Code2`='114' 
        OR `Code3`='114' 
        OR `Code4`='114' 
        OR `Code5`='114'";

I can find the column which satisfy the condition Code1 or Code2 or Code3 ... by array_search of the return result.

But can I get any other easy solution?

Upvotes: 1

Views: 193

Answers (4)

Muhammad Saqlain
Muhammad Saqlain

Reputation: 2212

You can use the IN predicate, as below

SELECT * FROM tbl_user WHERE 114 IN(Code1, Code2, Code3, Code4, Code5);

Documentation

Upvotes: 0

Nick
Nick

Reputation: 147176

You can achieve the result you want using the FIELD function:

SELECT *,
       FIELD(114, code1, code2, code3, code4, code5) AS code
FROM tbl_user
WHERE FIELD(114, code1, code2, code3, code4, code5) > 0

Output (for my demo):

code1   code2   code3   code4   code5   code
45      32      114     5       12      3
114     34      98      93      12      1
57      114     48      23      72      2
12      75      8       3       114     5
49      4       198     114     52      4

Note that if you want a string column name instead of just a number, you can use 

CONCAT('Code', FIELD(114, code1, code2, code3, code4, code5)) AS code

Upvotes: 4

Sayed Mohd Ali
Sayed Mohd Ali

Reputation: 2254

if you need to select column names then

 SELECT * FROM tbl_user.COLUMNS WHERE 114 IN(Code1, Code2, Code3, Code4, Code5);

The best way is to use the INFORMATION_SCHEMA metadata virtual database. Specifically the INFORMATION_SCHEMA.COLUMNS table...

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='yourdatabasename' AND TABLE_NAME='yourtablename'; It's VERY powerful, and can give you TONS of information without need to parse text (Such as column type, whether the column is nullable, max column size, character set, etc)...

Upvotes: 0

Shobi
Shobi

Reputation: 11461

maybe this is what you are looking for? 

SELECT col FROM (
   SELECT "Code1" AS col, Code1 AS value FROM tbl_user
   UNION ALL SELECT "Code2", Code2 FROM tbl_user
   UNION ALL SELECT "Code3", Code3 FROM tbl_user
) allValues
WHERE value=114;

Ref: https://stackoverflow.com/a/2940786/2693543

Upvotes: 0

Related Questions