CODEWITHSUNDEEP
phpmysql
kampit
kampit

Reputation: 149

How to add multiple OR in MySQL query

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.

Upvotes: 1

Views: 315

Answers (4)

jbudge
jbudge

Reputation: 36

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)){}
?>

Upvotes: 2

Lombo
Lombo

Reputation: 12235

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)) {
?>

Upvotes: 2

Felix Kling
Felix Kling

Reputation: 817208

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)) {

Upvotes: 0

Amarghosh
Amarghosh

Reputation: 59471

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)(,|$)'

Upvotes: 1

Related Questions