Query kills Loop - Page 2
Page 2 of 2 FirstFirst 12
Results 16 to 23 of 23

Thread: Query kills Loop

  1. #16
    High Energy Magic Dept. NogDog's Avatar
    Join Date
    Aug 2006
    Location
    Ankh-Morpork
    Posts
    13,902
    PHP Code:
            $query "SELECT * FROM TOAWorkorders WHERE TechNum = $TechNum  AND WorkDate = '$IncDate'";
                if (
    $result $mysqli->query($query)) 
                {
                      
    printf("Select returned %d rows.\n"$result->num_rows);

                     
    $result->close();
                }
                else  
    // find out what happened
                
    {
                    die(
    "<pre>Query failed: ".$mysqli->error.PHP_EOL.$query."</pre>");
                } 
    Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be." ~ from Nation, by Terry Pratchett

    "But the main reason that any programmer learning any new language thinks the new language is SO much better than the old one is because hes a better programmer now!" ~ http://www.oreillynet.com/ruby/blog/...ck_to_p_1.html


    eBookworm.us

  2. #17
    Senior Member
    Join Date
    Sep 2012
    Posts
    269
    The script runs without errors. So, something must be happening after the query, but I'll be switched if i can find what's happening. There's nothing but braces after the loop.

  3. #18
    Pedantic Curmudgeon Weedpacket's Avatar
    Join Date
    Aug 2002
    Location
    General Systems Vehicle "Thrilled To Be Here"
    Posts
    21,854
    But this code lies inside another loop which you haven't shown us. There might be something inside that loop (before the code you've shown) that is breaking after its first iteration (in fact it's probably this unmentioned loop to which "1st iteration" has alluded to all along).
    THERE IS AS YET INSUFFICIENT DATA FOR A MEANINGFUL ANSWER
    FAQs! FAQs! FAQs! Most forums have them!
    Search - Debugging 101 - Collected Solutions - General Guidelines - Getting help at all

  4. #19
    Senior Member
    Join Date
    Sep 2012
    Posts
    269
    Since I couldn't figure out my problem, I basically started over. Here's the code that is broken in the same spot:

    PHP Code:
    if ($SystemNumEntry == 0$query "SELECT * FROM Systems";
    else 
    $query "SELECT * FROM Systems WHERE SystemNum = '$SystemNumEntry'";

        if (
    $result $mysqli->query($query)) 
            {
             while (
    $row $result->fetch_assoc()) 
                    {
                    
    $SystemNum $row["SystemNum"];
                    
    $System $row["System"];
                    
    $LongSystem $row["LongSystem"];
                    
                    echo 
    $SystemNum ' !! ' $System ' $$ ' $LongSystem '<br>';
                    
                    if (
    $TechNumEntry == 0$query2 "SELECT * FROM Techs WHERE SystemNum = '$SystemNum'";
                    else 
    $query2 "SELECT * FROM Techs WHERE SystemNum = '$SystemNum' AND TechNum = $TechNumEntry";
                    
                    if (
    $result $mysqli->query($query2)) 
                        {
                         while (
    $row $result->fetch_assoc()) 
                            {
                            echo 
    $row["FirstName"] . ' ' $row["LastName"] . '<br>';
                            
                            if (
    $result $mysqli->query("SELECT * FROM TOAWorkOrders WHERE TechNum = '$TechNum'")) 
                                {
                                
    printf("Select returned %d rows.\n"$result->num_rows);
                                
    $result->close();
                                }
                                
                                else  
    // find out what happened 
                                    

                                    die(
    "<pre>Query 3 failed: ".$mysqli->error.PHP_EOL.$query."</pre>"); 
                                    }
                            }
                        }
                    
                    else  
    // find out what happened 
                        

                        die(
    "<pre>Query 2 failed: ".$mysqli->error.PHP_EOL.$query."</pre>"); 
                        }
                    }
                    
                    

            }
        else  
    // find out what happened 
            

            die(
    "<pre>Query 1 failed: ".$mysqli->error.PHP_EOL.$query."</pre>"); 
            } 
    If '$SystemNumEntry' is 0, then the outer loop should cycle through 66 records. But, the while loop dies after hitting the 2nd query.. If '$TechNum' = 0, && '$SystemNum' = 0, the loop should cycle through 615 records, but the 2nd loop dies when it hits the next query. The traps don't report any errors. To make a short story long, the script gets to the end of the next query and doesn't cycle. I gots to say that this is really weird.
    Last edited by timstring; 02-27-2013 at 03:30 PM.

  5. #20
    Senior Member
    Join Date
    Sep 2012
    Posts
    269
    I fixed it. i added a '2' in second query so that it reads:
    PHP Code:
        if ($TechNumEntry == 0$query2 "SELECT * FROM Techs WHERE SystemNum = '$SystemNum'";
               else 
    $query2 "SELECT * FROM Techs WHERE SystemNum = '$SystemNum' AND TechNum = '$TechNumEntry'";
               if (
    $result2 $mysqli->query($query2)) 
                    { 
    and then:
    PHP Code:
    if ($result3 $mysqli->query("SELECT * FROM TOAWorkOrders WHERE TechNum = '$TechNum' AND WorkDate BETWEEN '$StartDate' AND '$EndDate'")) 
          {
           
    printf("Select returned %d rows.\n"$result3->num_rows);
           
    $result3->close();
           } 
    Who would have thought?

  6. #21
    Pedantic Curmudgeon Weedpacket's Avatar
    Join Date
    Aug 2002
    Location
    General Systems Vehicle "Thrilled To Be Here"
    Posts
    21,854
    Quote Originally Posted by timstring
    Who would have thought?
    Pretty obvious now that some context has been given: in the broken code you're trying to store three different values in the same variable ($result) at once. Once the innermost loop completes, there are no rows left in $result so none of the outer loops find anything in $result and so they stop as well.
    $row has the same issue; after the innermost loop completes, it no longer has the value that either of the outer loops gave it. The only reason that hasn't caused a problem is because you don't use any of the values given to $row by the outer loops after starting the inner loops.

    Using more meaningful names would help avoid this problem.

    Another way to improve this would be to replace some of the looping by join clauses in the SQL - then there would be less code to have bugs in. Another thing to look at is using prepared statements for efficiency, so that the queries don't need to be repeatedly compiled. At the moment a rough estimate based on the sort of numbers you've supplied (66 records, at least one of which refers to >600 records, each referencing something like four or five records) suggests that this bit of code alone is making on the order of 200,000 queries.
    THERE IS AS YET INSUFFICIENT DATA FOR A MEANINGFUL ANSWER
    FAQs! FAQs! FAQs! Most forums have them!
    Search - Debugging 101 - Collected Solutions - General Guidelines - Getting help at all

  7. #22
    Senior Member
    Join Date
    Sep 2012
    Posts
    269
    Yes, the database is humongous and keeps oozing. One table has more than 200,000 rows is is growing by 2,500 entries a day. The other one has about 120,000 records. All of the hints to speed up the search process are greatly appreciated. If I could wrap my brain around making the tables relational, that would help as well. Using prepared statements has helped tremendously. The script has gone from paint-drying speed to less than 15 seconds.

    However, here's one bit of code which really carries the load that i haven't figured out how to accelerate:

    PHP Code:
    if ($result4 $mysqli->query("SELECT IdNum FROM CallVolume WHERE ANI = '$CellNum' AND WorkDate = '$IncDate'")) 
            {
            
    $CallCount["$Count"] = $result4->num_rows;
            
    $result4->close();
            
    $SubCallCount $SubCallCount $CallCount["$Count"];
            } 
    Is speeding this process up even possible?

    tyvm fer da hep

  8. #23
    High Energy Magic Dept. NogDog's Avatar
    Join Date
    Aug 2006
    Location
    Ankh-Morpork
    Posts
    13,902
    An index on the combination of ANI and WorkDate would likely help, if you don't have one yet.
    Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be." ~ from Nation, by Terry Pratchett

    "But the main reason that any programmer learning any new language thinks the new language is SO much better than the old one is because hes a better programmer now!" ~ http://www.oreillynet.com/ruby/blog/...ck_to_p_1.html


    eBookworm.us

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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