What mechanic can I use to order an array?

Question

I am stuck at a very weird problem. I mean I do not how to threat this.

Basically, I got a webpage where I list all stops for a defined bus route (example, route_id = 141). Sometime, a route, can start from point A and go to point B so theoretically from one terminus to another terminus. BUT, sometime, the same route can begin its trip somewhere between A and B and go at point A or B.

You can see a live working example at this address : http://stm.tranzit.ca/bus/stops/85/Hochelaga/

My super draw :

Regular trip
Start(A) ----------------------------------- End(B)

Sometime
                 Start --------------------- End(B)
Start(A) ---------------------- End

etc.. Its always going to be between point A and B. 90% of the time it's from A to B but the other 10% it can be anywhere between there until another point between there (sometime is from middle to A or B).

Ok. Before I was sorting my result using stop_sequence. I know the order of the stop sequence, so stop 1, stop 2, etc and how many stop by trip (a trip is from A to B, or A to middle, etc).

Now, I found a way to sort my result by trip and distinct them so now I know all the trip I got (A-B, B-A, A-middle, middle-B, etc..).

So if let's say I got A-B, B-A, A-Middle and B-Middle, I have 4 possibles trips for a route. I then fetch all stop linked to it.

Example, this is a list for trip A to B for a defined route. Please note the value at stop_sequence 35.

1   #53014  Station Frontenac (Frontenac / Ontario)

... 2, 3. etc..

34  #53293  Honoré-Beaugrand / Roux  
35  #54257  Station Honoré-Beaugrand / Sherbrooke    
36  #53290  Saint-Donat / Sherbrooke     
37  #53265  Saint-Donat / De Forbin-Janson   
38  #54676  Saint-Donat / De Grosbois    
39  #54674  Saint-Donat / Roi-René   
40  #54672  Saint-Donat / les Reaux  
41  #54668  Saint-Donat / Chénier    
42  #54661  Joseph-Renaud / Yves-Prévost     
43  #54646  Joseph-Renaud / Châtillon    
44  #54629  Joseph-Renaud / Wilfrid-Pelletier    
45  #54605  Joseph-Renaud / Châteauneuf  
46  #54609  Châteauneuf / Vaujours   
47  #54610  Châteauneuf / Rabelais   
48  #54612  Châteauneuf / de la Loire    
49  #54621  Châteauneuf / Roi-René   
50  #54623  Châteauneuf / des Ormeaux    
51  #54639  Châteauneuf / Rondeau    
52  #54724  Rondeau / Georges    
53  #54735  Rondeau / De La Vérendrye    
54  #54738  Rhéaume / Chaumont   
55  #54740  Chaumont / Guy   
56  #54741  Chaumont / Azilda    
57  #54742  Yves-Prévost / Azilda    
58  #54659  des Ormeaux / Chaumont

Here's a trip between middle and B.

1   #54257  Station Honoré-Beaugrand / Sherbrooke    
2   #53290  Saint-Donat / Sherbrooke     
3   #53265  Saint-Donat / De Forbin-Janson   
4   #54676  Saint-Donat / De Grosbois    
5   #54674  Saint-Donat / Roi-René   
6   #54672  Saint-Donat / les Reaux  
7   #54668  Saint-Donat / Chénier    
8   #54661  Joseph-Renaud / Yves-Prévost     
9   #54646  Joseph-Renaud / Châtillon    
10  #54629  Joseph-Renaud / Wilfrid-Pelletier    
11  #54605  Joseph-Renaud / Châteauneuf  
12  #54609  Châteauneuf / Vaujours   
13  #54610  Châteauneuf / Rabelais   
14  #54612  Châteauneuf / de la Loire    
15  #54621  Châteauneuf / Roi-René   
16  #54623  Châteauneuf / des Ormeaux    
17  #54639  Châteauneuf / Rondeau    
18  #54724  Rondeau / Georges    
19  #54735  Rondeau / De La Vérendrye    
20  #54738  Rhéaume / Chaumont   
21  #54740  Chaumont / Guy   
22  #54741  Chaumont / Azilda    
23  #54742  Yves-Prévost / Azilda    
24  #54659  des Ormeaux / Chaumont

Now, as you can see, stop_sequence 1 here is the same as 35. Second stop is the same as stop 36 above. As you can see also, the count from stop 35 to 58 is 24. The same as the second trip.

Now, what I want to know, is how I can merge them using PHP ? I would like to take let's say second group here and merge it inside the first one at the first occurence that match, so stop_sequence 1 from second group will become stop_sequence 35 because it match and it is the same.

Using this way, I can keep the stop order, not by using stop_sequence at the end of ordering, because as you saw, the second trip has stop_sequence 1 equal to 24 on the first trip. It mean that if I order by stop_sequence using MySQL, it will order sequentially all stop_sequence so the resulst will be stop_sequence 1 from first trip, sequence 1 from second trip, etc and my order will be wrong of all stops will be cleary wrong.

I want to do this in PHP but I do not know how to match result in an array and avoid X result after so I can match perfectly.

If you want to know what is my SQL query, here it is :

SELECT      t.trip_id, t.trip_headsign, st.stop_sequence, s.stop_id, s.stop_code, s.stop_name
FROM        trips AS t
LEFT JOIN   stop_times AS st ON st.trip_id = t.trip_id
LEFT JOIN   stops AS s ON s.stop_id = st.stop_id
WHERE       t.route_id = 141
    AND     t.trip_id IN (
        SELECT trip_id
        FROM (
            SELECT trip_id
            FROM (
                SELECT      COUNT(*) AS count, trips.trip_id, trips.trip_headsign
                FROM        trips
                LEFT JOIN   stop_times ON trips.trip_id = stop_times.trip_id
                WHERE       route_id = 141
                    AND     trips.trip_id LIKE (SELECT CONCAT(service_id, "%") FROM calendar_dates WHERE date = "20120628")
                GROUP BY    trips.trip_id
            ) a
            GROUP BY count, trip_headsign
        ) a
    )
GROUP BY    t.trip_id, st.stop_id
ORDER BY    t.trip_id ASC, st.stop_sequence ASC

trip_id define if it's from A-B, B-A, A-middle, etc.

stop_sequence is the order of the stop for a defined trip

stop_id and stop_code share the same thing - unique id (one for user and one for the internal system)

trip_headsign is like 141-W or 141-N (as for west or north).!

EDIT:

With my query, I can get all stop for each trip (green and red here). I want to merge this in one list by keeping the order. example schema

Route can be from stop 1 (red) to stop 8. So trip 1 can be stop 1, stop 2, etc until stop 8.

Route can be from stop 1 (green) to stop 8. So trip can be stop 1, stop 7 and stop 8.

My query give me both trip stop's list.

Now, I want to merge them as follow :

List: - Stop 1 (red) - Stop 2 - Stop 3 .... - Stop 6 - Stop 1 (green) - Stop 7 (once even if he is in trip red and green) - Stop 8 (once even if he is in trip red and green)

Answer 1

To summarize what has been said in the comments:

1. Find the authoritative trip by asserting the first stop is A and the last stop is B
2. For each other trip:
   1. if the first stop is A, it will be left / top aligned to the main trip
   2. if the last stop is B, it will be right / bottom aligned to the main trip

Optionally, you can start on either side of the shorter trip and remove any stops that don't appear in the main trip.