margaux Posted January 10, 2012 Share Posted January 10, 2012 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? Link to comment Share on other sites More sharing options...
Jonathon Posted January 10, 2012 Share Posted January 10, 2012 What have you tried? Link to comment Share on other sites More sharing options...
margaux Posted January 10, 2012 Author Share Posted January 10, 2012 $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. Link to comment Share on other sites More sharing options...
Jonathon Posted January 10, 2012 Share Posted January 10, 2012 Have you tried this in PHPMyAdmin to see what error it returns? Are you sure you want a LEFT JOIN too, are these results now inclusive of each other? Link to comment Share on other sites More sharing options...
margaux Posted January 10, 2012 Author Share Posted January 10, 2012 Thanks for your suggestion. Here's the error I received: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '$q = "SELECT c.comment, DATE_FORMAT(c.dateSub, '%M, %Y') AS ds, CONCAT(m.firstNa' at line 1 I'm not sure what kind of join to use - I chose the left join as I want to display all the comment fields from the comments table with its corresponding author, getting the author's name via the userID. You can view my protocol to get an idea of what I'm trying to do. After each comment the first number is the userId which I want to display as the author - firstName lastName - then the date formatted as month, year. Link to comment Share on other sites More sharing options...
margaux Posted January 10, 2012 Author Share Posted January 10, 2012 I did find one mistake... starting at CONCAT it should be CONCAT(u.firstName, ' ', u.lastName) instead of an m. preceding firstName and lastName but this amendment has not made any difference. Link to comment Share on other sites More sharing options...
Jonathon Posted January 10, 2012 Share Posted January 10, 2012 Inside PHPMyAdmin you only need to type the query itself so: 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 Not $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"; After you corrected this error, what was the new error? 1 Link to comment Share on other sites More sharing options...
margaux Posted January 10, 2012 Author Share Posted January 10, 2012 I didn't use the "$q = " so same error response ... #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"SELECT c.comment, DATE_FORMAT(c.dateSub, '%M, %Y') AS ds, CONCAT(u.firstName, '' at line 1 Link to comment Share on other sites More sharing options...
Antonio Conte Posted January 11, 2012 Share Posted January 11, 2012 You use C and U as table acronyms. FROM comments AS c LEFT JOIN users AS u Where does m come from? CONCAT(m.firstName, ' ', m.lastName) AS author 1 Link to comment Share on other sites More sharing options...
Josee Posted January 11, 2012 Share Posted January 11, 2012 You still need to delete the quotation mark at the beginning of the query. That's why the error message shows """ at the beginning of your query. #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"SELECT c.comment, DATE_FORMAT(c.dateSub, '%M, %Y') AS ds, CONCAT(u.firstName, '' at line 1 1 Link to comment Share on other sites More sharing options...
margaux Posted January 11, 2012 Author Share Posted January 11, 2012 thanks for the replies - @Antonio -see post 6 where I spotted that error. @Josee - I put $q in quotes just for that post, not in the actual code. My bad. Here's what I've used $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"; It doesn't work as I want it to but at least I am no longer getting a syntax error. I'm now getting all comments displayed as many times as there are userIds on the user database. So I'm going back to read more about joins. Link to comment Share on other sites More sharing options...
Antonio Conte Posted January 11, 2012 Share Posted January 11, 2012 Try: LEFT JOIN users ON users.userId = comments.userId 1 Link to comment Share on other sites More sharing options...
margaux Posted January 11, 2012 Author Share Posted January 11, 2012 This worked $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"; Thanks again for replying - helped me to know if I was on the right track or not. Link to comment Share on other sites More sharing options...
Recommended Posts