[SOLVED] Table Join Query

[iceblox]

Sorry guys, me again!

 

I have a ratings table and a models table. What id like to do is display the latest phones from the models table regardless of whether they have been rated or not.. and if they have been rated then i would like to get the average rating score.

 

I think im there but im sure there is something i need to add with regards to NULL?

 

I think im on the right tracks just not sure how to complete it. This is what i have..

 

<?php
$result2 = $db->sql_query("SELECT rating_id, modname, manname, manid, modid, avg(rating_num) AS columnaverage FROM ratings a, models b WHERE a.rating_id = b.modid GROUP BY rating_id ORDER BY columnaverage DESC");
                			while($row2 = $db->sql_fetchrow($result2))
		{
                $format_number = number_format($row2[columnaverage], 2, '.', '');
			$urlmanname = str_replace(" ", "-", "$row2[manname]");
			$urlmodname = str_replace(" ", "-", "$row2[modname]");

                echo "&#149; <a href=\"$urlmanname-$urlmodname-Mobile-Review-$row2[modid].html\">$row2[manname] $row2[modname] Reviews</a> <b>$format_number</b><br>";
}
?>

[kickstart]

Hi

 

You need to use an OUTER JOIN to bring back data from one table even if there is no matching data on the other table. Something like:-

 

<?php
$result2 = $db->sql_query("SELECT rating_id, modname, manname, manid, modid, avg(rating_num) AS columnaverage FROM models b LEFT OUTER JOIN ratings a ON b.modid = a.rating_id GROUP BY rating_id ORDER BY columnaverage DESC");
                			while($row2 = $db->sql_fetchrow($result2))
		{
                $format_number = number_format($row2[columnaverage], 2, '.', '');
			$urlmanname = str_replace(" ", "-", "$row2[manname]");
			$urlmodname = str_replace(" ", "-", "$row2[modname]");

                echo "&#38;#149; <a href=\"$urlmanname-$urlmodname-Mobile-Review-$row2[modid].html\">$row2[manname] $row2[modname] Reviews</a> <b>$format_number</b><br>";
}
?>

 

All the best

 

Keith

[iceblox]

Hi Keith,

 

Thanks for you reply, worked a treat!!

 

Thanks again