How to add multiple OR in MySQL query

Question

Example DB structure (download table)

id | pid     | title
----------------------------------------------------
1  | 3,4,5   | Download 3, Download 4, Download 5
----------------------------------------------------
2  | 3       | Download 3
----------------------------------------------------

Here is my code

<?php
  $pid = explode(",",  $order['pid']);
  for($x = 0; $x < count($pid);){ 
  if(count($pid) == 1 ) {
  $thepid = 'pid="'.$pid[$x].'"';
  } else {
  $thepid = 'pid="'.$pid[$x].'" OR ';
  }
$x++; }

$select = 'SELECT * FROM download WHERE '.$thepid.'';
$query = $db->rq($select);
while($download = $db->fetch($query)) {
?>

Question -

1. How to make the $select can read pid="3" OR pid="4" OR pid="5" if count($pid) more than one.

I know the table structure is not normal. But how possible to get it works.

Answer 1

If you want to stick with this structure (which is not optimal if you want to make such queries), you can use LIKE:

SELECT * FROM download WHERE pid LIKE '%3%' OR pid LIKE '%4%' OR pid LIKE '%5%'

But you will get into trouble if you have numbers > 9.

I would really recommend to normalize your table!

---

Some comments on your code.

1. Your for loop looks strange. Why do you increase $x at the end instead instead of defining this in the "head" of the loop?
2. You are reassigning $thepid again and again in the for loop, so it will have the value of the last iteration.
3. You are using count() too often.

Here is an improved version, but note, this does only works, if your table is normalized:

<?php
  $pids = explode(",",  $order['pid']);
  $conds = array();
  foreach($pids as $pid) {  
      $conds[] = 'pid="'.$pid.'"';
  }

  $conds = implode(' OR ', $conds);

  $select = 'SELECT * FROM download WHERE '.$cond.'';
  $query = $db->rq($select);
  while($download = $db->fetch($query)) {

Answer 2

Use REGEXP

SELECT * FROM download WHERE pid REGEXP '\\b(3|4|5|12)\\b'

If \\b is not supported, you can try:

SELECT * FROM download WHERE pid REGEXP '(^|,)(3|4|5|12)(,|$)'

Answer 3

This is not a really good way to represent your data.

I assume that your columns represent something like

id -> user_id

pid -> a list of all items downloaded by the user (comma-separated)

title -> a list of all items' names downloaded by the user (comma-separated)

The thing with this approach is that you will find that queries like this one are much harder to express than with a normal approach. For example:

Table Users

user_id

Table Files

pid

title

Table Downloads

user_id

pid

both as foreign key to the corresponding table

Your query would become

<?php
  $pid = explode(",",  $order['pid']);
  for($x = 0; $x < count($pid);){ 
  if($x == 0 ) {
  $thepid = 'pid='.mysql_real_escape_string($pid[$x]);
  } else {
  $thepid = 'OR pid='.mysql_real_escape_string($pid[$x]);
  }
$x++; }

$select = 'SELECT user_id FROM download WHERE '.$thepid.'';
$query = $db->rq($select);
while($download = $db->fetch($query)) {
?>

Answer 4

Maybe I'm over-simplifying this a bit, but wouldn't this be easier?

<?php

$select = 'SELECT * FROM download WHERE pid IN ('.$order['pid'].')';
$query = $db->rq($select);
while($download = $db->fetch($query)){}
?>