Re: SQL Hell

Guess some guys don't get the point of this posting or have no idea what foreign keys are about. This problem can easily be solved using JOINs -- red Lester Caine wrote:

Jason Barnett wrote:

So far I've made multiple statements pulling out all the data and using PHP to formulate the information. But I know there is a better way using mysql. Only thing is I haven't got the faintest idea how to do it in one query and keep overheads low. I start to get lost when I'm doing select statements within select statements.

I'm no DB guru, but as I understand it what you want to do requires the use of foreign keys. InnoDB tables implement them, but I don't think they currently grab the related information when you do SELECT statements. It sounds like you are emulating that right now with your SQL statements, and I don't think that any of the 4.x versions of MySQL will fully support foreign keys. I did find something interesting, though: apparently MySQL is going to support *stored_procedures* and *foreign_keys* in the 5.0-5.1 branch. I may have to go play with some of the 5.0 alpha stuff soon...

Alternatively, use a better database :) Firebird has had Triggers and Stored Procedures from the start. Using these with PHP gives the power needed to update cross references without having to build complex PHP code. http://www.firebirdsql.org/