开发者

How to add multiple OR in MySQL query

开发者 https://www.devze.com 2023-01-04 14:43 出处:网络
Example DB structure (download table) id | pid| title ----------------------------------------------------

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++; }

$开发者_如何学Pythonselect = '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.


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


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


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


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

精彩评论

暂无评论...
验证码 换一张
取 消