Jump to content


Photo

Randomizing query results


  • Please log in to reply
3 replies to this topic

#1 ViperSBT

ViperSBT
  • Members
  • PipPipPip
  • Advanced Member
  • 49 posts

Posted 09 June 2006 - 02:41 AM

Using the following code:
$sql = "SELECT m.id, m.name, m.picture, AVG(s.score) as average FROM models m LEFT JOIN scoring s ON s.id = m.id WHERE m.category = '$game' GROUP BY m.id ORDER BY average DESC";
$result = mysql_query($sql);
$count = mysql_num_rows($result);
$row = mysql_fetch_array($result);

$contestants = array_rand($row, 10);
I am getting an error:
[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]Warning: array_rand(): Second argument has to be between 1 and the number of elements in the array...[/quote]
I guess what I am trying to do may be a little different than what array_rand() is for. I rant to take 10 random rows and put them in the new array. The array $row has 30 rows of elements, and I want to take 10 on those.


#2 kenrbnsn

kenrbnsn
  • Staff Alumni
  • Advanced Member
  • 8,235 posts
  • LocationHillsborough, NJ, USA

Posted 09 June 2006 - 02:51 AM

When you do an mysql_fetch_array() or mysql_fetch_assoc() you are getting one row returned from the set of rows matching your query. In order to fetch all of them you need to put the function in a "while" loop:
<?php
$sql = "SELECT m.id, m.name, m.picture, AVG(s.score) as average FROM models m LEFT JOIN scoring s ON s.id = m.id WHERE m.category = '$game' GROUP BY m.id ORDER BY average DESC";
$result = mysql_query($sql) or die("Problem with the query: $sql<br>" . mysql_error());
$count = mysql_num_rows($result);
$tmp = array();
while ($row = mysql_fetch_array($result)) 
     $tmp[] = $row;
$contestants = array_rand($tmp, 10);
?>

Ken


#3 poirot

poirot
  • Members
  • PipPipPip
  • Advanced Member
  • 646 posts
  • LocationAustin, TX

Posted 09 June 2006 - 02:54 AM

You can also get a randomly ordered result from MySQL.

Refer to this topic:
[a href=\"http://www.phpfreaks.com/forums/index.php?showtopic=94498\" target=\"_blank\"]http://www.phpfreaks.com/forums/index.php?showtopic=94498[/a]

Edit: What is the point of using ORDER BY average if you are going to randomize them?
~ D Kuang

#4 ViperSBT

ViperSBT
  • Members
  • PipPipPip
  • Advanced Member
  • 49 posts

Posted 09 June 2006 - 03:18 AM

That the ORDER BY RND() worked perfect! Thanks....

The other ORDER BY was when I was trying to do something different, didn't need to be there...




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users