Combine Rows with Duplicate Column Data

[fwapah]

i have a table that has multiple reviews tied to multiple reviewers. the columns for each row are reviewer, product num, product score. ed jones reviewed product 1 and product 2, and shirley smith reviewed product 2 and product 3. each reviewer/review combo has its own row.

my question is, how do i display
Product 1, Score Jones
Product 2, Score Jones, Score Smith
Product 3, Score Smith

I am not sure how I can get the scores from each row to correlate with the product in one row.

I have a table, reviews, that looks like this
Reviewer || Prod Num || Score
Jones      ||      1        || 2.1
Jones      ||      2        || 1.6
Smith      ||      2      || 1.5
Smith      ||      3       || 2.7

I'm trying to get it to output this:
Prod Num || Reviewer||Score||Reviewer||Score
       1      ||    Jones   ||  2.1
       2      ||    Jones   ||  1.6 ||   Smith  || 1.5
       3      ||    Smith   ||  2.7

Thanks for the help!!

[Zyppora]

That's logic you won't be able to implement (completely) in your SQL query. You can use ORDER BY to make sure the scores are grouped together with respect to the review:

Code: [Select]

SELECT prodnum, reviewer, score FROM reviews ORDER BY prodnum ASC, reviewer ASC
Then you can loop through the results with the following if statement:

Code: [Select]

if ($current_prodnum != $result['prodnum']) {
  echo "<br>$result['prodnum']";
  $current_prodnum = $result['prodnum'];
}
echo " || $result['reviewer'] || $result['score']";