The connections table is required.
Start station and end station are the stations which the journey begins and ends. In the example of paris to dijon, no journeys begin or end at both of these stations.
I really don't see how i could provide more information that i already have... I might just have to do this programmatically rather than via SQL. Shame, but nevermind. Will get the job done.
hi, as I understand (may be not correctly) you need something like this
select table1.journeyID ..... from table1 where table1.journeyID IN (select table2.stationID from table2)that will select all records from table1 (in this case journeys )where journeyID will be = to table2 (in this case connections) stationID.
If you need more help ask me :)
regards
Thanks for getting back to me. Im not sure that will do it, my fault for a poor explanation.
Here is some example data;
table1 = journey
id, startstation, endstation, distance.
1, 1, 5, 800.
2, 2, 6, 400.
this following table contains all of the connecting points, or stations, along the journey or route (e.g. journeyid1 stops at stations 1-5.
table2 = connections
connID, journeyID, StationID.
1, 1, 1.
2, 1, 2.
3, 1, 3.
4, 1, 4.
5, 1, 5.
6, 2, 2.
6, 2, 6.
following table contains station data.
table3 = stations
id, name, address.
1, Euro-Lille, 09939
2, Gare du Paris, 84929
3, Lyon, 24802
4, Dijon, 94024
5, Marseille, 37800.
the query i need requires the Journey id, for journeys which connect at both the stations the traveller has specified.
eg. the traveller wishes to go from 2, paris to 4, dijon, which journey connects at both of these stations?
I have 2 tables,
1 called journeys, which contains the journeyID, the start station, end station, time to complete, and the distance. The second table is called connections and contains all the stations the train stops at along the journey. I need to write a query which extracts the correct journey based on 2 connection points/stationID's...
Anyone have any idea how to do this?
I want journey ID based on station ID's in connection point table where both connections are present in the connection point for that journey.
Thanks, definitely helps to talk these problems through. I will post back here and let you know the methods i went with in the end...
If you have any more ideas in the mean time, let me know!
I don't think PHP is the appropriate language for this. This project might be a bit beyond it's capabilities. The best I could do with PHP is maybe write some JavaScript to use the Google Maps API. Why re-invent the wheel, when Google has perfected it. I'm sure with the API you can calculate trip distances and times (but I'm not sure).
Although google does provide good distance calculation, it is based on walking or driving, and public transport is only included as beta in a select few cities.
What i am trying to determine is far simpler, but perhaps the way i put it wasn't so clear...
Anways, To simplify, lets consider this scenario of traveling by train, you wish to travel by train from point A to point B. London to Milan, for example. but to complete this journey, you will have to change trains in Paris. How does this simple process work? Would connection points be stored in a database so that every request that goes from london to milan has to go via paris (example 1), or would you have a routes logged in the database, and the code will determine the links on the fly (example 2) Or does it work some other way...
Example 1 - The database has a table called Journeys which stores all possible combinations of start points and destinations. So there would be a record linking start point of London, to destination Milan called journey 1. This …
Not sure if this is the right forum - but i will be coding the solution in PHP though (i think) if thats worth anything...
Im doing a bit of work surrounding the area of journey planning, and im just wondering, how the heck do they do it?
Say for example, you want to go from a residential address in London to a residential address in Paris using public transport... How would this work...
My initial ideas are that a range of transport providers provide data over web services, and the "portal" somehow collates all the available connection points and offers various routes, or the portal suggests the quickest, shortest, cheapest...etc...
I just cant grasp how code can map a path through these connection points is it just a case of having all the data and mathametically determining the distances (possibly for shortest route), but then you need information such as average speed of the vehicle you are travelling on to determine time... etc...etc..
Im just hoping someone out there has a clearer idea than i do! Does anyone have any ideas?
You need to do an analysis of the data you require for these tasks, for example:
2 main types of functionality you require -
1. Book seat in restaurant
2. Order take away.
Question: What data is required (by each functionality)?
1. Book-ee's Details - Name, possibily phone no, email address might be nice to send a confirmation to...etc...
Restaurant bookings - Current amount of places in the restaurant for a specific date (might need to know table sizes as doing it by heads might not be possible).
2. Person details - Name, address, phone number - for delivery. Possibly again, an email address to send confirmation or promotional info in the future.
Order Details - Needs to have a listing of menu items ordered.
Menu Details - Might have menu items categorised by starter, main, desert (maybe, i dont know). The item name and description and a price.
This is a very simplified analysis, and i dont know the exacting requirements of your project. But this is the sort of process i would go through if i wasn't aware of UML to identify the requirements of the back end. You need to make sure you follow good practices in db design such as, no many to many relationships and foreign keys etc...
Once your DB is designed, you can offer the restaurant a couple of simple screens to allow the update of this data, which as far as …
I will look into this as a solution, thanks TomW. Got any good resources i can look at re: module files?
Unfortunately, as soon as i make the sub "shared" it stops recognising all the variables and components on the form it relates to?
Any way around this? or any other ideas?
Hi guys,
Im having trouble with this code, when i had it working with static variables set, it was fine, now i am using a passed variable, its not working... Might not be anything to do with it, anyhow...
Public Class frmContacts
Dim con As New OleDb.OleDbConnection
Dim ds As New DataSet
Dim da As OleDb.OleDbDataAdapter
Dim sql As String
Dim projectid As Integer
Private Sub frmContacts_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Call reload()
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
End Sub
Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
End Sub
Public Sub clear()
ds.Tables("contacts").Clear()
End Sub
Public Sub reload()
con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\EZaudit.mdb"
con.Open()
sql = "SELECT id As pk, contacts.p_id As id, contacts.con_name as Name, contacts.con_position AS JobTitle, contacts.con_email AS Email, contacts.con_phone AS PhoneNo FROM contacts WHERE contacts.p_id=" & projectid & ""
da = New OleDb.OleDbDataAdapter(sql, con)
da.Fill(ds, "contacts")
con.Close()
dgCon.DataSource = ds.Tables("contacts")
dgCon.Visible = True
dgCon.Columns("id").Visible = False
dgCon.Columns("pk").Visible = False
dgCon.Columns("Name").Width = 100
dgCon.Columns("JobTitle").Width = 100
dgCon.Columns("Email").Width = 145
dgCon.Columns("PhoneNo").Width = 120
End Sub
Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click
Dim entry As New frmConEntry()
entry.ProjectId3 = projectid
entry.Show()
End Sub
Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
Dim cb As New OleDb.OleDbCommandBuilder(da)
da.Update(ds, "contacts")
MsgBox("Records Updated")
End Sub
Public Property ProjectId5() As String
Get
Return projectid
End Get
Set(ByVal Value As String)
projectid …