I need help deciding the best practice/efficiency on how to present data from tow tables, and when to make the calls.
Table “cruises” references table “itineraries” to present the itinerary of the cruise. On the summary page, I would like to present the ports of call for the cruise, stripped of the starting and ending port and the non ports:
table cruises uses:
itin_s and itin_e for referencing the itinerary table row numbers (i.e. itin_s would be ‘1’, and itin_e would be ‘7’).
itin table:(id, ship, day, poc, arrive, depart)
1 1 Mon Venice - 5PM
2 1 Tue Cruising the mediteranean - -
3 1 Wed Rome 8AM 5PM
7 1 Sun Barcelona 8AM -
The summary of a cruise would include the ports of call Rome, etc between the starting and ending point, and stripped of things like “cruising this or that”.
I have the scripting to make the changes, just not sure how to go about accessing the database. Do I load all the port data into an array, then pull from that array? Seems easiest, but there is a potential for a thousand members of the array. I think I have the join logic worked out, but am not particularly good:
Select c.date, c.days, c.ship, c.atob, c.itin_s, c.itin_e FROM cruises c INNER JOIN itineraries AS i ON c.ship = i.ship AND i.id BETWEEN c.itin_s AND c.itin_e WHERE c.date > $old_date ORDER by c.date, c.ship
Am I on the right track? Can’t seem to get what I want.