Question 40832 in PHP Question posted in
Official manual can be found here.

Sort the results of the first table by the id of the second – PHP

I’m sorting some users by id (oldest to newest).

This is my accounts table:

Accounts

id name
1 James
2 Kirk
3 Roberto
4 Lars

However, I need to improve this ordering by relating a second messages table.

Accounts_Messages

id sender_id receiver_id
1 1 4
2 1 2
3 1 3

In this case, users in Accounts table should be ordered by last messages.
This is the expected result:

Roberto;
Kirk;
Lars;

The question is: How can I sort the results of the first table by the id of the second?

I read that I need to use `JOIN` to relate these two tables, but I didn’t know how to apply it in this specific case.

Thank you, guys!

Tags:

2

Answers


  1. 0

    Hey use this MySQL query for your aspected result

    Select Accounts.name from Accounts_Messages left join Accounts on Accounts.id = Accounts_Messages.receiver_id order by Accounts_Messages.id DESC
    Login or Signup to reply.
  2. 0

    this is what I understand by your question you need to sort the accounts by the id of the accounts_Message

    I think you should try

    SELECT acc.* FROM Accounts_Message am LEFT OUTER JOIN Accounts acc USING(id) ORDER BY id ASC

    if both Accounts and Accounts_Message has same column name id use USING(id) or the column name is different use ON acc.id = am.id instead

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search