MySQL multitable select problem.

[mousey182]

Hi Guys,

Im having problems with some mysql select statements. I have a database containing 7 tables in the following format:

Adverts
Field Type Null Default
advert_id int(11) No 0
frequency int(11) No 0

keywords
Field Type Null Default
key_id int(11) No 0
keyword varchar(20) No

keywords_to_adverts
Field Type Null Default
s_key_id int(11) No 0
d_advert_id int(11) No 0

links
Field Type Null Default
link_id int(11) No 0

links_to_keywords
Field Type Null Default
s_link_id int(11) No 0
d_key_id int(11) No 0

users
Field Type Null Default
user_id int(11) No
last_visit date Yes NULL

users_to_links
Field Type Null Default
s_user_id int(11) No 0
d_link_id int(11) No 0
frequency int(11) Yes NULL
last_visit date Yes NULL

And Im trying to use a select to find out which adverts are most relevant to which users. I've come up with the following select statement to find out which users are related to which keywords, but when I try and further the statement so that it finds all the adverts that are related to that user, I get multiple repeated entries.(Bare in mind that this is part of a php code).

$r2 = mysql_query ("SELECT s_link_id, d_key_id, s_user_id, frequency, key_id, keyword 
					FROM links_to_keywords 
					JOIN users_to_links
					JOIN keywords
					ON links_to_keywords.s_link_id = users_to_links.d_link_id AND d_key_id = key_id
					WHERE s_user_id = $user_id
					ORDER BY frequency DESC
					LIMIT 0, 5");
if ($myrow = mysql_fetch_array($r2)) {
  		printf("<table border='1' cellspacing='1' cellpadding='1'>
				<td width='100px'>User Id</td>
				<td width='100px'>Key Word</td>
				<td width='100px'>Key Id</td>
				<td width='100px'>Frequency</td>
				");
  do {
		printf(		
			"<tr>
				<td width='100px'>".$myrow['s_user_id']."</td>
				<td width='100px'>".$myrow['keyword']."</td>
				<td width='100px'>".$myrow['key_id']."</td>
				<td width='100px'>".$myrow['frequency']."</td>
			</tr>");
  	 }
  while ($myrow = mysql_fetch_array($r2));}
  printf("</table>");

Anyone got any suggestions as to the best way to go about this, or if Im even doing it right?

Thanks alot for taking the time to read this lengthy post!!

Matt