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 querymethod. 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!