dbFacile v0.4

Started: 2006-08-10 Updated: 2008-02-29
Progress: 95%
Language: PHP5
OS: Web
License: MIT

Browse the Latest Code

This project used to be called dbSimple, but I've changed it's name to avoid confusion with this project.

A PHP Database Abstraction Class that honestly offers a simpler interface than all known alternatives. It is intended to have the best combination of simplicity and usability. I've also created a PHP Database Abstraction Class Comparison to show where dbFacile stands.

Features

• Support for popular databases: MySql, Sqlite2, Postgresql and MSSql. Support for PDO-based drivers is 85% complete
• One file and less than 800 lines of code
• No more stepping through result sets; fetch methods return data at once
• Easiest possible method for doing inserts and updates
• Insert operation returns the newly created primary key
• Update operation returns number of affected rows
• Fetch 1 or all rows
• Fetch a column from a table as a 1-dimensional array
• Fetch results of a query into an array of key=>value pairs
• Secure: doesn't save username/password in the object
• Secure: escapes all fields passed to insert/update, unless manually overridden
• Secure: quotes and escapes additional parameters passed to fetch*() and update() methods. This to prevent SQL injection attacks (see README II.B third example)
• Support for transactions
• Compatible with PHP5

Driving Ideals

1. Inserts and Updates are trivial and thus should be automated
2. Traditional prepared statements are rarely worth their trouble
3. Access to fields should be done by name and not column index, because, if your CREATE TABLE statement is modified, the index might pertain to a different field
4. The most common operations (SELECTs) shouldn't take more than 1 line of code, and should return a data structure native to PHP for ease

Why Not Use ... ?

Standard PHP API Functions

• They require monotonous coding for simple things such as looping over the results of a query. See Ideal #4

Pear MDB2

• MDB2 doesn't make interacting with a database any easier, it simply provides a common interface. I wanted more than that.

PDO

• PDO doesn't return meaningful information about the number of rows affected by a query, making it difficult to determine whether inserts and updates have succeeded
• PDOStatement is confusing. See Ideal #2

ADODb

• ADOdb is too bloated, containing features I have no use for. ADOdb Lite attempts to be slimmer, but keeps mostly the same interface which isn't simple enough.

Others

• They're not as simple
• They don't have convenient insert/update methods

Upcoming Features

• getTables() method to return all tables in current database

 

Download

Mercurial Repository

Here - Keep up

0.4

Not released yet. Check the repository above.

0.3

Note: There is 1 known issue with this release. The fetchKeyValue() method has a bug. I will be fixed in the next release but for now you can change line 227 to: $data[ $key ] = array_shift($row);

• Added a delete() method (now it's full CRUD!)
• Transaction support for: mysql, mysqli, postgresql, sqlite. See Methods below.
• Prevents SQL injection in update() where clause
• Made the where clause in update() optional
• insert() uses a transaction to ensure correct unique id is returned
• Not having previously tested the MySQL driver enough, I found a bug that prevented insert() from doing much of anything and fixed it. In the mysql driver class I incorrectly had it trying to extract the field name from the "Name" column of the "describe TABLE" results ... it should have been using "Field"
• Allow insert() parameters and first two update() parameters to be passed in any order (triggers a notice-level error)
• Changed behaviour of fetchKeyValue() when fetching more than two columns. See README.

CHANGES and README

dbFacile-0.3.tar.gz

dbFacile-0.3.zip

0.2.1

Connecting to a database has changed in this version. Check the README for proper usage. Other changes include: MIT license, split DB-specific code into "driver classes", safer way to specify where clause for update() that prevents SQL injection, and more.

dbFacile-0.2.1.tar.gz

dbFacile-0.2.1.zip

Agenda

Full functionality for Microsoft SQL Server and Sqlite3 is in the works. Basic fetching methods work, but more advanced things like inserts and updates might be broken in places.

Methods

See the README for better examples

dbFacile_mysql([connection handle])

Pass in existing connection handle to use it. (Eliminates need to call open())

open(USERNAME, PASSWORD, DATABASE, HOST)

Connects to database

logToFile(FILE)

Logs all queries to a file

fetch('select * from users')

Performs SQL query and returns two-dimensional array of rows and each's columns and values.

fetchRow('select * from users where name=?','john')

Performs SQL query and returns associative array of fields and values.

fetchCell(SQL)

Performs SQL query and returns first field from first row in result.

fetchColumn(SQL)

Performs SQL and returns the first column as a 1-dimensional array.

fetchKeyValue(SQL)

Performs SQL and returns an associative array with users.id as the key and users.email as the value for each. If more than two fields are fetched, the first field is used as the key and an array of the remaining fields becomes the value.

insert(DATA, TABLE)

Inserts associative array of data into table, returns newly generated primary key. Associative array keys should correspond to the table's field names.

update(DATA, TABLE, WHERE)

Updates records in table with associative array of data satisfying where clause. Returns number of affected rows. See insert() above.

delete(TABLE, WHERE)

Deletes records in table satisfying the where clause. WHERE is optional.

beginTransaction()

Begins a transaction

commitTransaction()

Commits a transaction

rollbackTransaction()

Rolls back a transaction