mssql_fetch_batch
(PHP 4 >= 4.0.4, PHP 5, PECL odbtp >= 1.1.1)
mssql_fetch_batch — Returns the next batch of records
Description
int mssql_fetch_batch
( resource
$result
)Returns the next batch of records.
Parameters
-
result -
The result resource that is being evaluated. This result comes from a call to mssql_query().
Return Values
Returns the number of rows in the returned batch.
Examples
Example #1 mssql_fetch_batch() example
<?php
// Connect to MSSQL and select the database
$link = mssql_connect('MANGO\SQLEXPRESS', 'sa', 'phpfi');
mssql_select_db('php', $link);
// Send a query
$result = mssql_query('SELECT * FROM [php].[dbo].[people]', $link, 100);
$records = 10;
while ($records >= 0) {
while ($row = mssql_fetch_assoc($result)) {
// Do stuff ...
}
--$records;
}
if ($batchsize = mssql_fetch_batch($result)) {
// $batchsize is the number of records left
// in the result, but not shown above
}
?>
- PHP Руководство
- Функции по категориям
- Индекс функций
- Справочник функций
- Расширения для работы с базами данных
- Расширения для работы с базами данных отдельных производителей
- Microsoft SQL Server
- mssql_bind
- mssql_close
- mssql_connect
- mssql_data_seek
- mssql_execute
- mssql_fetch_array
- mssql_fetch_assoc
- mssql_fetch_batch
- mssql_fetch_field
- mssql_fetch_object
- mssql_fetch_row
- mssql_field_length
- mssql_field_name
- mssql_field_seek
- mssql_field_type
- mssql_free_result
- mssql_free_statement
- mssql_get_last_message
- mssql_guid_string
- mssql_init
- mssql_min_error_severity
- mssql_min_message_severity
- mssql_next_result
- mssql_num_fields
- mssql_num_rows
- mssql_pconnect
- mssql_query
- mssql_result
- mssql_rows_affected
- mssql_select_db
Коментарии
This could be usefull if you have a large dataset:
<?php
$qry = mssql_query("SELECT * FROM huge_table", $conn, 1000); // 1000 Rows batchsize
do {
while ($row = mssql_fetch_row($qry)) {
// do something like
print_r($row);
}
} while (mssql_fetch_batch($navqry)); // get the next batch until end
?>
This demonstrates the return value:<?php
$connect = mssql_connect("databasename", "username", "password");
$sql = "SELECT * FROM huge_table"; // returns 3271 rows, for example
$qry = mssql_query($SQL, $connect, 1000); // 1000 Rows batchsize
$batchsize = mssql_num_rows($qry);
print "$batchsize <br />\r\n";
do {
$batchsize = mssql_fetch_batch($qry);
print "$batchsize <br />\r\n";
} while ($batchsize); // get the next batch until end
?>
returns:
1000
1000
1000
271
0
I was having issues doing two different select queries with batch sizes in the same connection. The first one would run fine, the second one would always return one batch + 1 record (ie. batch size on the 2nd query was 250000 records, it would stop after 250001 and say it was complete even though there should have been 3m+ records).
I was able to overcome this by disconnecting from the DB (via mssql_close($conn);) and reconnecting before I ran the second query. Now my second query returns all the records I need.
Though I would write this here as there is little to no documentation on this issue, and it may only be affecting me. I am connecting from Linux (RHEL5) to SQL Server 2005 on the network, using FreeTDS. If you have this same issue, try the above and it may work for you.
If you are doing more than one mssql_query(), you must do a mssql_free_result() to avoid undesirable results of mssql_fetch_batch()