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
– ptkoz, Commented Jul 17, 2015 at 6:56
Instead of multiple OR conditions, you could use an IN() - dev.mysql.com/doc/refman/5.0/en/… ie. WHERE tID IN (1,2,3,4). It would also work if you only had 1 WHERE tID IN (1) — Sean
– Sean, Commented Jul 17, 2015 at 6: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
– keewee279, Commented Jul 17, 2015 at 7:00

ptkoz · Accepted Answer · 2015-07-17 07:14:49Z

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, 2015 at 7:14

answered Jul 17, 2015 at 7:10

ptkoz

2,5372 gold badges21 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

keewee279 Over a year ago

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 ?

ptkoz Over a year ago

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: stackoverflow.com/questions/178479/…

ptkoz Over a year ago

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.

Ram Sharma · Accepted Answer · 2015-07-17 07:35:40Z

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, 2015 at 7:35

Ram Sharma

8,8157 gold badges48 silver badges59 bronze badges

Collectives™ on Stack Overflow

PHP: How to pass multiple values to SELECT query

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related