SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Jan 2001
    0 Post(s)
    0 Thread(s)

    I am trying to setup a basic public transport journey planner and have encountered some problems.

    I have got the system to allow me to plan journeys based on a departure time from one point on one route to another point on the same route.
    But if I want a change from one route to another I encounter a problem. If I ask for a journey from station1 to station2 via station3 and station4. (Station3 and Station4 are the same thing. They are the change station. station3 being on Line1 and Station4 being on Line 2), The problem is that results would show that I could leave Station 1 at 10:00 arrive at Station3 (ie change Station) at 10:15, and leave Station4 (ie Change station on other line) at 10:20 and get to station4 at 10:30.
    But then results continue to show that I could arrive at the change station at 10:15 and leave on the 10:20, 10:30, 10:40, etc...

    Although my SQL below does allow for this, how do I limit it so it will only output the next departure on my second leg of the journey.

    The SQL statement I am using is:

    select Line1.Name, Station1.Name, Train1.Time, Station3.Name, Train2.Time, Destination1.Name, Line2.Name, Station4.Name, Train3.Time, Station2.Name, Train4.Time, Destination2.Name

    From Station AS Station1, Station AS Station2, Station AS Station3, Station AS Station4, Station AS Destination1, Station AS Destination2, Line As Line1, Line As Line2, Train AS Train1, Train AS Train2, Train AS Train3, Train AS Train4

    WHERE Station1.Name="Goodge Street"
    AND Station2.Name="Earls Court"
    AND Train1.Time BETWEEN "10:09" AND "10:22"
    AND Line1.Line_ID=Station1.Line_ID
    AND Line2.Line_ID=Station2.Line_ID
    AND Line1.Line_ID=Station3.Line_ID
    AND Line2.Line_ID=Station4.Line_ID
    AND Station3.Name=Station4.Name
    AND (Station1.Station_ID=Train1.Station_ID
    AND Station3.Station_ID=Train2.Station_ID
    AND Train1.Train_ID=Train2.Train_ID
    AND Train1.Destination_ID=Destination1.Station_ID)
    AND Station4.Station_ID=Train3.Station_ID
    AND Station2.Station_ID=Train4.Station_ID
    AND Train3.Train_ID=Train4.Train_ID
    AND Train3.Destination_ID=Destination2.Station_ID
    AND Train2.Time < Train3.Time
    ORDER BY Train1.Time

    I would be grateful for any help anyone can offer.


  2. #2
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    San Diego, CA
    0 Post(s)
    0 Thread(s)
    Add a LIMIT to the end of your sql statement, I think....
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  3. #3
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Melbourne, Australia
    2 Post(s)
    0 Thread(s)
    Kevin Yank
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts