$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 he’s a better programmer now!" ~ http://www.oreillynet.com/ruby/blog/...ck_to_p_1.html
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.
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).
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.
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();
}
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.
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"];
}
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 he’s a better programmer now!" ~ http://www.oreillynet.com/ruby/blog/...ck_to_p_1.html
Reply With Quote
Originally Posted by timstring
Bookmarks