I am stuck in a very strange problem. I mean, I don’t know how to threaten this.
Basically, I have a web page where I list all the stops for a specific bus route (example, route_id = 141). Once upon a time, a route can start from point A and go to point B so theoretically from one end to the other end point. BUT, someday, the same route can begin its journey somewhere between A and B and go to 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)
Sometime
Start
Start(A)
etc. It will always be between points A and B. 90% of the time from A to B, and the remaining 10% can be somewhere between them to another point between them (sometimes from the middle to A or B).
Ok. , stop_sequence. , 1, 2 .. ( , ..).
, , (A-B, B-A, A-middle, middle-B ..).
, , A-B, B-A, A-Middle B-Middle, 4 . , .
, A B . stop_sequence 35.
1
... 2, 3. etc..
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
B.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
, , stop_sequence 1 35. , 36 . , 35 58 24. , .
, , PHP? let say , stop_sequence 1 stop_sequence 35, , .
, -, stop_sequence , , , stop_sequence 1, 24 . , stop_sequence MySQL, stop_sequence, resulst stop_sequence 1 , 1 .., , .
PHP, , X , .
, SQL-, :
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 , A-B, B-A, A-middle ..
stop_sequence -
stop_id stop_code - ( )
trip_headsign 141-W 141-N ( , ).!
, ( ).
, .
1 () 8. , 1 1, 2 .. 8.
1 () 8. , 1, 7 8.
.
:
:
- 1 ()
- 2
- 3
....
- 6
- 1 ()
- 7 ( , )
- 8 ( , )