PHP: Fast and easy SQL queries using ezSQL

by Jean. 36 Comments -

Nowadays, most websites and web applications are database driven, which means that you, the developer, have to query the database to get the requested information. Let’s take a look at a very useful tool to handle SQL queries easily and efficiently on small to medium projects.

What’s ezSQL, and why it is useful

On big projects, the usual good practice is to use a CMS or a framework such as Symfony or CodeIgniter to build your site on. But on smaller projects, many developers are still using PHP functions such as mysql_query() to do SQL queries to the database.

While it’s functional, I do not recommend to use all those mysql_XXX functions: Most websites are using MySQL, that’s right, but if one day you have to deal with another DB like PostGres or Oracle… Your code will not work at all, and you’ll have to rewrite it. Scary, isn’t it? This is why is it recommended to use a database abstraction layer, an API which unifies the communication between your application/website and databases such as MySQL, Oracle or PostgreSQL.

As you can guess, ezSQL allows you to work with various databases very easily. Though, please note that it does not support differences in SQL syntax implementations among different databases.

Also, ezSQL provide a few methods which simplify queries to the database, and help producing a cleaner code.

ezSQL and WordPress

As most of you are familiar with WordPress, you probably know the wpdb class, which allows you to send queries to the database. As wpdb is based on ezSQL, and you’re already familiar with the WordPress class, you won’t have any trouble to learn using ezSQL. And don’t worry if you never heard of WordPress or the wpdb class. ezSQL is extremely easy to learn and to use.

Downloading and installing ezSQL

Right, I have talked too much. How about some coding now? Let start by grabbing your copy of ezSQL. Once you have it, unzip on your server (or hard drive).

In order to be able to use ezSQL in your projects, you have to include two files: The first is ez_sql_core.php, which is ezSQL core file. The second depends on the database you’re going to use. In order to use ezSQL with a MySQL database, you have to include ez_sql_mysql.php.

Once done, you have to create a ezSQL object. This is done easily using your database username, password, name and host. The following example demonstrates the inclusion of the required files and the creation of a ezSQL object:

include_once "../shared/ez_sql_core.php";
include_once "ez_sql_mysql.php";
$db = new ezSQL_mysql('db_user','db_password','db_name','db_host');

Now, you have an object called $db. We’ll use it run any types of queries to our database.

Queries examples

ezSQL has a few methods to make SQL queries extremely simple. Let’s see what you can do with it:

Execute any query

In order to insert, delete or most generally, run any kind of query to the database, we have to use the query method. In case of a data insertion, the method will return the insert id.

$db->query("INSERT INTO users (id, name, email) VALUES (NULL,'The Cat','cat@google.com')");

Example of an update query:

$db->query("UPDATE users SET name = 'Patrick' WHERE id = 4");

Select a row

The get_row method is great if you just need to select a row from your database. The example below executes a simple select query and displays the results.

$user = $db->get_row("SELECT name, email FROM users WHERE id = 4");

echo $user->name;
echo $user->email;

Select a single variable

If you only need a variable, the get_var method is here to help. Using it is extremely simple as shown below.

$var = $db->get_var("SELECT count(*) FROM users");

echo $var;

Select multiple results

Although the methods documented above are quite useful, most of the time you’ll need to get various rows of data from your database. The method called get_results will get various data from your database. To output the data, a simple foreach() loop is all you need.

$results = $db->get_results("SELECT name, email FROM users");

foreach ( $results as $user ) {
    echo $user->name;
    echo $user->email;
}

Select a column

If you need to get a column, you can use the get_col method. The second parameter is the column offset.

foreach ( $db->get_col("SELECT name,email FROM users",0) as $name ) {
            echo $name;
}

Debug

When something doesn’t work as expected, ezSQL has a great method to perform some debugging. Not surprising, the method is called debug. When called, the method will display the last query performed and its associated results.

$db->debug();

