Imagine you have a table of Persons, Busses, and a mapping table in between. You want to determine, given a set of people, who is on a given Bus. So you create a PHP page to return a simple list of Persons. You also want the page to be robust, so you want to return any error reflected from the DB or other failures. Also, PDO is used to make the site flexible.
To reduce database queries (assuming that querying the DB is the most expensive part, especially if the DB get big and/or lots of visitors), you probably want to reduce the queries to the DB to an absolute minimum.
A simple script would, after validating the data for types and other unintended values, probably look like such on the DB side:
//See if the Bus exists
SELECT id FROM Busses WHERE id={$bus_id};
//See if the given Persons exist
SELECT id FROM Persons WHERE id IN ({$comma-seperated-person-ids});
//Finally check the mappings table and discover who is on that bus.
SELECT person_id FROM Busses_Persons WHERE bus_id={$bus_id} person_id IN ({$comma-seperated-ids});
I think this order is important, because suppose a bus did not exist, this page would return success and show an empty list if the first query did not execute to determine existing busses. You would also return a successful page with no persons, if none of the persons existed and you hadn't performed the second query to determine which of your persons set exists, and which don't. Strictly, you should return an error page if any provided data is invalid or non-existant.
How can I get a list of Persons on that Bus, given a set of persons, and given a bus, AND find out if any given person or bus does or does not exist, and all that in a single query?
I suppose you can hack about really deeply (even though to maintain DB abstraction with PDO, you probably can't go too far either), or it becomes a question of DB execution time. But I was really wondering how other developers made their scripts faster and more efficient when stumbling upon the problem here?
