How to order MySQL data from multiple queries?

[HDFilmMaker2112]

This a continuation from my XML thread.

 

If I were to go the Database route, how would I organize all the data retrieved from the DB?

 

Here's how my tables would be laid out:

 

Friends Table:

id | user_id | friend_id

 

Status Updates Table:

id | user_id | status_message | time_posted

 

Comments Table:

id | user_id | comment | time_posted | status_id

 

 

So from there I would query the friends table for the users friends, take the results from that query, and query the status updates table for status updates in a loop from the friend ids.

 

So I would end up with roughly something like this:

User Friend #1:

Status Update #1 - 6/10/2012 at 4:00 PM

Status Update #2 - 6/7/2012 at 1:23 PM

Status Update #3 - 6/5/2012 at 12: 24 PM

 

User Friend #4

Status Update #1 - 6/10/2012 at 5:00 PM

Status Update #2 - 6/7/2012 at 3:23 PM

Status Update #3 - 6/5/2012 at 11: 24 PM

 

So how would I order those returned status updates in chronological order, starting with the most recent? From the above, User Friend #4's Status Update #1 would need to be placed before User Friend #1 Status Update #1, and User Friend #4 Status Update #2 would need to be placed before User Friend #1 Status Update #2, etc.

 

 

Then I would need to add the comments from the comments table based on the status_id and order by time_posted (which this step is fairly straight forward).

 

I'm assuming this is definitely a place for PDO, transactions, and prepared statements.

[requinix]

Use one query, not two. With a JOIN.

SELECT f.friend_id, su.status_message, su.time_posted, or whatever you want
FROM status updates table AS su
JOIN friends table AS f ON su.user_id = f.friend_id
WHERE f.user_id = current user
ORDER BY su.time_posted DESC

[HDFilmMaker2112]

Use one query, not two. With a JOIN.

SELECT f.friend_id, su.status_message, su.time_posted, or whatever you want
FROM status updates table AS su
JOIN friends table AS f ON su.user_id = f.friend_id
WHERE f.user_id = current user
ORDER BY su.time_posted DESC

 

So if that works the way I think it does, it would save thousands of queries.

 

That would get all the status updates of all the friends in the friends list that are linked to the user id, correct?

[Psycho]

So if that works the way I think it does, it would save thousands of queries.

 

That would get all the status updates of all the friends in the friends list that are linked to the user id, correct?

Never, EVER run queries in loops. Do some research on how to properly query using JOINs.

[HDFilmMaker2112]

So if that works the way I think it does, it would save thousands of queries.

 

That would get all the status updates of all the friends in the friends list that are linked to the user id, correct?

Never, EVER run queries in loops. Do some research on how to properly query using JOINs.

 

I know how to use joins, I just couldn't get my head around how to format a single query that would pull every single status update for every single friend_id that is tied to the logged in user.

 

Thought I didn't really have any other choice.

[HDFilmMaker2112]

Bit of an extension on my last question. If I structure the friends table as follows:

 

id | user_id | friend_id
1  | 0001    | 0002
2  | 0001    | 0003
3  | 0002    | 0003

 

How would I alter the above query to pull friendships from both columns.

 

As you can see if I only query the user_id column and user 0002 is logged in, they'll only see the status updates of user 0003, but they should see 0003 and 0001. And user 0003 won't see updates from anybody.

[HDFilmMaker2112]

Never mind my last post; since I'm adding other features I'm going to have to redundant entries.

 

id | user_id | friend_id
1  | 0001    | 0002
2  | 0001    | 0003
3  | 0002    | 0003

 

Will actually need to be:

id | user_id | friend_id | is_friend | subscribed
1  | 0001    | 0002       | yes         | yes 
2  | 0002    | 0001       | yes         | yes 
3  | 0001    | 0003       | yes         | no 
4  | 0003    | 0001       | yes         | yes 
5  | 0002    | 0003       | no          | yes 
6  | 0003    | 0002       | no          | no

 

[Psycho]

