Map username to latest postings script

With my limited php skills I'm creating a simple script that shows the latest postings from my MLF installation along with post date and author. Getting the post, date, topic ID etc. from mlf_entries turned out easy, but getting the author was more challenging since the username is stored in mlf_userdata. I suppose I have to use JOIN to map the specific posting in mlf_entries to te correct user_id from mlf_userdata?

Could anyone explain to me how this query should look?

Example of what I want to achieve (except for the Username I have everything in place):

Latest forum thread name
by Username on 6 December 2015
Second latest forum thread name
by Username on 6 December 2015

I know there is a function for this which is supplied by MLF that does what I want to achieve, but I want to avoid having to go via RSS.

What I have so far:

 
<?php
$query = "SELECT UNIX_TIMESTAMP(time), subject, category, tid, views, last_reply, text, last_reply, user_id FROM
mlf2_entries GROUP BY tid ORDER BY last_reply DESC LIMIT 0, 5";
$result = mysql_query($query, $conn) or die("Query failed : " .
mysql_error());
 
echo "<ul>";
 
while($row = mysql_fetch_array( $result )) {
 
$topic = $row["subject"];
$tid = $row["tid"];
$category = $row["category"];
$user_id = $row["user_id"];
$last_reply = $row["last_reply"];
 
 
echo "<li><a href= \"/index.php?mode=thread&amp;id=".$tid."\">$topic</a></li>";
}
 
echo "</ul>";
 
mysql_free_result($result);
mysql_close($conn);
?>

Hi Wagner,

[…] Getting the post, date, topic ID etc. from mlf_entries turned out easy, but getting the author was more challenging since the username is stored in mlf_userdata.

Hhm, why don’t you just use the field user_name from mlf_entries? Note: If you run a forum where users without registration can post, this is all you have.

I suppose I have to use JOIN to map the specific posting in mlf_entries to te correct user_id from mlf_userdata?

IMHO, this would only makes sense if

you run a forum with registered users only and
you mandate them to state their “real name” in their profile.

If yes, I would suggest to create a new VIEW in phpMySQL which collects the required fields from mlf_entries and mlf_userdata (JOIN user_id which exists in both) in order to display user_real_name. In PHP simply query this view. Much easier – and avoids the loop (which can take ages to complete).

I have done similar stuff there. I was interested in displaying the category a post belongs to. My view is based on mlf_entries and mlf_categories joined by mlf_entries.category = mlf_categories.id.

--
Cheers,
Alfie (Helmut Schütz)
BEBA-Forum (v1.8β)

You're correct, my forum is only open to registered users. I don't have the field user_name in my mlf2_entries table. If so, it would have been easy :-) I'm running 2.3.4, and the missing "2" in the db prefix of my original post may have suggested I was running an older version.

And by username I mean the name with which you log in, for example, Wagner and Alfie. I don't want to show the real names people have in their profiles.

Thanks for the suggestion, I'll try to play around with joining mlf2_entries.user_id = mlf2_userdata.user_name

This works:

"SELECT * FROM mlf2_entries, mlf2_userdata WHERE mlf2_userdata.user_id = mlf2_entries.user_id GROUP BY tid ORDER BY last_reply DESC LIMIT 0, 5";

This shows the latest postings, original author and date of the latest reply. However, I want the username of the last person who replied. How can I do that?

If I use this query:

"SELECT * FROM mlf2_entries, mlf2_userdata WHERE mlf2_userdata.user_id = mlf2_entries.user_id GROUP BY pid ORDER BY last_reply DESC LIMIT 0, 5";

I get this output:

Lorem ipsum thread
by Ghost on 2015-12-07 09:05:58
Lorem ipsum thread
by Evil Knievel on 2015-12-07 09:05:58
Another thread
by Doc on 2015-12-07 09:05:58
Lorem ipsum thread
by Caroline on 2015-12-07 09:05:58
Another thread
by Marko on 2015-12-06 20:23:55

This is partly what I want, but I only want to show each topic once with the latest posting date and the last person who replied.

I now have this query:

"SELECT * FROM mlf2_entries, mlf2_categories, mlf2_userdata WHERE (mlf2_entries.category = mlf2_categories.id) AND (mlf2_userdata.user_id = mlf2_entries.user_id) GROUP BY tid ORDER BY last_reply DESC LIMIT 0, 5"

That I use to render the following:

Thread title
by username 2 minutes ago in Category

However, I still get the username of original poster. I would greatly appreciate tips on how to show the person who last responded instead. Is it possible or something I should forget?

RSS Feed of thread

my little forum

Map username to latest postings script (General)

Do you mean user_real_name?

No, I mean user_name

This almost work

Still stuck