Partager via

Functions to return row count and field count

  • Article

It’s great to see that people are downloading our the February CTP build of our PHP driver and that we’re getting feedback on our new features. We look forward to more of your feedback and we’re in the process of setting up a new forum specifically for questions and feedback for the PHP driver.

A lot of the feedback has centered around requests for a num_rows function for the PHP driver. In our PHP driver, we’re using the most performant way to retrieve the results of a SQL Server query, a forward-only read-only stream of data. With this approach there’s no way for the driver to know how the total number of rows that the query will return until you’ve finished processing the results of the query. We could offer a num_rows function but without providing buffered resultsets, that num_rows function would only return the number of rows returned so far. While that appears to be a fairly standard behavior for drivers that don’t support buffered resultsets, it doesn’t strike me as adding a great deal of value. With that said, I’m curious as to whether there’s real interest in a num_rows function without adding support for buffered resultsets?

We are planning on adding a function to return the number of fields in a resultset later in this release. There’s a simple workaround in the meantime – use the count function in conjunction with sqlsrv_field_metadata:

$fieldCount = count(sqlsrv_field_metadata($stmt));

The main benefit in adding a function to return this information is that returning the number of fields without generating the field metadata would be more performant.

David Sceppa

Program Manager, SQL Server Driver for PHP

