trouble parsing two tables

scrypte

I am trying to get who is scored the highest from the Rating table and display the tutorials name from the hosted table.

<?
# get the top tutorials
$sql_toptut = "SELECT r.rateID, r.average, t.tutID FROM r.rating,t.hosted ORDER BY r.average DESC LIMIT 0,25";
$result_toptut = mysql_query($sql_toptut);
while ($arr_toptut = mysql_fetch_array($result_toptut)) {	
?>
- <a href="tutorial.php?tutID=<? echo $arr_toptut['r.tutID']; ?>"><? echo $arr_toptut['t.hosted']; ?></a> &nbsp; (<a href="rate.php?tutID=<? echo $arr_toptut['r.tutID']; ?>">rate</a>)<br>
<? } ?>

Here is the two tables:

CREATE TABLE rating (
ratingID int(5) NOT NULL auto_increment,
tutID int(5) NOT NULL default '0',
votes tinytext NOT NULL,
score tinytext NOT NULL,
average tinytext NOT NULL,
PRIMARY KEY (ratingID)
)

CREATE TABLE hosted (
tutID int(5) NOT NULL auto_increment,
author varchar(200) NOT NULL default '',
desc varchar(255) NOT NULL default '',
content text NOT NULL,
picID int(5) NOT NULL default '0',
rateID int(5) NOT NULL default '0',
PRIMARY KEY (tutID)
)

robtbs

Hi, whats the actual problem... does the code above return anything....any errors etc.

//edit
give me a sec im writing you something

robtbs

Just a little pointer, you shouldnt really need rateID in the hosted table.
because you can select it using the tutID in the rating table.

Also, its best not to use reserved words such as 'desc' as field names.
for eg... if you try "SELECT desc FROM hosted;" it will error.
you "can" use them but you must quote them eg... "SELECT desc FROM hosted;"

🙂

<?php
# get the top tutorials
$sql_toptut = "SELECT r.rateID, r.average, t.tutID ".
"FROM hosted t ".
"LEFT JOIN rating r ".
"ON t.tutID = r.tutID ".
"ORDER BY r.average DESC LIMIT 0,25";

$result_toptut = mysql_query($sql_toptut);
while ($arr_toptut = mysql_fetch_array($result_toptut))
{
	print('- <a href="tutorial.php?tutID='.$arr_toptut['tutID'].'">'.$arr_toptut['desc'].'</a>
	 &nbsp;Current Avg: '.$arr_toptut['average'].' (<a href="rate.php?tutID='.$arr_toptut['tutID'].'">rate</a>)<br>');
}

?>