SQL one select with different where clauses by PHP variable

Question

Unlike How do I perform an IF…THEN in an SQL SELECT? , I'm asking about a PHP variable.

I'm trying to create a SQL SELECT statement on my PHP file which can select all person or single person.

if ( $watch_person == "all") {
  $sql = "SELECT WORK_RECORD
            FROM worklist
         ";
} else {
  $sql = "SELECT WORK_RECORD
            FROM worklist
           WHERE PERSON_NO = '".$watch_person."'
         ";
}

Is there a way to do this in one SQL statement?

You are essentially trying to output the same set of data twice. What you can do is grab **all** of the data in a single query, and then filter it in PHP based on the two different sets of **results** that you desire, avoiding the need for a second query. — Obsidian Age, May 25 '17 at 02:31
Possible duplicate of [How do I perform an IF...THEN in an SQL SELECT?](https://stackoverflow.com/questions/63447/how-do-i-perform-an-if-then-in-an-sql-select) — Arsen Davtyan, May 25 '17 at 03:01

score 0 · Answer 1 · answered May 25 '17 at 02:33

You can optimize your coding as below.

$sql = "SELECT WORK_RECORD
             FROM worklist WHERE ";
if ( $watch_person == "all") {
   $sql .= " 1=1";
 } else {
   $sql .= "  PERSON_NO = '".$watch_person."' ";
 }

OR

$sql = "SELECT WORK_RECORD
             FROM worklist WHERE ". ($watch_person == "all" ? "1=1" : "PERSON_NO = '".$watch_person."'");

score 0 · Answer 2 · answered May 25 '17 at 02:37

0

you can have the where statement in the if else.

$sql = "SELECT WORK_RECORD
        FROM worklist ". 
        ($watch_person == "all" ? "" : "WHERE PERSON_NO = '".$watch_person."'");

answered May 25 '17 at 02:37

Chinito

1,085
1
9
11

Steven Chou · Answer 3 · 2017-05-25T03:10:59.390

0

It will be more easy to extend where condition.

.

$sql = "SELECT WORK_RECORD
             FROM worklist WHERE 1=1 ";

if ( $watch_person != "all") 
   $sql .= "  and PERSON_NO = '".$watch_person."' ";

if ( $watch_person2 != "another") 
   $sql .= "  and example = '".$watch_person2."' ";

edited May 25 '17 at 03:10

answered May 25 '17 at 02:37

Steven Chou

1,504
2
21
43

score 0 · Accepted Answer · answered May 25 '17 at 02:38

0

Here's how I would do it:

$sql = "SELECT WORK_RECORD FROM worklist";
if($watch_person != "all"){
    $sql .= " WHERE PERSON_NO = '$watch_person'";
}

answered May 25 '17 at 02:38

user2914191

877
1
8
21

SQL one select with different where clauses by PHP variable

4 Answers4