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 -
- How to make the
$select
can readpid="3" OR pid="4" OR pid="5"
ifcount($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.
- Your
for
loop looks strange. Why do you increase$x
at the end instead instead of defining this in the "head" of the loop? - You are reassigning
$thepid
again and again in thefor
loop, so it will have the value of the last iteration. - 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)) {
精彩评论