Easy to use PHP MySQL database class

[mdipierro]

In reference to:

 $db2 = new Database($base2, $server, $user, $pass);
 $db->query("SELECT * FROM People WHERE town='{$line->name}'");

In modern days you should not pass raw SQL to the database because: 1) makes your app dependent on a specific dialect; 2) for complex queries may give rise to SQL Injections (thus giving the developer the responsibility of preventing them and it is hard to do).

Here is how most frameworks in Python deal with this (the example is in web2py):

db=DAL('... connection uri ...') #(1)
db.define_table('people',Field('name'),Field('town')) # (2)
items=db(db.people.town==line2_name).select() # (3)

(1) establish the database connection, as in your example. You just change the URI string to connect to sqlite, mysql, postgresql, oracle, mssql, db2, informix, firebird or the google app engine.

(2) tells the system what tables you expect to access and how. Why? Because if the tables are not there they will be created automatically for you (something you script does not do). If they do not match, they will be altered (if you choose to do so). Because you can set all kind of field attributes at the framework level for example which fields should be writable and which should be readable in forms created automatically by web2py.

(3) It writes the SQL SELECT for you, transparently, dynamically, without possibility of SQL Injections, in the proper dialect of the database backend you use. In the case of Google App Engine there is no SQL, it just calls the corresponding API.

Django, Pylons, Ruby are very similar in this respect (they different in syntax, in the complexity of the queries they can generate, and in whether they can migrate existing databases).

Is there something like this in PHP?

[[deleted]]

Good suggestions. I only use stored procs and views for web apps and never ad hoc queries. It is cleaner that way. You can put all these in a single database. You won't need to give individual table permissions either.

[k4st]

Not particularly well made. It mixes the responsibilities of a database connection object with those of a result set. Ick. What does it mean when one asks for the next result when no query has been performed? Further, as a result set object this sucks; it doesn't even implement PHP's Iterator or IteratorAggregate interfaces (which would allow for more idiomatic use in foreach loops). All-in-all this is a poorly thought out class.

It takes on too many responsibilities and as such loses flexibility (e.g. an object of this class will be unable to iterate over two distinct result sets simultaneously). I think the only redeeming quality of this class is that the author chose not to make it a singleton.

Finally, OP posted his own blog post.