Well, I had already typed this out before I saw your last post. Plus, once you understand the relationship you may see that you don't need redundant data (you never should). But, your last example doesn't make sense to me. Why would you have two records to describe the relationship between two users who are not friends? Wouldn't that structure require a record for every user-to-user relationship? That would cause the table to spiral out of control as new users are added.

 

Anyway, here was my response . . .

 

In this case you would need to modify the JOIN criteria to include an OR clause, and also add a condition to exclude those records for the own user's status updates. You would also need to modify the WHERE clause to include both cases for the target user.

 

Lastly, since you don't know if the friend's ID is in the user_id or friend_id columns you should just use the ID from the status updates instead.

 

This is something I would need to test against the actual database to be certain of, but I think this would work

SELECT su.user_id AS friend_id, su.status_message, su.time_posted, or whatever you want

FROM status updates table AS su

JOIN friends table AS f
  ON (su.user_id = f.user_id OR su.user_id = f.friend_id)
  AND su.user_id <> current user

WHERE (f.user_id = current user OR f.friend_id = current user)

ORDER BY su.time_posted DESC

[HDFilmMaker2112]

Well, I had already typed this out before I saw your last post. Plus, once you understand the relationship you may see that you don't need redundant data (you never should). But, your last example doesn't make sense to me. Why would you have two records to describe the relationship between two users who are not friends? Wouldn't that structure require a record for every user-to-user relationship? That would cause the table to spiral out of control as new users are added.

 

Anyway, here was my response . . .

 

In this case you would need to modify the JOIN criteria to include an OR clause, and also add a condition to exclude those records for the own user's status updates. You would also need to modify the WHERE clause to include both cases for the target user.

 

Lastly, since you don't know if the friend's ID is in the user_id or friend_id columns you should just use the ID from the status updates instead.

 

This is something I would need to test against the actual database to be certain of, but I think this would work

SELECT su.user_id AS friend_id, su.status_message, su.time_posted, or whatever you want

FROM status updates table AS su

JOIN friends table AS f
  ON (su.user_id = f.user_id OR su.user_id = f.friend_id)
  AND su.user_id <> current user

WHERE (f.user_id = current user OR f.friend_id = current user)

ORDER BY su.time_posted DESC

 

The reason for duplicate entries would be the subscribed column. Users could be friends but not necessarily be subscribed to their status updates.

 

I guess I could make another table to check whether the user is subscribed or not; but then I'd have another table to JOIN.

 

a subscribed table would look like this:

 

id | user_id | subscribed_id

 

With the subscribed_id being the id of the user their subscribed to. This table would be a one way relationship. 

 

So I'd have to JOIN the new table, and then add something to the WHERE clause (probably an AND?) to make sure only the users status updates the person is subscribed to are shown.

[Psycho]

OK, let me restate what I think I understand.

 

Users can have a friend relationship. Either both are friends with each other or neither is. Maybe you have a separate table to track friend requests until they are confirmed but, to me, that would logically be included in the fiends table. with a column for the confirmed date (e.g. if the confirmed date is null it is not yet confirmed).

 

Secondly, users can be subscribed to other users. But, the subscription can occur whether they are friends or not and one can be subscribed where another is not. This should definitely be stored in a separate table. Whereas you would have one record to record the friend status between two users, the subscription table would have one unique record for each subscription. SO, if two users were subscribed to each other, there would be two records.

 

So, I would expect two tables something like this:

 

friends

id | user_id | field_id | request_date | confirmation_date

You may not need the ID column if you have no need for it. I would think you would be using the user_id and fiend_id columns for all the joins anyway.

 

subscriptions

id | subscriber_id | subscription_date

Again, if you aren't going to use the ID for anything, you probably don't need it.

 

Now, would this be more complicated to do the JOINs? Yes, but it is the right way to store the data. You say

I know how to use joins . . .

but you apparently don't know how to do the more complicated JOINs. This is your opportunity to really learn how to use JOINs. So, do some research, ask questions, and make this a learning opportunity.

[HDFilmMaker2112]

