I have a problem looping through my tableLegs for a bus timetable to find the array of legs legID that the passenger would need to take to get from a to b, take for example the passenger want to start at LocID of c and travel to LocID of p, I would like the array of legs that make up that trip LegID's (01, 02, 04), hope that is easy enough to follow.
tableLegs
=============================================
LegID startLocID endLocID departTime
01 a e 9:00
02 e i 10:00
03 i m 11:00
04 i q 11:00
tableLegStops
=========================================
LegID stopNo LocID TripTime
01 1 a 0 min
01 2 b 20 min
01 3 c 30 min
01 4 d 40 min
01 5 e 60 min
02 1 e 0 min
02 2 f 20 min
02 3 g 30 min
02 4 h 40 min
02 5 i 60 min
03 1 i 0 min
03 2 j 20 min
03 3 k 30 min
03 4 l 40 min
03 5 m 60 min
04 1 i 0 min
04 2 n 20 min
04 3 o 30 min
04 4 p 40 min
04 5 q 60 min
To give you a basis for what I am trying to achieve here, it's for a bus website where each trip is made up of different legs.
This task is known as searching for a shortest path in a graph. There exists an implementation of Dijkstra algorithm for solving this in terms of MySQL stored procedure.
But this would probably require from you to change your table structure a bit, because the graph uses only two types of entities: nodes and edges. So you should decompose your legs into edges, and then deduce the legs back after you get the shortest path in edges.
You can use the the A* search algorithm for this. Se http://en.wikipedia.org/wiki/A_star