Joins

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
bob_the _builder
Forum Contributor
Posts: 131
Joined: Sat Aug 28, 2004 12:25 am

Joins

Post 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]
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post 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.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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).
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post 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.
bob_the _builder
Forum Contributor
Posts: 131
Joined: Sat Aug 28, 2004 12:25 am

Post 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
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post 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.
Post Reply