If I go with the subscriber, would I even need to query the friends table? The subscriber table should contain all the information I need on which users status updates to pull.

[Psycho]

If I go with the subscriber, would I even need to query the friends table? The subscriber table should contain all the information I need on which users status updates to pull.

 

I can't say if you need the friends table or if it needs to be in the query, That is up to you based upon your needs. But, if you want to maintain a friend status and a subscriptions status, as I understand how you want to use those, they should be tracked in separate tables.

[HDFilmMaker2112]

Alright, here's what I came up with. It works, but I'm not sure if it works right.

 

 

SELECT comments.comment, comments.user_id, subscribe.subscriber_id, subscribe.user_id, status.message, status.time_posted

FROM status_updates AS status 

JOIN subscribers AS subscribe ON status.user_id=subscribe.subscriber_id 

INNER JOIN comments ON status.id=comments.status_id 

WHERE subscribe.user_id=000000000002 

ORDER BY status.time_posted DESC

 

It works, but I can't tell if it's actually pulling the right data.

 

It should be seeing the logged in user (in this case user # 000000000002), status updates for the people their subscribed to in the subscribed table, and then ALL comments that are related to those status updates (including people they're not subscribed to).

 

The tables are currently as follows:

 

subscribers

id | user_id             | subscriber_id      | subscribe_date
1 | 000000000001 | 0000000000002 | 
2 | 000000000001 | 0000000000003 | 
3 | 000000000001 | 0000000000001 | 
4 | 000000000002 | 0000000000003 | 
1 | 000000000004 | 0000000000001 | 

 

 

status_updates

id | user_id              | message                 | time_posted
1  | 000000000002  | Test Message One | 2012-06-13 00:22:37
2  | 000000000003  | Test Message Two | 2012-06-13 00:22:37

 

 

comment

id | user_id              | comment           | time_posted               | status_id
1  | 000000000001 | Comment 1        | 2012-06-13 00:23:43 | 1
2  | 000000000003 | Comment Two   | 2012-06-13 00:24:33 | 1
3  | 000000000001 | Comment Three | 2012-06-13 00:24:33 | 2
4  | 000000000001 | Comment Four   | 2012-06-13 00:24:57 | 2
5  | 000000000001 | Comment Five    | 2012-06-13 00:59:23 | 2

 

 

When I run the above query I end up with this as the result:

 

comment             | user_id             | subscriber_id    | user_id              | message                | time_posted
Comment Three  | 000000000002 | 000000000003 | 000000000002 | Test Message Two | 2012-06-13 00:22:37
Comment Four    | 000000000001 | 000000000003 | 000000000002 | Test Message Two | 2012-06-13 00:22:37
Comment 5         | 000000000004 | 000000000003 | 000000000002 | Test Message Two | 2012-06-13 00:22:37

 

 

I think what's messing me up, is; should the logged in user viewing the page be queried as the subscriber or the user in the subscriber table. Because technically the user logged in would be the one subscribed to the other user; so they should be the subscriber_id correct? So right now, it's backwards.

[HDFilmMaker2112]

Alright I modified what I had prior because I also need to pull the users name and url from the user_details table.

 

This query runs, but returns no results:

 

SELECT details.name, subscribe.subscriber_id, status.message, status.time_posted AS status_time
	FROM status_updates AS status JOIN subscribers AS subscribe ON status.user_id=subscribe.subscriber_id 
	INNER JOIN comments ON status.id=comments.status_id INNER JOIN user_details AS details ON details.user_id=status.user_id AND details.user_id=comments.user_id WHERE subscribe.user_id=000000000002 
	ORDER BY status.time_posted DESC

[HDFilmMaker2112]

I've seemed to have gotten something that works.

 

SELECT status.message
     , status.time_posted AS status_time
     , status_details.name AS status_name
     , commenter_details.name AS commenter_name
  FROM subscribers AS subscribe 
INNER
  JOIN status_updates AS status 
    ON status.user_id = subscribe.subscriber_id 
INNER 
  JOIN user_details AS status_details 
    ON status_details.user_id = status.user_id
INNER 
  JOIN comments 
    ON comments.status_id = status.id
INNER
  JOIN user_details AS commenter_details 
    ON commenter_details.user_id = comments.user_id
WHERE subscribe.subscriber_id=000000000003 
ORDER 
    BY status.time_posted DESC

[Psycho]

Well, if it is working - OK. but, that looks a lot more complicated than I would think it would need to be. The table structure you have definitely looks much better.

 

You stated previously that you wanted

It should be seeing the logged in user (in this case user # 000000000002), status updates for the people their subscribed to in the subscribed table, and then ALL comments that are related to those status updates (including people they're not subscribed to).

 

So, if I am reading that correctly, I would build out the query starting with the status updates. I don't know if this is more efficient or not, but give it a try if you want. I think it is more organized. FYI: Your query wasn't pulling the comments - which I thought you wanted.

SELECT status.message, status.time_posted AS status_time,
       status_details.name AS status_name
       comment.comment, comment.time_posted AS comment_time
       commenter_details.name AS commenter_name

FROM status_updates AS status                     -- Pull data from status table

JOIN user_details AS status_details               -- Join user table to status records
    ON status_details.user_id = status.user_id    -- to get user info of poster

JOIN comment AS comment                           -- Join comment table to status records
  ON comment.status_id = comment.id               -- to get associated comments

JOIN user_details AS commenter_details            -- Join user table to comment records
    ON commenter_details.user_id = status.user_id -- to get user info of commenter

LEFT JOIN subscribers AS subscribe                -- Conditionally JOIN subscriber table
    ON subscribe.user_id = status.user_id         -- to status table ONLY where the 
   AND subscribe.subscriber_id = 000000000003     -- the subscriber is the current user


WHERE status.user_id = 000000000003               -- Filter out all records except those where
   OR subscribe.subscriber_id = 000000000003      -- the status poster is the current user or
                                                  -- where the current user is a subscriber
ORDER BY status.time_posted DESC

[HDFilmMaker2112]

Well, if it is working - OK. but, that looks a lot more complicated than I would think it would need to be. The table structure you have definitely looks much better.

 

You stated previously that you wanted

It should be seeing the logged in user (in this case user # 000000000002), status updates for the people their subscribed to in the subscribed table, and then ALL comments that are related to those status updates (including people they're not subscribed to).

 

So, if I am reading that correctly, I would build out the query starting with the status updates. I don't know if this is more efficient or not, but give it a try if you want. I think it is more organized. FYI: Your query wasn't pulling the comments - which I thought you wanted.

SELECT status.message, status.time_posted AS status_time,
       status_details.name AS status_name
       comment.comment, comment.time_posted AS comment_time
       commenter_details.name AS commenter_name

FROM status_updates AS status                     -- Pull data from status table

JOIN user_details AS status_details               -- Join user table to status records
    ON status_details.user_id = status.user_id    -- to get user info of poster

JOIN comment AS comment                           -- Join comment table to status records
  ON comment.status_id = comment.id               -- to get associated comments

JOIN user_details AS commenter_details            -- Join user table to comment records
    ON commenter_details.user_id = status.user_id -- to get user info of commenter

LEFT JOIN subscribers AS subscribe                -- Conditionally JOIN subscriber table
    ON subscribe.user_id = status.user_id         -- to status table ONLY where the 
   AND subscribe.subscriber_id = 000000000003     -- the subscriber is the current user


WHERE status.user_id = 000000000003               -- Filter out all records except those where
   OR subscribe.subscriber_id = 000000000003      -- the status poster is the current user or
                                                  -- where the current user is a subscriber
ORDER BY status.time_posted DESC

 

That does look like a good option too.

 

 

Now one thing I'm curious about. Since I'm pulling all the comments together with the status_update. The rows returned all have the status update message attached to them. So how would I increment through the comments to show with the status update and only show the status update once.

 

Doing a simple while loop would cause me to have repeated status updates with no more than one comment each.

 

Should I just append [$i] to the specific $row variable and do the comments separately in a for loop? How would I prevent the duplicate status updates though?

[Psycho]

Now one thing I'm curious about. Since I'm pulling all the comments together with the status_update. The rows returned all have the status update message attached to them. So how would I increment through the comments to show with the status update and only show the status update once.

 

Doing a simple while loop would cause me to have repeated status updates with no more than one comment each.

 

Should I just append [$i] to the specific $row variable and do the comments separately in a for loop? How would I prevent the duplicate status updates though?

This is a basic process when dealing with retrieving associated records in a one-to-many configuration. The solution is to simply create a "flag" variable to detect a change in the root record within the while loop.

 

The flag variable needs to track a value that will be unique for that root record. In this case you could possibly rely upon the status message. But, that's a poor solution since you probably don't require the status updates to be unique. The possibility of two users having the same status update back to back is small, but why rely upon that when you can use a better value - the status update ID? So, you would need to add the status ID to the result set in the query.

 

NOTE: I just realized you will need a LEFT JOIN on the comment table since there will not always be a comment.

 

Example:

$current_status_id = false;
while($row = mysql_fetch_assoc($result))
{
    //Detect if this status is different than the last
    if($current_status_id != $row['status_id'])
    {
        //Set the new flag value to suppress output for
        //subsequent comments on the same status
        $current_status_id = $row['status_id'];
        //Display the status info for 1st record assoc. w/ the status
        echo "<br><b>{$row['status_name']} {$row['status_time']}</b><br>\n";
        echo "{$row['message']}<br>\n";
    }

    //Display the comments
    //Need a condition to cover scenario where no comment exists
    if($row['comment'] != '')
    {
        echo "<br><b>{$row['comment_name']} {$row['comment_time']}</b><br>\n";
        echo "{$row['comment']}<br>\n";
    }
}

[HDFilmMaker2112]

Now one thing I'm curious about. Since I'm pulling all the comments together with the status_update. The rows returned all have the status update message attached to them. So how would I increment through the comments to show with the status update and only show the status update once.

 

Doing a simple while loop would cause me to have repeated status updates with no more than one comment each.

 

Should I just append [$i] to the specific $row variable and do the comments separately in a for loop? How would I prevent the duplicate status updates though?

This is a basic process when dealing with retrieving associated records in a one-to-many configuration. The solution is to simply create a "flag" variable to detect a change in the root record within the while loop.

 

The flag variable needs to track a value that will be unique for that root record. In this case you could possibly rely upon the status message. But, that's a poor solution since you probably don't require the status updates to be unique. The possibility of two users having the same status update back to back is small, but why rely upon that when you can use a better value - the status update ID? So, you would need to add the status ID to the result set in the query.

 

NOTE: I just realized you will need a LEFT JOIN on the comment table since there will not always be a comment.

 

Example:

$current_status_id = false;
while($row = mysql_fetch_assoc($result))
{
    //Detect if this status is different than the last
    if($current_status_id != $row['status_id'])
    {
        //Set the new flag value to suppress output for
        //subsequent comments on the same status
        $current_status_id = $row['status_id'];
        //Display the status info for 1st record assoc. w/ the status
        echo "<br><b>{$row['status_name']} {$row['status_time']}</b><br>\n";
        echo "{$row['message']}<br>\n";
    }

    //Display the comments
    //Need a condition to cover scenario where no comment exists
    if($row['comment'] != '')
    {
        echo "<br><b>{$row['comment_name']} {$row['comment_time']}</b><br>\n";
        echo "{$row['comment']}<br>\n";
    }
}

 

That's pretty damn clever.

 

Your not based in or around New York are you? Cause I'd definitely hire you as one of the lead programmers once we get investment backing.

 

This is definitely the most complicated thing I've done to date.

 

Anyway, thank you for the help.

[Psycho]

That's pretty damn clever.

 

Your not based in or around New York are you? Cause I'd definitely hire you as one of the lead programmers once we get investment backing.

 

This is definitely the most complicated thing I've done to date.

That is pretty basic stuff that any capable developer should know how to do.