Need Help Deciding Best Practicce


#1

Howdy,

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.


#2

I would do it like this…

[php] 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 <> c.itin_s AND i.id <> c.itin_e
WHERE c.date > $old_date
ORDER by c.date, c.ship[/php]

This is because I’m taking a guess here, that the start and end numbers might sometimes be out of order? I’m not sure it’s your data… But this will safeguard against that.

Also make sure you have an index on c.ship, i.ship, c.itin_s, c.itin_e

You should run the query and look at the explain plan and adjust as needed.

http://dev.mysql.com/doc/refman/5.5/en/execution-plan-information.html