I hope you enjoyed this article and that you’ll use ezSQL in your future projects. It’s a great tool which was very helpful for me many times!

  • http://www.techcircles.com Arafat Ashraf

    Thanks for nice information. I am currently using AdoDb for database abstraction layer for my personal projects. I will definitely try this one for my new project. It will also be helpful for me as WordPress is using it.

  • http://anggunpribadi.com anggun

    it’s simpler to fetch the data i guess

    but the download link doesn’t work for me it’s result me “Sorry, no content was found…” message.

  • http://dancunningham.co.uk Dan

    It would seem the link to ezSQL is broken, Can anyone else please supply a download link to the class in question?

    Cheers
    Dan.

  • http://dancunningham.co.uk Dan

    For anyone intertested i’ve found a newer version (v205) http://justinvincent.com/ezsql

    Cheers
    Dan.

  • Gustavo

    I would recommend PHP’s PDO (available since 5.1.0) instead of ezsql

  • http://siciarz.net/ Zbyszek

    Umm.. PDO has been there for quite some time and it is simple, no matter what you say. What’s the point of yet another DB abstraction?

  • http://milki.erphesfurt.de/ mario

    That’s not an abstraction layer. It’s a run of the mill wrapper which adds nothing new nor simplifications. Also it follows the outdated scheme of escaping and concatenation SQL data with commands. There’s little advantage over mysql_query. Avoid these yesteryear methods and start with PDO and parameterized SQL.

  • http://www.shiftedwork.de/blog Daniel S

    Great Tip, but as the comments before, i recommend PDO, too. I love the easy way to insert arrays. I’ve never found another solution, so i keep the use of pdo.

    Sorry for my bad english. Greetings from Germany.

  • http://www.effi10.com Cédric

    Hi

    ezSql is a very good solution to increase productivity on small projects (and “less small” !) but needs a strict use because of risks of code injection.

    So take car if you use it with variables, because usually we use clear SQL statements with it.

  • http://7pl.info procek

    I think this is worse than PDO. PDO is universal OOP for many SQL databases…

  • Matthew

    ezSQL Considered Harmful.

    Everyone wants SQL to be easy, and indeed this script does seem like an easy way to avoid having to change your SQL function calls if you change databases. But that’s it.

    ezSQL does not offer any way to parameterize your queries – or indeed to protect your queries from SQL injection at all — least of all in a system-portable fashion.

    What’s this ‘parameterized queries’ thing? Well, Basically, instead of “insert into table(name) values ($_POST[name])”, in place of the value you leave a placeholder, and then supply the value in a separate variable via a function call. This ensures that a user can’t insert some special character that’s smarter than whatever escaping function you’re using, and do stuff like:

    * Delete everything in your database
    * Add a new admin user
    * Expose all users’ passwords

    Please wise sir, how can I avoid this terrible fate?

    Use PDO! Especially, use PDO with parameterized queries!

    How to use PDO:
    http://www.phpro.org/tutorials/Introduction-to-PHP-PDO.html

    Section on parameterized queries (“Prepared Statements”):
    http://www.phpro.org/tutorials/Introduction-to-PHP-PDO.html#10

    • bpr

      Yes I have to agree,there is not much to say.
      PDO is available in PHP5 and should be used at it is the homemade version of this ezSQL. however, ezSQL is not bad, but hoster in this century offer php5 on their services so there is no need for ezSQL : >

      • mario

        PDO is also available for PHP4. It’s emulated and only supports mysql, pgsql and sqlite2, but at least you can use almost all of its features – even on outdated webserver setups. See xpdo.org or upgradephp

  • http://www.liveanime.org helium

    Alternatively use any of the above and abstract/ add features yourself???

  • James

    You all right, PDO does the best job but to ensure maximum security, adding HTML purifier and PHP filters, you can be sure your code can withstand any injections and XSS.

    HTML purifier and PHP filters are also easy to implement with just one or 2 lines of code. They are require for use in every data input and forms.

    • James

      …and if you want to ensure better security for your web server and better performance, I heard Hiawatha web server was the most advanced and secure in the world!

      nginx, lighthttp and cherokee (I not sure how will the latest fare since some benchmark on 0.99.xx found be to slower than nginx as compare to earlier version) for lightweight and high performance web server

      and G-Wan use C script that claims to have the ability to outperform 1,000,000 times faster than any other web servers on earth. From the test, any static page can be retrieve from 1-10 milliseconds.

  • http://charmingwp.com Ahmad

    i have to agree I would recommend PHP’s PDO

  • http://windowspatchmanagement.com Carl

    Excellent guide mate, honestly I have never use it, but it looks very simple and honestly I am not so good with SQL.

  • http://www.torquecars.co.uk Brian

    Interesting, but as I run my own servers I get full control over the database type intalled so I’d only consider this if it made things faster.

    Is there a speed advantage or disadvantage with ezsql?

  • James

    Query to any database doesn’t improve much unless you are using faster web server that use less memory consumption, the web server affect OS performance. What web server you are currently using?

  • http://www.highergroundcreative.co.uk Stuart

    I have used ezSQL for building custome CMS’s for over a year and it has been such a time saver.

  • http://richardwhiuk.com Richard Whitehouse

    Also the power supposedly provided with this is non existant. Why? Because SQL syntax is non standard.

    For example:

    $var = $db->get_var(“SELECT count(*) FROM users”);

    Is MySQL specific. If you want to do this in other database engines, it will differ. The same goes for limit, joins, etc etc.

    So you don’t have to change mysql_ -> pgsql_. Wonderful. But you do have to change all your queries. Damm.

    Also, you are perpetuating a myth that changing db engine is common – it isn’t – or at least not to a similar style of engine. Changing from MySQL to BigTable or something along those lines is in vogue atm but this is not something you can change by twiddling the connections string..

    Finally, PDO is a much more mature engine than this hacked reimplementation of ADODB, PDO etc etc etc. Every CMS literally rolls it’s own.

  • http://www.tutdepot.com/ Olaf Lederer

    There is nothing wrong with using ezSQL if you use ezComponents.
    It’s a mature library and their database abstraction layer has stable support for Oracle and MS SQL server, for PDO these drivers are still experimental ;)

  • http://www.surgeforward.com Surge

    Security is a big issue when dealing with databases, care needs to be excersized when dealing with “easy” coding so as not to allow signal injection. Thanks for the post.

  • http://www.furniturejakarta.com Yusuf

    I’ll try this for my new website, many thx

  • http://shay.co/ Shay

    PDO is the solution for the problem, as mentioned by many others.
    There is no reason for using this kind of php-implemented abstraction layer when there is a much better implementation, not only that but PDO is far more popular also.

  • Sohan

    conclusion ?? please tell which is faster PDO or any other wrapper ? speed is critical in my website …

  • Bhavesh

    conclusion ?? please tell which is faster PDO or other wrapper ? speed is critical in my website ..

  • http://www.wildfiremedia.com.sg James L.

    Sohan, if performance is critical to your site. You should NOT be looking into MySQL, instead consider using nosql (Not Only SQL): MongoDB, CouchBD, Cassandra (the easiest but limited features), HBase and many others. Although they require root access to administrate the web server but they are much better at scalability than MySQL except that some are not mean for MySQL replacement. Ever seen Amazon, Facebook, Google, Digg using MySQL?

    I can recommend MongoDB which is I’m learning now, although there are much better performance than Mongo but no point having the greatest performance if I cannot do a basic query, so Mongo fit my requirements. Just sign up for VPN server and you are ready to go.

    The better idea for using nosql is no SQL injection and more fun! I have started to look at alternative DB if I want scalability with distributed data and is much faster than MySQL. We are already in Data Age, so it critical that we walk out of our comfortable zone and be well prepare in handling unforeseeable scenario.

    • sohan

      yaap James L., my site will deal with heavy traffic so i m confusing wht to use Mysql or other. However i m thinking about Cassandra, because some of such websites uses it. but the problem is that, i m worry about Cassandra because many hosting companies doesn’t provide it. can u refer any good wrapper or i should go for Mysql directly ??

      • http://www.wildfiremedia.com.sg James L.

        Hi, read my comment above. Thanks.

    • Mariano

      But comparing PDO and ezSQL… which is faster (and/or more efficient)? Sometimes you just have to stay with mysql in a project…

  • http://www.wildfiremedia.com.sg James L.

    Sohan, do you need query by range and database as a document type which should make sense to your site? You should go with MongoDB instead. You will have to set up VPS and running MongoDB yourself or there are hosting for MongoDB.
    To lighten up your server load and bandwidth, you may consider signup a MaxCDN to offload your images, CSS, javascript so it won’t cause much strain to your server.

    Cassandra may have further plan to rewrite from scratch, it not worth the effort to start Cassandra in production now. I gave up this after experimenting for days, I’m glad MongoDB is more natural for every purposes we can store like a document as such Facebook profile where each user have different types of data and info under one ID generated by MongoDB. You will truly love the way how it store.

    For data replication and backup is only a few line of Javascript command, with Journal, it is much better and fun than looking back to mySQL. Yes, please consider MongoDB.

  • http://enjoyadultwebcams.com/ Mark L’oghen

    I love it!
    There are four important reasons using ezSQL:

    You will generate clean code.
    You will reduce development time.
    Debugging is fast and easy.
    Migration to various DB is easy.

  • Paul

    There’s a class here that extends the MySQL version of ezSQL to give it the prepare function that WordPress uses if anyone wants it – https://github.com/pau1phi11ips/ezSQL/blob/master/mysql/ez_sql_extend.php

  • Mittul Chauhan

    I use ezSQL for smaller projects and for bigger projects PDO of course