PDO, SELECT, numRows?

[dbo]

The documentation I'm reading only provides two solutions for retrieving the number of rows returned from a select statement:

 

1. Run a SELECT COUNT(*) before your actual query to get the results.

2. Use fetchAll and count

 

Gotta tell ya, neither of these is very practical. Is there another solution?

[dream25]

Try this one...

 

SELECT sysobjects.[name], max(sysindexes.[rows]) AS TableRows,

  CAST(

    CASE max(sysindexes.[rows])

      WHEN 0 THEN -0

      ELSE LOG10(max(sysindexes.[rows]))

    END

    AS NUMERIC(5,2))

  AS L10_TableRows

FROM sysindexes INNER JOIN sysobjects ON sysindexes.[id] = sysobjects.[id]

WHERE sysobjects.xtype = 'U'

GROUP BY sysobjects.[name]

ORDER BY max(rows) DESC

GO

[dbo]

I very much appreciate your response.

 

That being said, this is also not a practical answer. Additionally, if you're querying the system tables (as I believe you are) this is going to make it specific to the database in use. Meaning I would need to have new code for postgres vs mysql vs mssql vs oracle, etc.

[trq]

You can use PDOStatement->rowCount() however not all databases will populate this from a SELECT statement.

 

The only way you can do this and make it cross DBMS compatible is to use the COUNT() method you stated. This isn't PDO's fault, but the underlying DBMS.

 

The whole thing with attempting to make your applications cross DBMS compatiable is you need to comprimise all the way. If all DBMS's stuck to standard SQL we wouldn't have this issue, but allas, we do.

[dbo]

I guess I don't understand how mysql can have a num_rows function, yet it doesn't work for PDO and we can say it's not PDO's fault.

 

I thought I had decided on PDO, but I may have to rethink this yet.

[dbo]

This is so inefficient and a hack, but here's what I came up with.

 

Basically what I'm trying to do is wrapper database interaction inside of my own class so that should I choose to do so, I could allow my application to be deployed on different databases. I like mysql just fine, but I don't want to be married to it.

 

This is a generic little class that encapsulates the functions I commonly use. Does it look generic enough that it could easily be modified to use postgres, mssql, oracle, etc by only modifying this file? Please provide feedback.

 

 

class DB
{
   private $handle;
   private $sql;
   
   public function connect($host="localhost", $data="data", $user="user", $pass="pass")
   {
      try
      {
         $this->handle = new PDO("mysql:host=$host;dbname=$data", $user, $pass);
      }
      catch(Exception $e) { }
   }
   
   public function disconnect()
   {
      $this->handle = null;
   }
   
   public function query($sql)
   {
      $this->sql = $sql;
      return $this->handle->query($sql);
   }
   
   public function getNumRows(&$result)
   {
      //HERE IS THE HACK. PASSED IN THE OBJECT BY REFERENCE
      //SO THAT I CAN REQUERY TO POPULATE THE DATA TO BE
      //FETCHED INDIVIDUALLY.
      $count = count($result->fetchAll());
      $result = $this->query($this->sql);
      return $count;
   }
   
   public function getLastInsertId()
   {
      return $this->handle->lastInsertId();
   }
   
   public function getRow($result)
   {
      return $result->fetch(PDO::FETCH_ASSOC);
   }
   
   public function quote($value)
   {
      return $this->handle->quote($value);
   }
}

[dbo]

Blah, wont work.

 

The reason being if I tried to do the following:

 

$db = new DB();
$db->connect();
$result1 = $db->query("SELECT * FROM table1");
$result2 = $db->query("SELECT * FROM table2");
$rows = $db->getNumRows($result1);
for( $i = 0; $i < $rows; ++$i )
{
   $row = $db->getRow($result1);
   echo "1: " . $row['Name'] . "<br />";
   
   $row = $db->getRow($result2);
}
$db->disconnect();

 

The getNumRows would be off because it would effectively be populated the the last run query (SELECT * FROM table2) rather than (SELECT * FROM table1)

[trq]

Yeah, generic enough. Ive never bothered wrapping PDO in anything, its easy enogh to use directly.

 

One thing I would do is add another argument to your connect() method. Database type. Run this $type variable though a switch to formulate a valid DSN for the database in use.

[dbo]

Good advice on the DSN. I'll definitely add that.

 

Just need to come up with a solution for this num rows thing.

 

Basically I'm just a weirdo. I like to use for loops and when I'm testing a result set I've always grabbed the number of rows and been like... if( $rows > 0 ).... do some stuff. I could change the way I do it I suppose but it's a comfort thing and I hate to change the way I'm coding for something so silly.

[dbo]

Hrmmm. Maybe it is reasonable to have the num of rows things only work for the most recently run query.

[dbo]

The DSN was a good call. Thanks for the suggestion. I also modified the constructor such that it's argument is an array that I unpack.

 

That being said, you're right that PDO is easy to use, that's not my reasoning for abstraction. My hope by doing it this way is that I've got a single point of entry should I need to make a change. So in theory if PDO disappeared I could modify this class and restore the old mysql* functions without having to change a bunch of instances in my code. Similarly should PDO be renamed to PDO2 then I'll only have to modify this single file rather than the entire application.

[trq]

The thing is, your limitting your function set. In doing so you create the temptation for someone (or yourself) to bypass your class altogether to get to the functionality they need.

[dbo]

Perhaps. Those are the functions I use 95% of the time though.

 

That being said, I can easily extend the functionality to include, transactions for example. Is there a particular function that you think is useful that I'm excluding? I mean I'll be honest, it's intentionally limited. I like the KISS approach and I don't want a bunch of extra bloat.

[trq]

Is there a particular function that you think is useful that I'm excluding?

 

No, nothing in particular. I just think its a little odd to put a wrapper around it, unless you where creating an ORM or something.

[dbo]

We'll it's truly being designed for a CMS sort of application I've been planning for a long time, more than use for an API. I may access this class directly, but it's truly not intended for others to use.

 

I've got a real slick little datamodel of about 12 or 13 tables that allow me to represent objects on the fly. The idea is to plop an application on top of this and have a datamodel that never needs to change, yet provides a huge amount of flexibility for creating content types and being able to present them.

 

I'm really trying to approach this carefully, as I've got a solid product, but want to do it right from the getgo. If you have suggestions I'm certainly open to hearing them.