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!