My name is Jean-Baptiste Jung and I’m a 29 years old web developer and professional blogger. I was born and raised in Paris, France and I now live in Belgium with my wife and our adorable cat. I first used the internet in 1998, built my first website in 2001 and finally started to work as a professional web developer in 2005. In 2010, I left my job and created my own web development studio. jb is a DZone MVB and is not an employee of DZone and has posted 55 posts at DZone. You can read more from them at their website. View Full User Profile

PHP: Fast and easy SQL queries using ezSQL

10.20.2011
| 7683 views |
  • submit to reddit

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!


Source: http://www.catswhocode.com/blog/php-fast-and-easy-sql-queries-using-ezsql

Published at DZone with permission of jb j, author and DZone MVB.

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)

Tags:

Comments

Mario T. replied on Thu, 2011/10/20 - 9:49am

Fast and easy? Retarded is more like it.

This class is the reason WP had so many SQL exploits over the years. It follows the very dated manual escaping methodology. Why anyone would use a library without placeholder or bound parameter support is beyond me. That's not exactly easier to use.

 

Rehman Khan replied on Sat, 2012/02/25 - 4:46am

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.

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.