duanjunjie0196
duanjunjie0196
2016-10-23 21:26

使用mySQL JOIN排序问题

已采纳

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!

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

1条回答

  • douyinliu8813 douyinliu8813 5年前

    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.';
    }
    
    点赞 评论 复制链接分享

相关推荐