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 8 of 8
  1. #1
    Regular Coder mOrloff's Avatar
    Join Date
    Nov 2008
    Location
    The Great Pacific NW, USA
    Posts
    422
    Thanks
    8
    Thanked 6 Times in 6 Posts

    Limited results : Why do I only get 145 records ??

    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)
    PHP Code:
    //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_rsqlsrv_errors(), true));
        }else{
            
    $i=0;  //  for testing
            
    while($row=sqlsrv_fetch_array($rzStockSQLSRV_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
            
    $qtyTop=floor($qtyTop*1.5);
        }elseif(
    $qtyTop>=10000){
            
    $qtyTop+=10000;
        }elseif(
    $qtyTop>=1000){
            
    $qtyTop+=1000;
        }elseif(
    $qtyTop>=100){
            
    $qtyTop+=100;
        }elseif(
    $qtyTop>=50){
            
    $qtyTop+=10;
        }elseif(
    $qtyTop>=10){
            
    $qtyTop+=5;
        }else{
            
    $qtyTop++;
        }  
    //  end IF/ELSEIF($qtyTop>=...) conditionals
        
    if($qtyTop>=$qtyMax){
            
    $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.

    Thanks-a-bunch,
    ~ Mo

  • #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
    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.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • #3
    Regular Coder mOrloff's Avatar
    Join Date
    Nov 2008
    Location
    The Great Pacific NW, USA
    Posts
    422
    Thanks
    8
    Thanked 6 Times in 6 Posts
    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

  • #4
    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
    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.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • #5
    Regular Coder mOrloff's Avatar
    Join Date
    Nov 2008
    Location
    The Great Pacific NW, USA
    Posts
    422
    Thanks
    8
    Thanked 6 Times in 6 Posts
    1) $j is the main counter.
    For example:
    PHP Code:
    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:
    PHP Code:
    $rowCount=sqlsrv_num_rows($rzStock);  //  for testing
    if(!$rowCount){
        echo
    'Row Count NOT Successful<br/>';
    }else{
        echo
    "Count of rows in this group: $rowCount<br/>";
    }  
    //  for testing 
    I get "Row Count NOT Successful"


    3) Yes, display_errors is on

    ~ Mo
    Last edited by mOrloff; 03-18-2010 at 10:44 PM.

  • #6
    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
    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.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • #7
    Regular Coder mOrloff's Avatar
    Join Date
    Nov 2008
    Location
    The Great Pacific NW, USA
    Posts
    422
    Thanks
    8
    Thanked 6 Times in 6 Posts
    AHAH !

    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.
    Thanks-a-BUNCH.
    ~ Mo

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

  • #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
    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.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)


  •  

    Posting Permissions

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