Better method

[me102]

Hello I have a rating script what I need is a faster and better way of getting the title from the first query so i dont have to run 2 queries.

 

 

$q1 = "select ItemID, sum(Rating) as mr from dd_rating group by ItemID order by mr desc limit 0,4";
$r1 = mysql_query($q1) or die(mysql_error());
while($a1 = mysql_fetch_array($r1))
{
$q2 = "select ItemTitle from dd_items where ItemID = '$a1[0]' ";
$r2 = mysql_query($q2) or die(mysql_error());
while($a2 = mysql_fetch_array($r2)){

[iblood]

here's what I will do in this kinds of situation

 

<?php 


$q1 = "select ItemID, sum(Rating) as mr from dd_rating group by ItemID order by mr desc limit 0,4";
$r1 = mysql_query($q1) or die(mysql_error());

$q2parts = array();
while($a1 = mysql_fetch_array($r1))
{
$q2parts[] = "ItemID = '$a1[0]'";
}

$q2results = array();
if (!empty($q2parts)) {
$q2 = "select ItemTitle from dd_items where ".implode(' OR ', $q2parts); 
$r2 = mysql_query($q2) or die(mysql_error());

while($a2 = mysql_fetch_array($r2)){
	$q2results = $a2;	
}
}

?>

 

I'm pretty sure there's some workaround using SQL joins, but I'm just not a fan of JOINS.

[awjudd]

Using JOINs makes it insanely easy.

 

SELECT r.ItemID, it.ItemTitle, SUM(r.Rating) AS mr
FROM dd_rating r
JOIN dd_items it ON r.ItemID = it.ItemID
GROUP BY r.ItemID, it.ItemTitle
ORDER BY mr DESC
LIMIT 0, 4

 

I'm not sure why iblood isn't a fan of JOINs but that is just crazy.

 

~juddster

[me102]

thank you juddster just what I needed.