Using your existing data from your two tables, you could LEFT JOIN columns source and destination from Routes meaning your INT values will match up to the Location.name column. Then make sure you order the data by source column for so it's easier to sort with PHP later on.
MySQL:
SELECT locStart.name as start, locFinish.name as finish FROM `routes`
LEFT JOIN location as locStart
ON routes.source = locStart.id
LEFT JOIN location as locFinish
ON routes.destination = locFinish.id
ORDER BY routes.source
Output:
array (
0 =>
array (
'start' => ' bangalore',
'finish' => 'delhi',
),
1 =>
array (
'start' => ' bangalore',
'finish' => 'mumbai',
),
2 =>
array (
'start' => ' bangalore',
'finish' => 'kolkatta',
),
//Etc....
Then add your DB results into multidimensional array using the Source name as the key.
$stmt = $pdo->query($query);
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
$newArr;
foreach ($result as $v) {
$newArr[$v['start']][] = $v['finish'];
}
Output:
array (
' bangalore' =>
array (
0 => 'delhi',
1 => 'mumbai',
2 => 'kolkatta',
),
'mumbai' =>
array (
0 => 'delhi',
1 => 'kolkatta',
),
'kolkatta' =>
array (
0 => 'delhi',
),
)
For outputting the new array, you can use a recursive function:
function recurse($newArr, $key=NULL) {
if($key !== NULL) {
foreach ($newArr[$key] as $k=>$v) {
echo '<li>Destination: ' . $v . '</li>';
}
echo '</ul>';
} else {
foreach ($newArr as $k=>$v) {
echo 'Source: ' . $k . '<br><ul>';
recurse($newArr, $k);
}
}
}
Output:
Source: bangalore
- Destination: delhi
- Destination: mumbai
- Destination: kolkatta
Source: mumbai
- Destination: delhi
- Destination: kolkatta
Source: kolkatta
