and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
Hi,
I am writing a basic forum. I am struging to grab the last poster for display in the forum index.
My table layout is:
[syntax="sql"]CREATE TABLE headings (
headingid int(5) NOT NULL auto_increment,
heading varchar(50) NOT NULL,
description varchar(255) NOT NULL,
PRIMARY KEY (headingid)
);
CREATE TABLE topics (
topicid int(5) NOT NULL auto_increment,
headingid int(5) NOT NULL,
userid int(5) NOT NULL,
views int(5) NOT NULL,
topic varchar(50) NOT NULL,
PRIMARY KEY (topicid)
);
CREATE TABLE posts (
postid int(5) NOT NULL auto_increment,
topicid int(5) NOT NULL,
userid int(5) NOT NULL,
Post text NOT NULL,
added datetime NOT NULL,
viewed int(5) NOT NULL,
PRIMARY KEY (postid)
);
On the page where the headings are displayed I am trying to show the last poster for each heading.
I have tried with no luck, here is what I have at the moment which is no where near correct:[/syntax]
$sql = mysql_query("SELECT u.first_name, u.first_name, p.added
FROM topics as t
LEFT JOIN posts as p ON t.topicid = p.topicid
LEFT JOIN users as u ON u.userid = p.userid
LEFT JOIN headings as h ON h.headingid = t.headingid
WHERE t.headingid = h.headingid
ORDER BY t.topicid DESC LIMIT 1") or die (mysql_error());
while ($row1 = mysql_fetch_array($sql)) {
$sql = mysql_query("SELECT * FROM headings");
while ($row = mysql_fetch_array($sql)) {
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
If you plan to do it all from one query, you should open up MySQL and try to start from the deepest, most direct table (`users` or `posts`) and find the latest, then gradually move out. It'll be much easier to determine where you're not looking at it the right way rather than just attempting to do it all in one-shot.
Then maybe you should follow my advice. Build the query from the inside-out. Take it step-by-step. More work now, usually, translates to less work later.