PDO equivalent of mysql_num_rows or mssql_num_rows

Question

I know this question has been asked before but it seems like the solutions have been specific to the problem presented.

I have a codebase with hundreds of instances where mssql_num_rows is used.

Code example:

$db->execute($sql);

if ($db->getRowsAffected() > 0) {
    $total = $db->fetch();

In db class:

$this->rowsaffected = mssql_num_rows($this->query_result);

I can't create generic SELECT count(*) FROM table queries as I have too many specific select statements.
I could run a preg_replace to remove everything between SELECT and FROM and then replace with a COUNT(*) and run a second query but this assumes all queries are setup a certain way.
I could fetchAll first then count() the results but that means upgrading all instances of the if statements.

So what is the best all around replacement to a *_num_rows function if people are updating their code to PDO. Not something that solves a specific problem, but something that replaces the functionality of *_num_rows. If that's not possible what allowed it to be possible before?

I use that for INSERT, DELETE, or UPDATE statements. However: PDOStatement::rowCount() returns the number of rows affected by the last DELETE, INSERT, or UPDATE statement executed by the corresponding PDOStatement object. If the last SQL statement executed by the associated PDOStatement was a SELECT statement, some databases may return the number of rows returned by that statement. However, this behaviour is not guaranteed for all databases and should not be relied on for portable applications. — loader, Jul 22 '15 at 17:10
Why would you need `SELECT COUNT(*)`? I can't see where the problem is, you can't count the size of the array containing db records once your query's done because that would involve changing too many ifs? Technological debt at its best. — N.B., Jul 23 '15 at 14:37
@N.B. If you always choose the best approach, even if it takes longer, you loose time somewhere else. This system is being made obsolete soon enough. I needed a fast way of replacing a single function not a system overhaul. — loader, Jul 23 '15 at 15:51
I agree with you to a point, naturally with an obsolete system and a quick fix - I do understand your point. However, bear in mind that many people that visit this site have similar problem(s) as we do and for the sake of future code and projects - we mention optimal solutions. More than often, an optimal solution is also the shortest and easiest way to implement something, so it depends heavily on the context and the approach whether a great idea is also the optimal solution as well as the shortest possible way. IMO, your proposed solution is not the fastest way of solving the problem. — N.B., Jul 23 '15 at 16:17
The reason I asked this specific question, is to find an answer to why there isn't an exact solution like there was with *_num_rows. I eventually did get my answer via Jay Blanchard, that basically there isn't an equivalent. At that point the problem switches to what's the best approach to changing the way the code works. I wish Jay's comment was actually an answer because I'd like it to be up at the top. Many people have asked for an equivalent to *_num_rows because they are used to using it and the real answer is there isn't an equivalent, so here's some better ways to write your code. — loader, Jul 23 '15 at 16:32
@N.B. Saw your comments under that guy's *now deleted* answer which serves him right. Don't suck up to him, he will take you down when you turn your back. He thinks he's God's gift to the f'ing world. — Funk Forty Niner, Jul 23 '15 at 18:12
@Fred-ii- I know you have the best intention, but I don't suck up to people, I'm usually ruled by logic unless fuelled by usual alcoholic substances, though I appreciate it :) — N.B., Jul 23 '15 at 18:40
@N.B. I've *tried* and have been polite to him before, only to have him backstab me and on many occasions, and hasn't stopped to this day. Take my advice; he's *bad seed* and many will agree. (he's been banned for a year, and for a reason, and was reinstated only recently). Some things never change. — Funk Forty Niner, Jul 23 '15 at 19:27
@Fred-ii- point taken. Though, I'm sure many would place me in the same boat where he's at :) — N.B., Jul 23 '15 at 19:32
@N.B. your name never came up ;-) am sure you're far nicer/polite than he is. — Funk Forty Niner, Jul 23 '15 at 19:35

Jay Blanchard · Answer 1 · 2020-08-17T16:03:23.643

3

If you want to count the rows you can do this with PDO:

$sql = 'select * from users';
$data = $conn->query($sql);
$rows = $data->fetchAll();
$num_rows = count($rows);

There is no way to directly count rows when using a SELECT statement with PDO as stated in the docs.

PDOStatement::rowCount() returns the number of rows affected by the last DELETE, INSERT, or UPDATE statement executed by the corresponding PDOStatement object.

Only do a row count if you absolutely need the count, otherwise you can verify that the query worked with other methods. You should also not use this method if you expect to be returning thousands of rows from a table, instead, use the COUNT() function in a query for just performing the count.

