#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2011
    Posts
    3
    Rep Power
    0

    SQL Display single unique cell in "grouped" select command


    Hey Everyone,

    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:

    Code:
    $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";
    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?

    Any help you could give would be greatly appreciated.

    Thanks!
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    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:

    sql Code:
    SELECT
    	...
    FROM
    	photo_uploads
    	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.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".

IMN logo majestic logo threadwatch logo seochat tools logo