Page 1 of 1
many database queries
Posted: Tue Oct 11, 2005 1:12 pm
by deltawing
I wasn't quite sure whether this goes better in the db forum or this one, but I chose this one because its mainly about object structure.
I have a few pages which will have to make quite a few database queries each time they're run. Do you think its worth sacrificing readability to combine the queries (when they actually relate to different objects) as far as possible, and how would I go about this? There would have to be some way of telling the database connection object exactly what information I'll need with one set of methods, then using another set of methods to get that information from the results, I think. Do I have the right idea, or am I just making things difficult for myself? I don't have much experience with databases (I've not even been working with PHP too long, and I've been learning as fast as I can), but I'm working with a database with 16 tables, so its quite a challenge.
Posted: Tue Oct 11, 2005 1:52 pm
by pickle
In my opinion, the more data you can get from a single database query, the better. Inline comments and adequate variable naming should be able to get you around any readability issues.
Posted: Tue Oct 11, 2005 2:18 pm
by deltawing
The other option I've thought of is I could just get all data which is fairly likely to be needed at once. This would make the object structure much more obvious, as one object passes one variable to the database access object, which then gets some results, formats them into relevent variables, then these variables can be accessed if required. But of course, some of them may not be required, which would mean that they had been retrieved for nothing, but on the plus side, it all would have been done in one or two queries. What do you think?
Posted: Tue Oct 11, 2005 2:54 pm
by J_Iceman05
Something you could do, is use variables in your queries.
I have done this with something of mine, then you actually only write one statement, and if some of the data may not always be needed, then it is not pulled
example:
Code: Select all
if ($your_variable == $your_other_vaiable) {
// set vaiables
$extra_info = ", Country, MotherName, FatherName, MotherMaidenName";
$where_clause = " WHERE State='California' AND City='LA'";
}
// perform query
$queryExample = "SELECT FirstName, LastName, DOB, Address, City, State, Zip".$extra_info." FROM information".$where_clause.$groupby_clause.$orderby_clause;
$resultExample = mysql_query($queryExample) or die('Example Query failed: '.mysql_error());
/*
if no variables are set, then your query would look like this...
"SELECT FirstName, LastName, DOB, Address, City, State, Zip FROM information"
but with the variables set how I did, it now querys this..
"SELECT FirstName, LastName, DOB, Address, City, State, Zip, Country, MotherName, FatherName, MotherMaidenName FROM information WHERE State='California' AND City='LA'";
*/
*************************************
Don't forget about LEFT JOINs
If you dont know how to use them...
SELECT * FROM table LEFT JOIN other ON (table.CommonField = other.CorrespondingField) LEFT JOIN another ON (table.Field = another.OtherFields) ... ect.
**************************************
Obviously, your query doesn't have to be exactly the same, but I hope that points you in the right direction, or helps in some way.
Posted: Tue Oct 11, 2005 5:07 pm
by deltawing
That's a really good idea. I'm sure I can work that into my object structure if its relevant. Thanks for that. And yeah, I already knew about left joins, although I don't know how to use any of the other joins. I'll have to check that out somewhere at some point, and make sure I'm not missing anything else really useful.