Functions to return row count and field count

Functions to return row count and field count

  • Comments 15

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

  • 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.

  • 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]未能准备语句。

  • 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.

  • 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.

  • First off, thank god someone is finally looking into the SQL driver for PHP. I love MSSQL/IIS and ASP/X is no match for PHP for web development no matter how you look at it.

    Anyways, yes. I use num_rows() in just about every query I do.

    $query = xx_query($sql, $connection)

    if (!xx_num_rows($query)) die("invalid xyz");

    Also, for paging.. if you display 10 results per page you need to know how many results in total..

    I would suggest making an optional parameter which defaults to true for "fetch record count" etc. This way you could optionally specify not to get a record count for speed, but 99% of the time you do want a record count for website development. Infact I can hardly think of an occasion that a web developer would not want to check the number of rows in a recordset, while speed may be a concern, what difference does 0.00000001 seconds make, with a dual cpu xeon 3ghz with 4 gigs of ram, speed is not really an issue :)

    Another function you need is xx_escape()

    This save writing a crappy str_replace("'", "''", $sql) function. It should also remove any un-supported characters and optionally escape wildcards.. ie "hel%o' world" could return "hel[%]o'' world'"

    Also xx_insert_id([$table_name]); while I know @@IDENTITY can get it, I'd rather call a built-in function to be honnest.

    Also, if you want to make this the best driver out there, make xx_insert and xx_update functions.. quite easy:

    $fields=array(

     'field_name'=>'field_value',

     'next_field'=>'initial value'

    );

    xx_insert($table='my_table_name', $fields);

    $id = xx_insert_id($table='my_table_name');

    $fields['next_field'] = 'changed the value';

    xx_update($table='my_table_name', $fields, $where="xx_id = $id");

    These functions should be able to take un-escaped data and badly formated data,.. ie accept 1,234,567.00 as a decimal(16,2) by removing $,#% etc..  

    Having this done at a driver level would be awesome.

    Another thing that would make SQL a lot more popular in the PHP would is making a LIMIT function.. all it needs to do is run the query, jump to the offset and return the record_count rows.. not using TOP which doesn't work with order by.. it'd be slow, but who cares. ie:

    // assume products has 10,000 records

    $sql = "SELECT * FROM products ORDER BY sort_order";

    $query = xx_query_limit($sql, $offset=30, $record_count=10);

    echo xx_num_rows($query); // output: 10

    One more function for newbies, xx_date_format()

    This would take a string, or timestamp and return the best SQL formatted date.. ie:

    $x = xx_date_format('2008-03-31 8:49pm');

    echo $x; // output: 31 Mar 2008 20:49:00

    I've been holding back from using SQL2005 because of the lack of support in PHP.. had enough problems getting SQL2000 running so thought I'd hold off till MS done something about it.. 3, maybe 4 years latter (time flies when having fun?), bingo :)

  • 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.

  • Ah, I mean "mssql_num_rows()", "num_rows" of course :)

  • 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

  • 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.

  • 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

  • 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.

  • 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

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

  • 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.

  • Anybody tested sqlsrv_num_rows() in version 1.1 with CTE queries?

    I getting wrong rows amount and cant fix it.

    http://social.msdn.microsoft.com/Forums/en-US/sqldriverforphp/thread/e011dd5d-c925-4a08-9452-0c1c2e411b49

Page 1 of 1 (15 items)