Jump to content
Larry Ullman's Book Forums

Recommended Posts

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

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

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?

  • Upvote 1
Link to comment
Share on other sites

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

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

  • Upvote 1
Link to comment
Share on other sites

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

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

 Share

×
×
  • Create New...