Comments

  • Anonymous
    March 06, 2008
    I understand there is no way of returning the number of rows in an unbuferred resultset driver. Simple enough to understand. However, php_mysql and php_mysqli have both had buffered resultsets for age and php_mssql has also been working that way. Developing a new un-buffered driver will surely break the original way of thinking for databases in the PHP world. And it DOES make sense to have a buffered resultset for a PHP application (web page display). Since the data is most of the time going to be output to a web page, all resultsets must be limited in size at some point. There is no such thing as performance since you want, most of the time the whole resultset to be outputted. The kind of application is different from a standalone database application that does a lot processing / data transfer / migration. A PHP script has to run in a very limited amount of time. MySQL is the de-facto database for PHP, and most PHP applications are written for this database engine. SQL Server support for PHP applications is often only a plus to make some Microsoft-only customers happy. I think you are overestimating the real market for PHP applications natively built for MSSQL. Developers are not stupid, if SQL Server is the only database to be supported, why bother using PHP at all? What we want really is just a working/modern/stable version of php_mssql.php.

  • Anonymous
    March 09, 2008
    can fix utf-8 bug? i am come from china. i need insert utf-8 data to sqlserver 2005, but (February 2008) version can't work. $tsql="update test_users set name='测试名' where uid=1"; $stmt = sqlsrv_prepare( $conn, $tsql); $result = sqlsrv_execute( $stmt); SQLSTATE: 42000 code: 105 message: [Microsoft][SQL Native Client][SQL Server]字符串 '娴嬭瘯鍚? where uid=1' 后的引号不完整。 SQLSTATE: 42000 code: 102 message: [Microsoft][SQL Native Client][SQL Server]'娴嬭瘯鍚? where uid=1' 附近有语法错误。 SQLSTATE: 42000 code: 8180 message: [Microsoft][SQL Native Client][SQL Server]未能准备语句。

  • Anonymous
    March 10, 2008
    The main reason why the num_rows function is used, is to check whether there are actual results in the query. To give an example: if ( get_num_rows() ) { while ( $row = fetch_row() ) { --- do some action --- } } else { --- do some action when there are no rows returned } I have checked our software and this is what it is used for 90% of the time. In other cases it is used if we want a specific number of rows returned and check on that or if we want to create a specific array based on the number of rows (not being the actual output of the result). Let's say we want a dropdown with an ordering filling up until the number of rows and output this alongside each row. The alternative is obviously a count query (which I currently implemented as a workaround), but performance wise this is less optimal. The dataseek function is really needed as far as I'm concerned. The reason being that SQL Server does not support the limit statement as MySQL does, and most web applications use paging to limit the output to the browser. The solution we use in our SQL Server driver is to use TOP instead and then jump to the first needed row to mimik LIMIT x,y. LIMIT 20,10 would become TOP 30, then jump to row 20 using dataseek. The alternative of subquerying with tripple ordering, double limiting is not a solution that can be used in all cases and requires ordering of the actual output all the time and knowing the order by fields in all cases; and there can be order by fields that have multiple rows for each value. My current solution is to simply fetch the unwanted rows until the first needed row is ready for fetching. This strikes me as less performant. To conclude; it would be wise to make this driver work simular to the mysql driver (or the current php_mssql driver). This makes porting code to different database and building applications able to connect to different backends a lot easier. Maybe a solution would be to choose between buffered and unbuffered resultsets. When unbuffered is used the dataseek and num_rows will not work, returning an error.

  • Anonymous
    March 11, 2008
    I do agree with W1lz0r that num_rows is used almost exclusively to test the fact there are actual results in the query. A function like "has_rows" would surely be a good compromise, well better than nothing. Writing multi-DB applications is hard enough, just by trying to find the universal SQL statement (when it exists). Let's not make it harder by having a driver that doesn't play like the others.

  • Anonymous
    March 30, 2008
    The comment has been removed

  • Anonymous
    April 09, 2008
    First off, I'm glad for SQL Server 2005 Driver for PHP. I have many problems with "build-in" MSSQL support in PHP (message "Warning: mssql_connect(): Unable to connect to server" quite randomly, I must recycle DefaultAppPool in IIS6 for every 4 minutes, to avoid problems) and I hope that driver could be solution for this. I fully agreed with W1lz0r and others. Row_number() is needed in web development. I have mssql_row_number() in many functions/objects in my projects, truly I need this function.

  • Anonymous
    April 09, 2008
    Ah, I mean "mssql_num_rows()", "num_rows" of course :)

  • Anonymous
    May 29, 2008
    First off, thanks for all of the feedback on the topic. Most of the feedback I'm seeing regarding num_rows implies that developers are using the function to determine whether or not the query returned data.  For those looking to determine if the query returned rows, would checking the return value on the first call to fetch, fetch_array, or fetch_object suffice? We are looking to add support for scrollable results and other features in a future release to make it easier to modify applications to work with the driver.

  • David Sceppa  Program Manager, SQL Server Driver for PHP

  • Anonymous
    June 04, 2008
    To David Sceppa: What you suggest may be better than nothing. Here is a typical usage of num_rows in PHP: $rs = mysqli_query($dbh, "SELECT * FROM ..."); if (mysqli_num_rows($rs) == 1) { So basically what would be useful is something that takes a resultset object as an argument and returns the number of rows. Something like: mssql_has_rows($rs) could be good in the meantime. I think we all understand MySQL and MSSQL are working on two different paradigms, but what we need is something that really works like other PHP drivers. If we really want to rewrite all our applications using unbuffered drivers, there are already a load of drivers we can use (PDO_MSSQL, ODBC) that does that, so there is no point of yet another driver.

  • Anonymous
    June 05, 2008
    Thanks for the added feedback regarding has_rows. As I've said before, we are looking at adding support for scrollable resultsets (which would be required for num_rows) in a future release.  We made a conscious decision not to try to provide all functionality in v1.0 of the driver so that we could get v1.0 of the driver out in a more timely fashion.  While there are no guarantees, scrollable resultsets is on the agenda for the next release.

  • David Sceppa  Program Manager, SQL Server Driver for PHP

  • Anonymous
    June 10, 2008
    I think that there's an obvious confusion here between what we want as users of this extension and what is being suggested as as concomitant necessity.   I, for one, do not want scrollable result sets, because they require substantial overhead.  But I do want/need a function to indicate if there were records returned. Adding scrollabe result sets just turns this into an implementation of something like ADO in php, which isn't what is wanted.

  • Anonymous
    June 24, 2008
    Thanks, virmaior.  I understand that you're looking for a function to indicate if there were records returned.  We are looking at adding that functionality in the next release. We are also looking at adding support for scrollable resultsets, including row count functionality.  We expect that we would rely on either SQL Server's server-side cursors or ODBC's client-side cursors in order to provide scrollable resultsets and that row count is only available when explicitly requesting a scrollable resultset. Your feelings regarding overhead involved in scrollable resultsets is understandable.  Scrollable resultsets will not be the default, nor will they be required in order to call the function to determine whether or not rows were returned. I hope that clears things up.

  • David Sceppa Program Manager, SQL Server Driver for PHP

  • Anonymous
    October 20, 2008
    Hopefully it's not too late to throw a little weight in favor of the num_rows() function. I've created many applications where I need to check if 0 rows were returned, 1 row, or > 1 row. Depending on those 3 possibilities, I show different information on the screen. I think it's absolutely critical to have this functionality. :)

  • Anonymous
    August 26, 2009
    According to the docs, the forthcoming version 1.1 of the driver will include a sqlsrv_num_rows() function, which is welcome news: http://msdn.microsoft.com/en-us/library/ee376931%28SQL.90%29.aspx Is there an expected release date? This is a mandatory feature.

  • Anonymous
    November 23, 2009
    The comment has been removed