On 6/13/07, Daevid Vincent <daevid@xxxxxxxxxx> wrote:
I'll throw in my suggestion here as to how I implement this. Generally I evaluate how big are the tables and what do I intend to do with them. Sorting is usually a "problem". Using the PHP multi_sort seems cumbersome and never seems to be as simple as letting the DB do it. I too use LEFT JOINs frequently. If I have to join more than say 4 or 5 tables, I start to break it up. My favorite little trick is to load a PHP array with smaller tables. [Pseudocoded:] $employeeType = array( "SELECT id, name FROM employee_type_table" ); And I might do this for several tables. (you can also store these in a $_SESSION if you're using these key/values frequently on different pages) Then this can effectively eliminate one whole join (per), as most tables key off of ID's (duh). Then do my real SELECT/JOIN query, and during my while/$row loop I just substitute the array value back in like this <?= $employeeType[ $row['employee_type_id'] ] ?> I actually use this little optimizing trick as an interview question for new hires. You'd be amazed at how many people don't think of this, as obvious as it seems to me...
That's a neat trick, I'll try to keep it in mind. In our current setup we'd do something like: [pseudo] while($row) $employee = find::($row['employee_id']); // Return an Employee object echo $employee->getType()->getName(); So here, when we created an employee object, it did a db hit and looked in the employees table. The type attribute for employee was just a ProxyType object (with an id that was stored as type_id in the employees table). When I tried to access the name the ProxyType did a db hit and replaced itself with a Type object that had all the columns of the types table as its attributes. If we needed to later access some other attribute of the employee's type, no db hit would be made since it's now loaded. In this case, if you're looping over all employees and outputting their type names you'd be doing nearly twice as many db hits with my method. Thanks for the tip.
D.Vin -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
-- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
