PHP: How to pass multiple values to SELECT query

Question

I am new to PHP and hope someone can help me with this.

I currently use the below lines to retrieve a value from a db and to output it as an array with the item's ID and value which works as intended.

Now I would need to do the same for multiple items so my input ($tID) would be an array containing several IDs instead of just a single ID and I would need the query to do an OR search for each of these IDs.

I was thinking of using a foreach loop for this to append " OR " to each of the IDs but am not sure if this is the right way to go - I know the below is not working, just wanted to show my thoughts here.

Can someone help me with this and tell me how to best approach this ?

My current PHP:

$content = "";
$languageFrm = $_POST["languageFrm"];
$tID = $_POST["tID"];

$stmt = $conn->prepare("SELECT tID, " . $languageFrm . " FROM TranslationsMain WHERE tID = ? ORDER BY sortOrder, " . $languageFrm);
$stmt->bind_param("s", $tID);
$stmt->execute();
$result = $stmt->get_result();
while($arr = $result->fetch_assoc()){
    $content[] = array("ID" => $arr["tID"], "translation" => $arr[$languageFrm]);
}

My thought:

foreach($tID as $ID){
    $ID . " OR ";
}

Many thanks for any help, Mike

You can also use WHERE tID IN(1, 2, 3, 4) clause to select any of these IDs — ptkoz, Jul 17 '15 at 06:56
Instead of multiple `OR` conditions, you could use an `IN()` - https://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_in ie. `WHERE tID IN (1,2,3,4)`. It would also work if you only had 1 `WHERE tID IN (1)` — Sean, Jul 17 '15 at 06:56
@Sean: Thanks as well ! - If someone wants to post this as an answer I will accept. :) Two more questions on this: 1) Would this also work in case there is just one ID ? 2) If I know the input is only numerical would I use something other than "s" in bind_param("s"... ? — keewee279, Jul 17 '15 at 07:00

ptkoz · Accepted Answer · 2015-07-17T07:14:49.083

2

There are two approaches, assuming $tID is an array of IDs

Using MySQL IN() clause

This will work also when $tID is not an array, but a single scalar value.

$tID = array_map('intval', (array)$tID); // prevent SQLInjection
if(!empty($tID)) {
    $query .= ' WHERE tID IN(' . implode(',', $tId) . ')';
} else {
    $query .= ' WHERE 0 = 1';
}

Using OR clause, as you suggested

A bit more complicated scenario.

$conds = array();
foreach($tID as $ID) {
    $conds[] = 'tID = ' . intval($ID);
}

if(!empty($conds)) {
    $query .= ' WHERE (' . implode(' OR ', $conds) . ')';
} else {
    $query .= ' WHERE 0 = 1';
}

edited Jul 17 '15 at 07:14

answered Jul 17 '15 at 07:10

ptkoz

2,388
1
20
28

Thanks a lot - this is great ! I think $4ID is just a typo, right ? :) Also, if I use this approach I wouldn't need bing_param at all, correct ? – keewee279 Jul 17 '15 at 07:13
1

Actually you can't achieve this behavior with bind_param. You would have to know how many IDs are in array and place that number of qestion marks in the query. See more here: http://stackoverflow.com/questions/178479/preparedstatement-in-clause-alternatives – ptkoz Jul 17 '15 at 07:17
1

There are several DB abstract layers out there which can do this. For example in Zend_Db: $db->query('SELECT x FROM t WHERE id IN(?)', $array); AFAIR also PDO has this feature. – ptkoz Jul 17 '15 at 07:21

score 0 · Answer 2 · answered Jul 17 '15 at 07:35

0

As per above conditions you can try with implode();

implode($tID,' OR ');

You can also use IN condition instead of OR something like this.

implode($tID,' , ');

answered Jul 17 '15 at 07:35

Ram Sharma

8,676
7
43
56

PHP: How to pass multiple values to SELECT query

2 Answers2