Jump to content



Photo
- - - - -

Joins


  • Please log in to reply
12 replies to this topic

#1 margaux

margaux

    Advanced Member

  • Members
  • PipPipPip
  • 451 posts

Posted 10 January 2012 - 5:10 PM

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?
  • 0

#2 Jonathon

Jonathon

    Advanced Member

  • Members
  • PipPipPip
  • 1,020 posts

Posted 10 January 2012 - 5:20 PM

What have you tried?
  • 0

#3 margaux

margaux

    Advanced Member

  • Members
  • PipPipPip
  • 451 posts

Posted 10 January 2012 - 5:37 PM

$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.
  • 0

#4 Jonathon

Jonathon

    Advanced Member

  • Members
  • PipPipPip
  • 1,020 posts

Posted 10 January 2012 - 5:39 PM

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?
  • 0

#5 margaux

margaux

    Advanced Member

  • Members
  • PipPipPip
  • 451 posts

Posted 10 January 2012 - 5:57 PM

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.
  • 0

#6 margaux

margaux

    Advanced Member

  • Members
  • PipPipPip
  • 451 posts

Posted 10 January 2012 - 6:08 PM

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.
  • 0

#7 Jonathon

Jonathon

    Advanced Member

  • Members
  • PipPipPip
  • 1,020 posts

Posted 10 January 2012 - 6:41 PM

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

#8 margaux

margaux

    Advanced Member

  • Members
  • PipPipPip
  • 451 posts

Posted 10 January 2012 - 6:54 PM

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
  • 0

#9 Antonio Conte

Antonio Conte

    Advanced Member

  • Members
  • PipPipPip
  • 1,054 posts
  • LocationOslo, Norway

Posted 11 January 2012 - 4:11 AM

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

#10 Josee

Josee

    Advanced Member

  • Members
  • PipPipPip
  • 112 posts

Posted 11 January 2012 - 4:53 AM

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

#11 margaux

margaux

    Advanced Member

  • Members
  • PipPipPip
  • 451 posts

Posted 11 January 2012 - 4:56 AM

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.
  • 0

#12 Antonio Conte

Antonio Conte

    Advanced Member

  • Members
  • PipPipPip
  • 1,054 posts
  • LocationOslo, Norway

Posted 11 January 2012 - 5:15 AM

Try:
LEFT JOIN users ON users.userId = comments.userId

  • 1

#13 margaux

margaux

    Advanced Member

  • Members
  • PipPipPip
  • 451 posts

Posted 11 January 2012 - 6:02 AM

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.
  • 0