Order Using Multiple Rows acting as one

Question

I have an SQL query

$choice = mysqli_real_escape_string($con,$_POST['choice']);
    
    if($option == "Name")
    $searchresult = "SELECT  id, stationname1, stationinfo1, stationprice1, image1, stationname2, stationinfo2, 
    stationprice2, image2, stationname3, stationinfo3, stationprice3, image3, stationname4, stationinfo4, stationprice4, 
    image4 FROM fuel WHERE stationlocation1 LIKE '%" . $location .  "%' ORDER BY stationname1 $choice"; //search database

which gets the data and arranges it by station name 1. The problem is that I have stationnames 1 - 4 which i would like to order together so that all names are in alphabetical order no matter the column. I know that using

ORDER BY stationname1 ASC, stationname2 ASC, stationname3 ASC, stationname4 ASC

won't work as it orders them individually. Please what do I do to order them together?

Basically something in the form of

ORDER BY stationname1 and stationname2 and stationname3 and stationname4 ASC

for example...looking at this database pic , i would like it to show:

• another_one
• nnnn
• ordinarytest
• zgkgkgkkg

in that order

Answer 1

As others have said, you really should normalize your data so you don't effectively have 4 rows' data per actual row; but this should get you what you want (and may be a path to normalizing your data):

SELECT  id, stationname1 AS stationname, stationinfo1, stationprice1, image1
FROM fuel 
WHERE stationlocation1 LIKE '%" . $location .  "%' 
UNION
SELECT  id, stationname2 AS stationname, stationinfo2, stationprice2, image2
FROM fuel 
WHERE stationlocation2 LIKE '%" . $location .  "%' 
UNION
SELECT  id, stationname3 AS stationname, stationinfo3, stationprice3, image3
FROM fuel 
WHERE stationlocation3 LIKE '%" . $location .  "%' 
UNION
SELECT  id, stationname4 AS stationname, stationinfo4, stationprice4, image4
FROM fuel 
WHERE stationlocation4 LIKE '%" . $location .  "%' 
ORDER BY stationname $choice;

The final ORDER BY clause of a UNION applies to the entire UNION unless parenthesis force it to be otherwise.