edited Aug 17 '20 at 16:03

answered Jul 22 '15 at 17:11

Jay Blanchard

34,243
16
77
119

So what is it about PDO that prevents counting rows from SELECT statements when this was something that worked in previous mysql connections? – loader Jul 22 '15 at 17:14
It is/was something that the PHP-DEV team struggled with for a long time. In most discussions it was not considered *normal* to count rows because SQL has the built-in functionality. You could fucntionalize what I have provided (I have done so) so that all you have to do is call a function, but in most cases that would be overkill. – Jay Blanchard Jul 22 '15 at 17:39
1

Ok now you got me thinking. I'm lucky in that a getrowsaffected() function already exists so I've built into that a bit of code that removes everything between SELECT and FROM and then replaces it with a count(*) and then runs it to give me a total rows using the same things from the previous statement. It's not elegant but it works. – loader Jul 22 '15 at 18:17

score -2 · Answer 2 · answered Jul 22 '15 at 18:30

-2

So with everyone's help this is what I built.

function getRowsAffected() {

    $rawStatement = explode(" ", $this->query);
    $statement = strtoupper($rawStatement[0]);

    if ($statement == 'SELECT' || $statement == 'SHOW') {

        $countQuery = preg_replace('/(SELECT|SHOW)(.*)FROM/i', "SELECT count(*) FROM", $this->query);
        $countsth = $this->pdo->prepare($countQuery);

        if ($countsth->execute()) {
            $this->rowsaffected = $countsth->fetchColumn();
        } else {
            $this->rowsaffected = 0;
        }
    }

    return $this->rowsaffected;
}

$this->rowsaffected is already being updated in the execute phase if the statement is an INSERT, UPDATE, or DELETE with $sth->rowCount() so I only needed to run this second query on SELECT and SHOWS.

if ($statement == 'INSERT' ||  $statement == 'UPDATE' || $statement == 'DELETE') {
    $this->rowsaffected = $this->sth->rowCount();
}

I feel bad though, because just as I mentioned in my question, that I was looking for an overall solution I seem to have stumbled onto a specific solution that works for me since the code already asks for the number of rows using a function. If the code was doing this:

if (mysql_num_rows($result) > 0) {

then this solution would still create work updating all instances to use that custom function.

answered Jul 22 '15 at 18:30

loader

25
5

1

Your method has a misleading name. Why are you doing a count? That's expensive. You **receive** affected rows already - something affected is something that **changed**. A select changes nothing. Also, when you select something, simply count it. – N.B. Jul 23 '15 at 14:39
You obviosly didn't read my question properly. "I have a codebase with hundreds of instances where mssql_num_rows is used." I'm not trying to create the best approach, I'm trying to upgrade a codebase without having to rewrite everything. I mentioned this in point 3: "I could fetchAll first then count() the results but that means upgrading all instances of the if statements." I'm not getting paid to go through hours of code. You really need to go back and read the question again. – loader Jul 23 '15 at 15:42
I'm commenting your *solution*, not the reason why you came up with it. I understand your reasoning, you are correct in that part. But, you can't disagree with me when I comment on your solution. If you had to start with a project from the scratch, would you use this approach or would you come up with something easier and that makes more sense? I'm sorry if you're offended in any way, but I don't comment on you personally, your abilities, skills or any other property. I comment on the code and the approach and I stand by my words. I would argue with you that proper way would yield less work. – N.B. Jul 23 '15 at 16:24
Solutions aren't just about what's best for the code. I was looking at about 250 instances of where this getrowsaffected function was being called and I wanted to create a solution I could put into that function rather then having to update everywhere it is located. Inside that function originally was an mssql_num_rows call and I wanted to replace it so I didn't have to spent hours replacing every if statement that referenced getrowsaffected. – loader Jul 23 '15 at 16:40
Re: misleading name. I never named this function and I agree it's a misleading name. It was originally used for both insert and select style queries so I think they named it and just used it for both. – loader Jul 23 '15 at 16:43
In any case, I do hope you will soon get rid of this project. I had no ill intent when I commented or when I cast my vote. Whatever happens, I wish you good luck. In case you ever need anything similar in the future using PHP, do consider one of great ORMs / DBAL's available to PHP such as Eloquent, Doctrine or Propel. – N.B. Jul 23 '15 at 18:46

PDO equivalent of mysql_num_rows or mssql_num_rows

2 Answers2

Linked

Related