[SOLVED] I am strugling to perform this little task

[npsari]

Allow me to introduce you to my database...

 

I have a table called phpbb_topics

and a table called phpbb_users

 

I want to display the latest 10 topics (and the image of who posted them)

 

I did this but doesnt work!

 

$q = "SELECT * FROM phpbb_topics, phpbb_users ORDER BY topic_last_post_id DESC LIMIT 10;";
$res = @mysql_query($q);
while($r = @mysql_fetch_array($res)){

print "<IMG src=\"/forums/images/avatars/$r[user_avatar]\">";
echo "<a href='/forums/viewtopic.php?t={$r['topic_id']}'>{$r['topic_title']}</a><br>";
print "Views: $r[topic_views]";
print "  ";
print "Replies: $r[topic_replies]";
}

 

phpbb_users has a row called user_id

and phpbb_topics has a row called topic_poster (These are the 2 equal ones)

 

Can you show me how to write the $q please

[cooldude832]

the rules around here are never post sql errors if you are suppressing errors so cahnge

$res = @mysql_query($q);

 

to

$res = mysql_query($q) or die(mysql_error());

[pocobueno1388]

Try

 

SELECT t.topic_id, t.topic_title, t.topic_views, t.topic_replies
FROM phpbb_topics t
LEFT JOIN phpbb_users u
ON u.user_id = t.topic_poster
ORDER BY t.topic_last_post_id DESC LIMIT 10

[npsari]

thanks for the code

 

i tried it, everything works ok, however, the image is not showing

 

The image row is called user_avatar and it is available in the table phpbb_users

 

do you know what should i add to the code you sent me

[pocobueno1388]

Oops, sorry...I left that field out.

 

SELECT t.topic_id, t.topic_title, t.topic_views, t.topic_replies, u.user_avatar
FROM phpbb_topics t
LEFT JOIN phpbb_users u
ON u.user_id = t.topic_poster
ORDER BY t.topic_last_post_id DESC LIMIT 10

[npsari]

Now things are working

thanks for the support

i appreciated it much!

byez

[npsari]

hmm, can you show me how to add a 3rd table if that is possible

 

What if i want to add the table phpbb_posts so i can select a row called post_time

 

is it hard to add to the above code

[pocobueno1388]

I don't know what your field names are called that link the tables, but something like this.

 

SELECT t.topic_id, t.topic_title, t.topic_views, t.topic_replies, u.user_avatar, p.post_time
FROM phpbb_topics t
LEFT JOIN phpbb_users u
ON u.user_id = t.topic_poster
LEFT JOIN phpbb_posts p
ON p.poster_id = t.topic_poster
ORDER BY t.topic_last_post_id DESC LIMIT 10

[npsari]

great,

 

thanks, i was courius for the future 

[pocobueno1388]

If you look, you will see the pattern in the query. Whenever you want to add something, just follow whats already there.