Page 1 of 1

Joins

Posted: Thu May 31, 2007 6:00 pm
by bob_the _builder
feyd | Please use

Code: Select all

,

Code: Select all

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]

Code: Select all

$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)) {
Thanks


feyd | Please use

Code: Select all

,

Code: Select all

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]

Posted: Thu May 31, 2007 6:45 pm
by superdezign

Code: Select all

SELECT `userid` FROM `posts` WHERE `topicid`='$currentTopic' ORDER BY `added` DESC
Maybe? :?:

Of course, you should adapt it to all of your joins and such... Though, I think the only one you'd need is the `users` table.

Posted: Thu May 31, 2007 6:53 pm
by Weirdan
On the page where the headings are displayed I am trying to show the last poster for each heading.
you need either 1+n queries like superdezign suggested or one query with subquery (some older mysql servers don't support subqueries).

Posted: Thu May 31, 2007 6:57 pm
by superdezign
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.

Kind of like solving a complex equation.

Posted: Thu May 31, 2007 9:57 pm
by bob_the _builder
Hi,

Still having no luck :(

can never get it to grab the correct information.

problem maybe the posts table doesnt hold the headingid, making a match harder.

I havnt had much to do with joins, only simple 2 tables, have no idea on sub querys and query in the loop messes things up.


Thanks

Posted: Thu May 31, 2007 10:48 pm
by superdezign
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.