View Full Version : Running a query within a loop - Good Practice?

04-11-2012, 06:52 AM

At times we need to run a SQL query within a WHILE or FOR loop to get data based on the value during the loop.

I wonder if its a good practice at all?

04-11-2012, 07:11 AM
No, its terrible practice.
Typically all this data can be fetched in a single query (actually, with perfect normalization and well thought out code, the goal is a single query per script, although I've never achieved that myself due to how I create many separate components and am too lazy to figure out how to get them to join properly without allowing a privacy leak between the objects). What matters beyond that is a proper order by clause, followed by special handling for changes in data to deal with rows, columns, div's etc.
If you refer to data in a previously run query, this would simply be a join.

04-11-2012, 07:14 AM
But you do agree that you too use this sometimes?

04-11-2012, 08:49 AM
no, Fou-Lu only admits that he uses more than one query per script. As he said, queries inside a loop can always be done using JOINs.

04-11-2012, 08:51 AM
oh ok thanks for the clarification.

04-11-2012, 09:48 AM
Inserts, deletes can be done for muliple records with one query. Only update (afaik) needs one query per record.

Running a query in a loop is a terrible idea. Suppose you have 3 or 4 loops in a script that do this, you could have 30 (or more) queries per loop (imagine having a few hundred). Now, imagine how long it would take for that script to complete it's execution. Multiply that by a few users and you've got enough CPU hogging to bring down a server.

You can BUILD yur queries within a loop yes (eg the multiple insert brackets) but it's a really bad idea to have uncrontrolled numbers of queries in a loop.

04-13-2012, 06:41 AM
THanks everyone. You guys have been very helpful and kind to me.

Next, Please Consider the following scenario.

I am developing an FB app where a user needs to select their FB friends (that was fetched from the FB API) from a list (checkboxes) and when they submit the button the selected friend's FB id is saved in the database. Now, what i need to do is, before inserting the FB ids in the db, i need to check if those ids are already in the db. Ids that are already in the db does not gets inserted.

To achieve this, I need to run a loop of the checkbox array, and within each loop I will run a SELECT query to check if that particular Id exits in the db.

If you think this is not a standard and efficient way, can you please suggest a better one?

Many many thanks :)

04-13-2012, 08:45 AM
Use an IN clause. Select the ID from a where clause of ID IN, and the resulting records are those that already exists. Construct an array of the result set, use an array_diff on the checkbox array, and those are the ones to insert.

04-13-2012, 08:53 AM
That helps!

Thanks :)