[RESOLVED] [PHP] Database interface

[TheBigB]

Hi,

I'm building a database interface for my CMS.
Please correct me if I'm wrong: the purpose of such an interface would be that I could easily convert the database engine from one to another.

I have the following functions the interface:

• connect
• close
• select_db
• query
• num_rows
• fetch_assoc
• escape_string
• error
• errno

Am I missing anything important/useful?

Thanks

[techgnome]

try taking a look at CodeIgniter http://codeigniter.com/ look through their Database Class in the User Guide ... should give you a pretty good idea of what to look for.

-tg

[kows]

yes -- the purpose of an interface is so that you can define the way a class should work, but leave the actual implementation up to the developer (either you, or someone else). so, then you can create a MySQL-based class and also an Oracle-based class, and all of the method names would be the same.

I would personally suggest writing methods that allow you to create prepared statements; they are absolutely invaluable and remove the chance of a user opening themselves up to SQL injection.

[visualAd]

Have you considered using PDO?

[MartinLiss]

Moved from PHP forum.

[TheBigB]

Have you considered using PDO?

I haven't, but I might, now that I've read that.
Should I be worried about webhosters not having PDO enabled?

I would personally suggest writing methods that allow you to create prepared statements; they are absolutely invaluable and remove the chance of a user opening themselves up to SQL injection.

I've never worked with them in PHP but I see PDO has support for prepared queries, so I might consider that.

try taking a look at CodeIgniter http://codeigniter.com/ look through their Database Class in the User Guide ... should give you a pretty good idea of what to look for.

-tg

After a quick glance I at least found one function I really like;

$data = array(
               'title' => $title,
               'name' => $name,
               'date' => $date
            );

$this->db->insert('mytable', $data);

// Produces: INSERT INTO mytable (title, name, date) VALUES ('{$title}', '{$name}', '{$date}')

PDO would be awesome, but the only question that remains is scale of deployment.
Thanks for the suggestions so far.

[TheBigB]

Also, is it a good idea to design a front-end for PDO?

[MarMan]

May wish to add select_server.

[TheBigB]

That's pretty much what connect is for.
Or do you mean something else?

[MarMan]

Your Interface looked similar to mine. I build the connect string from properties:
UserID property
Password property
DataBase property
Server Property

Then my connect method takes no arguments. I thought a server property would be nice if you can change the database. But if you're just connecting to Access then you may not need it.

[visualAd]

I haven't, but I might, now that I've read that.
Should I be worried about webhosters not having PDO enabled?

Web hosts should probably be worried that you'll find another host if they don't support it. I believe that most now do.

Also, is it a good idea to design a front-end for PDO?

It depends on the design pattern you are using. If you are using something like the model view controller pattern, your front end will be another layer of abstraction on top of the PDO layer. At minimum you should consider a simple templating class for the front end.

As well as PDO you also have Pear::DB (which is an abstract library similar to PDO) and Mysqli (which supports prepared and paramatized queries). Also, you can see the link in my sig for PHP5 OOP I have created a DB abstraction layer as an example.

There is little point in writing your though own if there is one that already exists and you don't have very bespoke requirements.