Handling Multiple Places ID trick

Question

Currently I have 3 tables countries, routes and route_place

countries

id
name

table routes

id
title

table route_place

id
parent_id (id from table routes)
from (id from countries)
to (id from countries)
place_name

I have 3 destination China - Dubai - London from countries table, currently I save the data into route_place table like this:

China (from column) - Dubai (to column)
Dubai (from column) - London (to column)

I wanted to save the data into the route_place like this

China (from column) - Dubai (to column)
China (from column) - London (to column)
Dubai (from column) - London (to column)

I couldnt figured the logic after an hour, is there any method or some guidelines for it

for instance, I submitted China - Dubai - London routes, and manually choose routes place by sequence

China - Dubai
Dubai - London

what I wanna do is it will automatically create another route from China to London without needing me to insert manually, because China to is one way trip to London too by sequence.

You're looking for a pivot table. Have a look [here](https://stackoverflow.com/questions/7674786/mysql-pivot-table). — Andrei, Mar 08 '19 at 08:14
can you exlpain more , this is not totaly clair , where the data is comming ? do you want only a php script that insert into the table route_place static information ? — Yassine CHABLI, Mar 08 '19 at 08:52
I have edited the question, did I make myself clear? @MohammedYassineCHABLI — Daniel, Mar 08 '19 at 09:22

score 0 · Answer 1 · answered Mar 08 '19 at 09:55

Since you only need the logic, here is my solution when using PHP. Let say that you query all the distinct destination on route_place table (route_place->to) and insert it into $destination variable.

foreach($destination as $destination) {
    foreach($route_place as $route_place) {
        if ($route_place->from == $destination->to and $route_place->id != $destination->id) {
            //save this route to database
        }
    }
}

score 0 · Accepted Answer · answered Mar 11 '19 at 10:19

0

this should do the trick

$array = array('China','Dubai','London');

$array_length = count($array);

for ($i = 0; $i < $array_length - 1; $i++){

  for($j = $i+1; $j < $array_length; $j++){

    echo $array[$i] . " to " . $array[$j] .'<br>';

  }

}

answered Mar 11 '19 at 10:19

Daniel

155
1
2
15

Handling Multiple Places ID trick

2 Answers2