[SOLVED] Multiple Queries

[Fehrant]

Hello all. I am sort of an amateur to PHP, so some of my questions might come off as rather naive; please bear with me. I am currently working on coding a forum from scratch. I have never done any massive access website, but I am aware one should always try to optimize the code so as to not have the db do much work (among other optimizations). Yet, I have some across some cases where, either because of ignorance or because of not addressing the issue properly, I have to do two different sql queries in one page. Now, is this common? Is it bad?

 

Let me give you something concrete to work with. In a forum, when you make a new thread, not only do you add (INSERT INTO...) a thread entry to the thread table, but you also need to add a post entry to the post table (well, of course this depends on how your db is sorted out). Thus, I would have to make two sql insert statements once the form with all the information is submitted.

 

And on the subject of multiple queries, what is more memory effective? Two sql statements, or one with a JOIN clause?

 

[rarebit]

And on the subject of multiple queries, what is more memory effective? Two sql statements, or one with a JOIN clause?

By combining the query, yes you would lessen the network load by only having a single handshake. Yet the resulting data amount would probably be the same. There's times when you just gotta do multiple access's, be aware. If you need recursive functions then you generally can do an optimised query and then resort with php, but it's always a trade off. At the end of the day use 'microtime' to simply study access rates, there's also some admin (logging) functions in sql for further study.

[Psycho]

And on the subject of multiple queries, what is more memory effective? Two sql statements, or one with a JOIN clause?

 

Well, I agree with rarebit that sometime multiple queries are needed, as in your example above about two insert queries. But when it comes to doing multiple queries vs a single query I would always suggest using the single query.

 

I see a lot of posts on this forum where people do a query for all users in the users tab (for example), and then loop through each record and do another querey for user data that is in a related table. These looping queries are absolute resource hogs and should be avoided. I believe that people that use that approach do so because they do not have a firm grasp of how to work with relational databases.

 

Knowing how to craft a complex query with multiple joins is just as much an art as it is a science. It is something I strugle with many times, but the research I go through to get the "right" query makes me a better programmer.

[Fehrant]

Thanks for the replies.

 

So you guys are basically saying it's ok sometimes to have more than one query, and you're also saying that joining two tables is easier on the db than doing two queries. Awesome. I'll keep this in mind.