many database queries

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
deltawing
Forum Commoner
Posts: 46
Joined: Tue Jun 14, 2005 2:55 pm

many database queries

Post 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.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
deltawing
Forum Commoner
Posts: 46
Joined: Tue Jun 14, 2005 2:55 pm

Post 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?
User avatar
J_Iceman05
Forum Commoner
Posts: 72
Joined: Wed Aug 03, 2005 10:52 am
Location: Las Vegas, NV

Post 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.
deltawing
Forum Commoner
Posts: 46
Joined: Tue Jun 14, 2005 2:55 pm

Post 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.
Post Reply