View Full Version : Limited results : Why do I only get 145 records ??

03-18-2010, 08:32 PM
I am trying to compare a dataset from an MSSQL query to some MySQL data.
(This is all on a local machine which is both an MSSQL Server and a WAMP server.)

When I comment out all the stuff which pertains to running the MySQL query (as shown in the code included below), I get a total of 7218 rows returned.

As soon as I uncomment the $result=$fchzConn->query($fchzSQL); line, my resultset drops down to about 150 records.

What am I missing ??

Here's the applicable code: (I try to comment like a madman to make review easier)

//while($keepGoing){ // REM'd during production/testing
$j=$k=0; while($k<3){ $k++; // for testing
if($lastIteration) $keepGoing=false; // we've reached stopping point for the WHILE($keepGoing) loop
$rzSQL="SELECT fullpartnumber AS pn,description,lowprice,price,quantity AS qty FROM partrecord WHERE quantity>$qtyBottom AND quantity<=$qtyTop"; // sql
$rzStock=sqlsrv_query($rzConn,$rzSQL); // run the query
if(!$rzStock){echo 'Error in statement execution.\n'; die( print_r( sqlsrv_errors(), true));
$i=0; // for testing
while($row=sqlsrv_fetch_array($rzStock, SQLSRV_FETCH_ASSOC)){ // use "sqlsrv_fetch_array($rzStock)" for regular array
$i++; $j++; echo "$j) Group $k : Row $i (top qty = $qtyTop)"; // for testing
$stockPN=$row['pn']; // grab the part number
$fchzSQL="SELECT pn,description FROM future WHERE pn=\"$stockPN\"";
echo $fchzSQL.'<br/>'; // for testing
// $result=$fchzConn->query($fchzSQL);
// if(!$result){
// echo " - No match found for $stockPN.<br/>"; // for testing
// }else{
// $array=$fchzConn->fetch_array($result);
// echo '<pre>'; print_r($array); echo '</pre>'; // for testing
// }
} // end WHILE($row...) loop
$qtyBottom=$qtyTop; // set the new bottom qty to pick up where the last loop left off
if($qtyTop>=100000){ // increment the top qty for paginating the query
} // end IF/ELSEIF($qtyTop>=...) conditionals
$lastIteration=true; // the next loop will be the last one
$qtyTop=$qtyMax; // don't overshoot
} // end IF($qtyTop>=...) conditional
} // end WHILE($keepGoing) loop

NOTE: I did look in the query method in the DB class, and I don't see anything that would limit it from that side. If you would like to see it anyhow, just let me know.

~ Mo

03-18-2010, 08:49 PM
Have you checked you're error log for memory issues? To me, it looks like you're re-querying every record without freeing previous resources. Eventually the garbage collector will get it, but until that point it will hold onto the memory for you.

03-18-2010, 09:38 PM
Yeah, I've checked the Apache, MySQL and PHP error logs.
No errors about this.
Additionally, I have error_reporting = E_ALL in the php.ini.

What else can I look into?

~ Mo

03-18-2010, 09:49 PM
How are you getting you're display for the records set? That the $i I'm assuming? What is the actual query result say for the count?

Also, setting you're error_reporting is only effective from a view scope if you also have display_errors enabled.

03-18-2010, 11:26 PM
1) $j is the main counter.
For example:

echo "$j) Group $k : Row $i (top qty = $qtyTop)"; // for testing

echoes: "1) Group 1 : Row 1 (top qty = 1) ... etc"

2) I just tried to get the count from the actual query result, and for some reason it's not returning anything. Using this:

$rowCount=sqlsrv_num_rows($rzStock); // for testing
echo'Row Count NOT Successful<br/>';
echo"Count of rows in this group: $rowCount<br/>";
} // for testing
I get "Row Count NOT Successful"

3) Yes, display_errors is on

~ Mo

03-19-2010, 12:13 AM
Add just a final print at the end of the script indicating completion. Does it still make it there when the inner query is uncommented?
If it doesn't, problem with the script which I would presume points to memory usage. If it does, I'm at a loss I'm afraid. I'm not familiar with the usage of the sqlsrv_* functions, but I've assumed the signatures are identical with the exception of the names to the MSSQL extension.

03-19-2010, 12:50 AM

I just found it!
I REM'd all the usage of the MySQL DB class, and hand coded in the the connection and query stuff.
It looks like the class is doing some sort of error suppression or something.

With the hand coded script, it ends with a notification of Maximum execution time exceeded.

Wow! That was fun.
~ Mo

PS: is there a good rule of thumb for how far I can push out the max execution time ??

03-19-2010, 04:38 PM
Nope, you can pretty much run indefinitely especially for a one time run deal. I've never had an infinite script on a hosted website before, but I have run while(true) loops within my PHP in the past on home servers. These caused me no problems whatsoever in regards to usage. On a hosted one, maybe limit it to 600 seconds to see how that goes; I suspect that it will run successfully within that timeline.
Too bad PHP doesn't have 'real' multithreading, otherwise this task would be done in very little time.
If available, look at using a prepared statement call against that mysql. If necessary, use a mysqli object instead which supports them by default. These allow quicker batch calls generally used with insertions / updates, but there is no reason why you cannot use them with selections. I believe those will actually reuse the query like its been 'cached' but bind variables at runtime. That should give you a bit of a performance gain.