CODEWITHSUNDEEP
phpmysqlselectjoinjson
Igal
Igal

Reputation: 6083

Select data from 2 tables in mySQL, join it

I need to select data from 2 tables in my DB - comments and users. This is the structure of these tables:

comments:

commentID | commentDateAndTime | imageID | userID | comment

users:

userID | username

I need to select all the data in both tables, join it on userID, where imageID should be equal to $imageID. I tried the following SELECT statement:

    return json_encode(select("
    SELECT
     commentDateAndTime,
     imageID,
     userID,
     COMMENT,
     username
   FROM users
    JOIN comments
    ON comments.userID = users.userID
   WHERE comments.imageID = $imageID
   GROUP BY comments.comment   
"));

But I didn't receive any data.

Any advice, please?

Upvotes: 1

Views: 2378

Answers (2)

MrFusion
MrFusion

Reputation: 911

MySQL does auto-joins (from this page: "The FROM [...] clause indicates the table or tables from which to retrieve rows. If you name more than one table, you are performing a join").

Also, since there are two userID columns you need to be specific of which one by preceding the table name.

So your query could be like this:

SELECT commentDateAndTime, imageID, users.userID AS userID, comment, username
FROM comments, users
WHERE comments.userID = users.userID AND comments.imageID = $imageID

Upvotes: 2

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

Why are you using group by if you need all the result.

SELECT users.* , comments.*
FROM users JOIN comments ON comments.userID = users.userID 
WHERE comments.imageID = $imageID

Upvotes: 4

Related Questions