How to temporarily save the result of the query, to use in another?

Question

I have this problem I think you may help me with.
P.S. I'm not sure how to call this, so if anyone finds a more appropriate title, please do edit.

Background

• I'm making this application for searching bus transit lines.
• Bus lines are a 3 digit number, and is unique and will never change.
• The requirement is to be able to search for lines from stop A to stop B.
• The user interface is already successful in hinting the user to only use valid stop names.
• The requirement is to be able to display if a route has a direct line, and if not, display a 2-line and even 3-line combination.

Example:

I need to get from point A to point D. The program should show:

• If there's a direct line A-D.
• If not, display alternative, 2 line combos, such as A-C, C-D.
• If there aren't any 2-line combos, search for 3-line combos: A-B, B-C, C-D.

Of course, the app should display bus line numbers, as well as when to switch buses.

What I have:

My database is structured as follows (simplified, actual database includes locations and times and whatnot):

+-----------+
| bus_stops |
+----+------+
| id | name |
+----+------+

+-------------------------------+
|    lines_stops_relationship   |
+-------------+---------+-------+
|  bus_line   | stop_id | order |
+-------------+---------+-------+

Where lines_stops_relationship describe a many-to-many relationship between the bus lines and the stops.

Order, signifies the order in which stops appear in a single line. Not all lines go back and forth, and order has meaning (point A with order 2 comes after point B with order 1).

The Problem

• We find out if a line can pass through the route easily enough. Just search for a single line which passes through both points in the correct order.
• How can I find if there's a 2/3 line combo? I was thinking to search for a line which matches the source stop, and one for the destination stop, and see if I can get a common stop between them, where the user can switch buses. How do I remember that stop?
• 3 line combo is even trickier, I find a line for the source, and a line for the destination, and then what? Search for a line which has 2 stops I guess, but again, How do I remember the stops?

tl;dr

How do I remember results from a query to be able to use it again? I'm hoping to achieve this in a single query (for each, a query for 1-line routes, a query for 2, and a query for 3-line combos).

Note: I don't mind if someone suggests a completely different approach than what I have, I'm open to any solutions.

Will award any assistance with a cookie and an upvote. Thanks in advance!

Answer 1

You may not want to make this drastic of a change at this point, but what you describe is exactly the use case for Graph Databases. Graph Databases are based on graph theory, which is what you are touching upon by trying to find a path between 'X' and 'Y' across a directed graph of bus routes.

If you haven't already looked into one, take a peek at something like Neo4J. It has a REST API and you can find PHP clients for it.

You'll find a bunch of people of Stack Overflow who could help with the implementation side of things.

Answer 2

Let's say a user wants to go from $start_id to $end_id (both are valid stop_id values). You may use these queries to find a valid route from $start_id to $end_id:

1. Search for direct route (single line):

   SELECT *
   FROM bus_stops bs1, bus_stops bs2
   WHERE bs1.stop_id=$start_id AND bs2.stop_id=$end_id AND bs1.bus_line=bs2.bus_line
   

2. If there's no result with the previous query, then search for a route using 2 lignes:

   SELECT *
   FROM bus_stops bs1, bus_stops bs2, bus_stops bs3, bus_stops bs4
   WHERE bs1.stop_id=$start_id
       AND bs1.bus_line=bs2.bus_line
   AND bs2.stop_id=bs3.stop_id
       AND bs3.bus_line=bs4.bus_line
   AND bs4.stop_id=$end_id
   

Replace * with the fields you really need to retrieve.