Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    Regular Coder
    Join Date
    Jun 2006
    Location
    UK
    Posts
    922
    Thanks
    302
    Thanked 3 Times in 3 Posts

    Question Running a query within a loop - Good Practice?

    Hi

    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?

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    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.

  • Users who have thanked Fou-Lu for this post:

    phantom007 (04-11-2012)

  • #3
    Regular Coder
    Join Date
    Jun 2006
    Location
    UK
    Posts
    922
    Thanks
    302
    Thanked 3 Times in 3 Posts
    But you do agree that you too use this sometimes?

  • #4
    Senior Coder Dormilich's Avatar
    Join Date
    Jan 2010
    Location
    Behind the Wall
    Posts
    3,342
    Thanks
    13
    Thanked 349 Times in 345 Posts
    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.
    The computer is always right. The computer is always right. The computer is always right. Take it from someone who has programmed for over ten years: not once has the computational mechanism of the machine malfunctioned.
    André Behrens, NY Times Software Developer

  • Users who have thanked Dormilich for this post:

    phantom007 (04-11-2012)

  • #5
    Regular Coder
    Join Date
    Jun 2006
    Location
    UK
    Posts
    922
    Thanks
    302
    Thanked 3 Times in 3 Posts
    oh ok thanks for the clarification.

  • #6
    Senior Coder
    Join Date
    Feb 2011
    Location
    Your Monitor
    Posts
    4,341
    Thanks
    60
    Thanked 527 Times in 514 Posts
    Blog Entries
    4
    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.
    See my new CodingForums Blog: http://www.codingforums.com/blogs/tangoforce/

    Many useful explanations and tips including: Cannot modify headers - already sent, The IE if (isset($_POST['submit'])) bug explained, unexpected T_CONSTANT_ENCAPSED_STRING, debugging tips and much more!

  • Users who have thanked tangoforce for this post:

    phantom007 (04-11-2012)

  • #7
    Regular Coder
    Join Date
    Jun 2006
    Location
    UK
    Posts
    922
    Thanks
    302
    Thanked 3 Times in 3 Posts
    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

  • #8
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    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.

  • #9
    Regular Coder
    Join Date
    Jun 2006
    Location
    UK
    Posts
    922
    Thanks
    302
    Thanked 3 Times in 3 Posts
    That helps!

    Thanks


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •