Re: Re[2]: [PHP-DB] MySQL: Random select with specific count of a column

"Pablo M. Rivas" <pmrivas01@yahoo.com.ar> wrote in message news:19993319456.20040629165556@yahoo.com.ar... > 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!!!!!! Hi Pablo, thank you very much for your extensive efforts! This will definitely help me. Best regards, Torsten