Constructing mysql select from $_POST array

Question

This is the $_POST array from my form.

Array ( [prescribedid] => Array ( [0] => 1 [1] => 2 [2] => 3 [3] => 9 [4] => 13 )

I want to create a select for any of items in the Array. I have written this, which produces the proper SELECT, but the if() to eliminate a trailing OR makes it clunky.

$query = "SELECT * ";
$query .= "FROM prescribed WHERE ";

for($i=0; $i<count($_POST["prescribedid"]); $i++) {
    $query .= "prescribedid={$_POST['prescribedid'][$i]} ";
        if($i < (count($_POST["prescribedid"])-1)) {
        $query .= "OR ";
        }
}

It produces this:

SELECT * FROM prescribed WHERE prescribedid=1 OR prescribedid=2 OR prescribedid=3 OR prescribedid=9 OR prescribedid=13

Is there another way to group the SELECTS or write the FOR() to make it cleaner, i.e. without the last IF().

`Tip:` You can use `IN` in order to avoid multiple `OR` clauses. `...WHERE prescribedid IN (1,2,3,9,13)....` — 1000111, Aug 17 '16 at 07:16
`Tip:` You need to use PDO or vaidate input. This code can be used for SQL injection. — ineersa, Aug 17 '16 at 07:18

score 8 · Accepted Answer · edited May 23 '17 at 12:19

8

$values=implode(",",$_POST["prescribedid"]);
$query = "SELECT * FROM prescribed WHERE  prescribedid IN ($values)";

Sanitization is on you :)

edited May 23 '17 at 12:19

Community

1
1

answered Aug 17 '16 at 07:17

Hanky Panky

46,730
8
72
95

D Coder · Answer 2 · 2016-08-17T12:55:27.700

4

Use MySQL IN clause

$ids = implode(",",$_POST["prescribedid"]);
$query = "SELECT * FROM prescribed WHERE  prescribedid IN ($ids)";

edited Aug 17 '16 at 12:55

answered Aug 17 '16 at 07:20

D Coder

572
4
16

score 4 · Answer 3 · answered Aug 17 '16 at 07:22

4

Hi You can Use In condition. use imploade function to find comma seoarated values

$data = array('prescribedid'=>array(1,2,3,9,14));
$query = 'SELECT * FROM prescribed WHERE  prescribedid IN (';
$query .= implode(',',$data['prescribedid']).')';
echo $query ;

Output

SELECT * FROM prescribed WHERE prescribedid IN (1,2,3,9,14)

answered Aug 17 '16 at 07:22

Passionate Coder

7,154
2
19
44

You don't need the string concatenation (and it *may* cause some confusion, especially in this layout.) – ʰᵈˑ Aug 17 '16 at 12:58

Indrasis Datta · Answer 4 · 2016-08-17T07:20:14.367

2

You can simply use IN clause here.

Refer to MySQL IN clause

$query = "SELECT * FROM prescribed WHERE  prescribedid IN ".implode(',', $_POST["prescribedid"]);

edited Aug 17 '16 at 07:20

answered Aug 17 '16 at 07:19

Indrasis Datta

8,692
2
14
32

Constructing mysql select from $_POST array

4 Answers4