Mysql query question

Forum Moderators: open

Message Too Old, No Replies

Mysql query question

Mister_L

2:32 pm on Jul 5, 2009 (gmt 0)

Hi,

I have a mysql database with a table containing reviews about products. Each review has a rating from 1 to 5.I want to choose 5 reviews randomly, and display them in a way that the top rated ones appear first.How should the query look like?

SELECT * FROM reviews WHERE product_id='$id'....(what's next?)

Thanks!

janharders

2:49 pm on Jul 5, 2009 (gmt 0)

mh, maybe

ORDER BY RAND(), rating DESC

could work

If not: get your five results, then sort them in your script.

Mister_L

2:56 pm on Jul 5, 2009 (gmt 0)

I tried ORDER BY RAND(), rating DESC, but it doesn't work.
How do I sort the results in my script?

janharders

3:29 pm on Jul 5, 2009 (gmt 0)

of course, now that I think of it, it's clear why it's not working ;)

if you're using php, look at [php.net ]

Mister_L

11:08 pm on Jul 5, 2009 (gmt 0)

I just don't get it.

I tried a different approach with the array_multisort command but I get a lot of errors.Here is my code:

$query= "SELECT ReviewSource,Date,Nick,Location,Rating,RevSummary,ReviewText FROM reviews WHERE ProductID='$id' AND ProductType='$product_type' ORDER BY ReviewSource,RAND() LIMIT 5";

}

$result = mysql_query($query) or die("display_db_query:" . mysql_error());

// Sort Reviews By Rating

$i=1;

while ($row_rev=mysql_fetch_assoc($result))
{
$data[$i]=$row_rev;
$i=$i+1;
}

foreach ($data as $key => $row) {

$Rating[$key] = $row['Rating'];

array_multisort($Rating,SORT_DESC,$data);
}

Why doesn't it work?

janharders

1:37 pm on Jul 6, 2009 (gmt 0)

I'm not familiar with array_multisort (and php has way too many sort-functions), but why don't you want to use usort?

if your field is really called Rating, this might just work:


function cmp($a, $b)
{
  if ($a['Rating'] == $b['Rating']) {
    return 0;
  }
  return ($a['Rating'] < $b['Rating']) ? -1 : 1;
}usort($data, "cmp");