Re[2]: [PHP-DB] MySQL: Random select with specific count of a column
| From: | Pablo M. Rivas | Date: | Tue, 29 Jun 2004 19:55:56 +0000 |
| Subject: | Re[2]: [PHP-DB] MySQL: Random select with specific count of a column | ||
| References: | 1 2 3 | Groups: | php.db |
| Request: | Send a blank email to php-db+get-35130@lists.php.net to get a copy of this message | ||
Hello Torsten,
TR> thanks for your help. Unfortunately it's always returning the same row for
TR> each category (maybe because of the group by) and only one row for each
TR> category. I need to select 2 random rows for each category. Any more ideas?
I'ts returning 2 rows each RECORD, but the fields have
the same name, so if you do a mysql_fetch_array, you'll find only
one...
Change your sql to:
select a.category as categorya, a.name as namea, b.name as
nameb from tablename as a left join tablename as b on
a.category = b.category and a.name<>b.name and
a.language=b.language where a.language='de' and b.name is not
null group by a.category
and you'll do:
echo "<tr><th>languaje<td>category<td>name\n";
while ($data=mysql_fetch_array($result)) {
echo "<tr><td>de<td>" . $data["categorya"] .
"<td>" . $data["namea"];
echo "<tr><td>de<td>" . $data["categorya"] .
"<td>" . $data["nameb"];
}
ok?...
EACH ROW CONTAINS 2 RECORDS...but.. we need something more, 'cause
you're needing RANDOM rows...
If you need RANDOM, i think mysql will not help you, and you must
change your query to:
select a.category as categorya, a.name as namea, b.name as
nameb from tablename as a left join tablename as b on
a.category = b.category and a.name<>b.name and
a.language=b.language where a.language='de' and b.name is not
null order by a.category
and then do something in php like store everything in an array, and take some
randomly...
srand(time());
$lastcat=0;
echo "<tr><th>languaje<td>category<td>name\n";
while ($data=mysql_fetch_array($result)) {
if (($lastcat) and ($lastcat<>$data["categorya"])) {
echo "<tr><td>de<td>" . $lastcat . "<td>" .
$myarray[$lastcat][rand()%count($myarray[$lastcat])];
echo "<tr><td>de<td>" . $lastcat . "<td>" .
$myarray[$lastcat][rand()%count($myarray[$lastcat])];
}
$myarray[$data["categorya"]][]=$data["namea"];
$myarray[$data["categorya"]][]=$data["nameb"];
$lastcat=$data["categorya"];
}
echo "<tr><td>de<td>" . $lastcat . "<td>" .
$myarray[$lastcat][rand()%count($myarray[$lastcat])];
echo "<tr><td>de<td>" . $lastcat . "<td>" .
$myarray[$lastcat][rand()%count($myarray[$lastcat])];
mhhh... i don't like... but i think this will work and you can make it
better!!!!!!
--
Best regards,
Pablo