Help needed for SQLSRV_QUERY : only getting one row returned

[mOrloff]

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_r( sqlsrv_errors(), true));
}else{
    echo '<pre>rzStock: '; var_dump($rzStock); echo '</pre><br/>';  //  for testing
    $partnumbers=sqlsrv_fetch_array($rzStock, SQLSRV_FETCH_ASSOC);  //  
    echo '<pre>partnumbers: '; print_r($partnumbers); echo '</pre><br/>';  //  for testing 
} 


And, here's the output:

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.

[Fou-Lu]

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.

[mOrloff]

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

[mOrloff]

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

[mOrloff]

Anybody ?
Any input is welcome.

Thanks-a-bunch!
~ mo

[Fou-Lu]

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):

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!

[mOrloff]

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.

[Fou-Lu]

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.

[mOrloff]

Yeah, Fumigator suggested that I come back to the PHP plan.

Thanks.