[gadgetgirl]

I"m struggling to understand how best to use joins. Here's an example I'm trying to code. I have 2 tables in a d/b - User table stores userId, firstName, lastName, dateOfStay and some other fields; Comments table stores commentId, userId, comment, dateSubmitted.


I would like to display all the comments with the corresponding firstName, lastName and dateSubmitted in descending order by dateSubmitted. I know I need to use a join of some kind, an outer join I think, and would like to use a CONCAT so I can display the author of the comment as firstname lastname and display the dateSubmitted formatted to month, year which is stored as a timestamp using the NOW function.


Any suggestions?

[webdesigner93]

gadgetgirl, on 10 January 2012 - 10:11 PM, said:

I"m struggling to understand how best to use joins. Here's an example I'm trying to code. I have 2 tables in a d/b - User table stores userId, firstName, lastName, dateOfStay and some other fields; Comments table stores commentId, userId, comment, dateSubmitted.


I would like to display all the comments with the corresponding firstName, lastName and dateSubmitted in descending order by dateSubmitted. I know I need to use a join of some kind, an outer join I think, and would like to use a CONCAT so I can display the author of the comment as firstname lastname and display the dateSubmitted formatted to month, year which is stored as a timestamp using the NOW function.


Any suggestions?

mmm i dont think you need a join you can do this

$sql = mysql_query("SELECT * FROM `comments` ORDER BY `dateSubmitted` DESC");

[gadgetgirl]

the comments table only stores the userId - I need a join of some kind to get the first and last name of the userID from the users table. Here's what I initially tried

$q = "SELECT c.comment, DATE_FORMAT(c.dateSub, '%M, %Y') AS ds, CONCAT(m.firstName, ' ', m.lastName) AS author 
FROM comments AS c LEFT JOIN users AS u USING userId ORDER BY dateSub DESC";

It didn't work.

[gadgetgirl]

I did find one mistake... starting at CONCAT it should be

CONCAT(u.firstName, ' ', u.lastName)

a u. should precede firstName and lastName instead of an m. but this amendment has not made any difference. I'm still getting a #1064 - You have an error in your SQL syntax.

[Renaissance-Design]

Lose the CONCAT - JOIN User on User.userId = Comments.userId and manipulate the results in PHP to get the strings you want to output.

Edit: rationale behind that is filesystem & DB access are the slowest parts of any application so if you can get the data you want with a very simple SQL query then perform simple PHP string functions/concatenation, that's likely the most efficient way.

This post has been edited by Renaissance-Design: 10 January 2012 - 11:53 PM

[gadgetgirl]

Thanks Renaissance-Design, that helped, but I'm not quite there. I need to read up more on the different types of joins.

$q = "SELECT comments.comment, DATE_FORMAT(comments.dateSub, '%M %Y') AS ds, CONCAT(users.firstName, ' ', users.lastName) AS author 
FROM comments LEFT JOIN users ON userId = comments.userId ORDER BY comments.dateSub DESC";

This listed every comment as many times as there are userIds on the users table, but at least I'm no longer getting a syntax error. Off to read more about joins!

Also thanks for the pointer on efficient coding - once I get this working, I will try your way vis a vis using PHP to manipulate the raw data.

This post has been edited by gadgetgirl: 11 January 2012 - 10:03 AM

[gadgetgirl]

$q = "SELECT comments.comment, DATE_FORMAT(comments.dateSub, '%M %Y') AS ds, CONCAT(users.firstName, ' ', users.lastName) AS author 
FROM users INNER JOIN comments USING (userId) ORDER BY comments.dateSub DESC";

finally got there.

[webdesigner93]

gadgetgirl, on 11 January 2012 - 11:04 AM, said:

$q = "SELECT comments.comment, DATE_FORMAT(comments.dateSub, '%M %Y') AS ds, CONCAT(users.firstName, ' ', users.lastName) AS author 
FROM users INNER JOIN comments USING (userId) ORDER BY comments.dateSub DESC";

finally got there.

aww looks like alot cleaner code to good job