-2

I have 2 columns in my database. One is named "name01" and the other is named "name02". This database displays friends on my app. When I go to my friends list on the app I want all my friends displayed by name in asc order. The problem is users names could be in "name01" or "name02" depending on who sent the friend request first. How would you "order by" both name01 & name02 at the same time? I have tried doing it with a comma (ORDER BY name01, name02) but it orders one first and then the other so the names aren't all in alphabetical order.

 $sql = mysql_query("SELECT * FROM friends WHERE id01 = '$id' AND status01 = '1' OR id02 = '$id' AND status02 = '1' **ORDER BY name01, name02** Limit 500");

Above will order name01 separately from name02.

Devsi Odedra
  • 5,244
  • 1
  • 23
  • 37
BTR2834
  • 3
  • 1
  • You could [CONCAT](https://www.w3schools.com/sql/func_mysql_concat.asp) the two names together and order by that value. Though that would potentially be really slow. – Cully Nov 26 '20 at 06:41
  • When username is in one of the fields, what does the other contain? Is it `NULL` or something else? – El_Vanja Nov 26 '20 at 08:01
  • 3
    Also, please note that the way you're building your query is unsafe. You're open to [SQL injection](https://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work). You should use [prepared statements](https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php) instead. – El_Vanja Nov 26 '20 at 08:02
  • 1
    **Warning:** `mysql_*` extension is deprecated as of PHP 5.5.0, and has been removed as of PHP 7.0.0. Instead, either the [mysqli](https://www.php.net/manual/en/book.mysqli.php) or [PDO_MySQL](https://www.php.net/manual/en/book.pdo.php) extension should be used. See also the [MySQL API Overview](https://www.php.net/manual/en/mysqlinfo.api.choosing.php) for further help while choosing a MySQL API. – Dharman Nov 26 '20 at 09:45

1 Answers1

0

Use the GREATEST() function and then ordering:

SELECT friends.*, GREATEST(IFNULL(name01, 0), IFNULL(name02, 0)) as greatest_name 
FROM friends 
WHERE id01 = '$id' AND status01 = '1' OR id02 = '$id' AND status02 = '1'
ORDER BY greatest_name Limit 500
Valeri
  • 327
  • 1
  • 5
  • 15