September 2nd, 2013, 01:40 PM
SQL Display single unique cell in "grouped" select command
Got a quick question. So I have a MySQL Database Table set up like this:
user | user_friend | image_file | date_time | description
user1 | user2 | img1.jpg | 2013-09-02 | Pic of me!
user1 | user3 | img1.jpg | 2013-09-02 |
user1 | user4 | img1.jpg | 2013-09-02 |
I'm querying the results into a while loop so I can display the image, the username of the person that uploaded the photo (user) and the description for everyone in user column and user_friend column. I'm basically attempting to combine the rows so they act as one row.
Here is how I have set up my query:
The image description always gets inserted in the top row of the "Group", but along with the image itself I want all users in column (user_friend) to see the description cell. However, currently only those in the "user" column and the user in the first row of the "user_friend" column can see the description. I would like all users to see the description without having to alter the "INSERT" code to make duplicate descriptions down the row. Is there any way to do this?
$sql = "SELECT user, image_file, date_time, description FROM photos WHERE user='$log_username' OR user_friend='$log_username' GROUP BY image_file ORDER BY date_time DESC";
Any help you could give would be greatly appreciated.
September 2nd, 2013, 02:35 PM
the issue comes from a flawed data model and a misunderstanding of the relational concept.
SQL tables are no Excel tables. There is no "first row" or "top row", and you cannot leave fields empty in the sense of "see above". SQL tables represent sets, which means they are unordered. And each row is a complete dataset standing for itself (it may contain references to other rows, though).
So the first step to fix the data model would be to add the missing data, namely the descriptions.
However, SQL tables represent a specific relation. Something like "customer x bought y units of product z". Your table isn't like that. It's basically two relations merged into one: You have users uploading pictures. And you have users being friends with other users. Those are two completely different aspects. Having put them into a single table, you now face the problem of redundant data. You'd have to repeat all user-related data in every row belonging to that user. This violates the principle of normalization.
To fix the problem, you need to revise your data model: You have two relations, so you need two tables. Call them "photo_uploads" and "friendships" or something like that. The photos table only describes the uploads, nothing else. And the friendships table only assigns users to other users, nothing else.
To associate the data, join the two tables. On one hand, you have a photo shot by a user. And on the other hand, you have all friends of this user:
JOIN friendships ON photo_uploads.USER = friendships.USER
Note that your GROUP BY clause makes no sense and actually violates the SQL standard. Other database systems wouldn't even accept this. The GROUP BY clause has a specific meaning: It's used for applying aggregate functions like COUNT() or SUM() to specific subsets rather than all rows. It's not "grouping" in the colloquial sense. When people talk about "grouping", they usually want the ORDER BY clause.
As a rule of thumb: If you have no aggregate function in your query, you can't have a GROUP BY clause.
Last edited by Jacques1; September 2nd, 2013 at 02:37 PM.