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

    Help needed for SQLSRV_QUERY : only getting one row returned

    This table has over 100k records, but I am only getting one returned.
    What am I doing wrong ??

    Here's my query code:
    PHP Code:
    $rzSQL='SELECT fullpartnumber,description,lowprice,price,quantity FROM partrecord'
    $rzStock=sqlsrv_query($rzConn,$rzSQL);
    if(!
    $rzStock){
        echo 
    'Error in statement execution.\n';
        die( 
    print_rsqlsrv_errors(), true));
    }else{
        echo 
    '<pre>rzStock: 'var_dump($rzStock); echo '</pre><br/>';  //  for testing
        
    $partnumbers=sqlsrv_fetch_array($rzStockSQLSRV_FETCH_ASSOC);  //  
        
    echo '<pre>partnumbers: 'print_r($partnumbers); echo '</pre><br/>';  //  for testing 

    And, here's the output:
    Code:
    rzStock: resource(7) of type (SQL Server Statement)
    
    partnumbers: Array
    (
        [fullpartnumber] => 0000-04-0523
        [description] => 
        [lowprice] => 0
        [price] => 0
        [quantity] => 0
    )
    I should be getting TONS of data back, yet only get this one record.
    Where do I go from here?

    Thanks-a-bunch,
    ~ Mo

    NOTE: We had to use SQLSRV_xxx instead of MSSQL_xxx because, for some odd reason, we could not get MSSQL working on this machine.
    Last edited by mOrloff; 03-16-2010 at 02:50 PM.

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,979
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    You're missing a while loop in there. At 100,000+ records, that will take a long time to process and print on you're screen, you'll want to add a pagintation of some sorts in there.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #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
    Thanks, although I don't understand why a while loop would be needed for a select statement.
    Could someone please explain ?
    Is this something specific to the SQLSRV extension ??

    ~ Mo
    Last edited by mOrloff; 03-16-2010 at 02:52 PM.

  • #4
    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
    Additionally, I actually want all the records in an array for later use ... unless there is a better way of doing it ??
    (I'm only echo-ing as a test.)

    Here's my task at hand.
    We have a table of aprox 100,000 part numbers. All of these have deprecated descriptions (deprecated in content, values & format).
    In order to update those, I need to check each one against a table with aprox 715k records for part-number matches, and where matches are found, update the stock records with the new descriptions.

    I was planning to grab all our stock into a MASSIVE array, then loop through each one to compare it against the new data and gab the description for the update.

    Are there better options ??

    ~ Mo
    Last edited by mOrloff; 03-16-2010 at 02:46 PM.

  • #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
    Anybody ?
    Any input is welcome.

    Thanks-a-bunch!
    ~ mo

  • #6
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,979
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    A loop is always required when fetching from external resource. PHP has no idea where the resource is, or what its doing, all it can tell is if there are more results to go through. With SQL, you'll need a loop to fetch each record; the fetch call only grabs the current record and increments the offset pointer.

    You will not want PHP to do this task alone, it will take too long to guarentee the successful state of the results. What you can do is join these two tables to determine what needs to be updated, and then update based on this. You should be able to use SQL to perform this entire task, but the only way I can think of off the top of my head is a nested select which wouldn't be favourable in this situation (looking something like this; no clue if it is even valid hah):
    Code:
    UPDATE table SET field = (SELECT field FROM othertable WHERE othertable.partnumber = table.partnumber) WHERE EXISTS (SELECT field FROM othertable WHERE othertable.partnumber = table.partnumber)
    Check in the SQL forum for more advice on this one; old pedant and guelphdad are brilliant with SQL!
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #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
    Thanks, will do.
    One thing I probably should have mentioned earlier is that the smaller table is in a MSSQL DB, and the other table is in a MySQL DB.

    If you have any other pointers, please post.
    In the mean time, I'm off to the SQL forum.

    Thanks-a-bunch.
    ~ Mo

    Edit:
    And, by the way, I am totally embarrassed that I didn't remember that.
    I have used while($row = ...) enough times in the past that you'd think I would remember
    Any how, thanks again.
    Last edited by mOrloff; 03-16-2010 at 05:19 PM.

  • #8
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,979
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    Hmm, with multiple databases, you'll need to throw something between them; I don't believe you can facilitate the communication directly between two different types.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #9
    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, Fumigator suggested that I come back to the PHP plan.

    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
    •