使用mySQL JOIN排序问题

Question

For a few days now, I can't find a solution for this one issue regarding sorting the data from a joined table.

Say I'm making restaurant itineraries for users. Each user has a few restaurants in his itinerary.

I have two tables, one that contains the information regarding a user's itinerary (when will he be visiting restaurants, which restaurants), and one containing the information about all the restaurants I know.

guides ...................and.............. restaurants

userid               --->   id
restaurant1_id -->--|       name
date1               |       style
restaurant2_id -->--|       address
date2

The "restaurant1_id" or "restaurant2_id" corresponds with the "id" in restaurants table. So I'm inner joining these tables like this:

SELECT restaurants.name AS name, restaurants.style AS style, restaurants.address AS address, guides.date1 AS date1, guides.date2 AS date2
FROM `guides`
INNER JOIN `restaurants` 
    ON guides.restaurant1_id = restaurants.id or guides.restaurant2_id = restaurants.id 
WHERE guides.userid = 2

Say I'd like to see all of the info of all of the restaurants a user with ID "2" will be visiting. With the following query, I'm getting the desired result:

$result = $conn->query($sql);
  if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {   
      echo 
        $row['name'].'<br>'
        .$row['style'].'<br>'    
        .$row['address'].'<hr>'    ;
      }
   }
else {
   echo 'No results.';
   }

I created a fiddle here.

Where I'm having trouble, is sorting the results. I'd like the restaurant that is referred to in the "guides" table as "restaurant1_id" to show up before "restaurant2_id".

Another issue is the date. I can't figure out how to show 'date1' next to the name of 'restaurant1_id'.

Should I store all the data in variables or is there a better way to approach this? I appreciate any suggestions!

Answer 1

Try query

SELECT IF(guides.restaurant1_id = restaurants.id, 1, 0) as srt ,
  restaurants.name AS name, restaurants.style AS style,
  restaurants.address AS address, guides.date1 AS date1, guides.date2 AS date2
  FROM `guides`
INNER JOIN `restaurants` ON guides.restaurant1_id = restaurants.id or guides.restaurant2_id = restaurants.id 
WHERE guides.userid = 2
order by srt DESC

Show results:

$result = $conn->query($sql);
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {   
  echo 
    $row['name'].'<br>'
    .($row['srt']?$row['date1']:$row['date2']).'<br>'
    .$row['style'].'<br>'    
    .$row['address'].'<hr>'    ;
  }
}
else {
  echo 'No results.';
}