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?
Well hitting the db is one of the most expensive processes. Therefore, minimizing queries should be of the most absolute importance for efficiency and scalability reasons. I try to avoid running queries in loops like I believe your referring to with every sense of my being for those reasons. Seems like in *most cases it can be avoided using joins. Though I wouldn't go as far to say it always being avoided but probably most the time it can be with a little SQL fu. One of the only cases I can think of where it is accepted is when using an ORM that has a smart cache layer. There aren't many good ways to map relational objects back and forth without extra queries or creating custom eager loading methods for specific data sets when dealing with ORMs. This is actually one of the common problems that come up with known ORMs and relational objects leading to usage of memcache and things of that nature.
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
As a realist I'd say that unless you're talking real traffic, you can be pretty inefficient and it won't matter much. In my early days of learning PHP I had sites that did loops within loops within loops for queries and it runs fine because most sites just don't have much traffic anyway. Generally queries in loops should be avoided because it's the wrong way to do it.
However, in your situation - it would be more ideal to check them all in a single query. Then simply filter out the ones that you do/don't want to insert and then generate another single query for inserting them all.
One big query is far more efficient than splitting those up into individual queries, because there is an overhead to run a query.
If you need to run the same type of check without inserting, create an array of the IDs, convert array into a string that would equate to part of a "ID in ()" where criteria, return results to a seperate array. Then run a for each to check if item in array 1 is existant in array 2 from your recordset.