Back to Top

How to use ezSQL with example

ezSQL

ezSQL is an open source PHP database class which was written to handle SQL queries easy and efficient manner in PHP.ezSQL class is very simple and easy to use for mySQL, Oracle, SQLite (PHP) any type of queries in PHP.

Many developers are using still traditional mysql_query to apply SQL queries the database but ezSQL is simple, reusable and code consistent so I would commend to use it.And I am sure after reading this article, you will go with ezSQL rather than traditional mysql_* functions.

Main Reason behind writing about ezSQL is WordPress is also using ezSQL and I have written around more than 90 articles in WordPress but never get the chance to write backbone of WordPress Queries. So Here I am 🙂

Installation

First of all, Download ezSQL library. Now create database configuration file and include so that ezSQL files into this so no need to include ezSQL files in every page.

Configuration File:

Here in above code, you can see two files are there which you can download from here.One file is core which have core coding and MySQL file is for mysql database related functions of ezSQL.

Read about: EXISTS and NOT EXISTS in MySQL

After configuration,connection to the database:

After you create an ezSQL object, you can use the object for your database queries.Now you can run any types of queries to your database using ezSQL.

ezSQL provides very nice feature is it allows to use the various database so now you can easily migrate one to another database or can use multiple databases.
Now, Let’s see the SQL queries examples using ezSQL and you can get

Examples:

First of all see the basic examples of SQL queries like Insert, Update and Select. I will show the functions you can use for most basic needs of database queries.

General Query:

Here I am going to share general query function of ezSQL

SYNTAX:

The Query function is used to execute any query like SELECT, INSERT, DELETE, UPDATE.You can also use for CREATE, ALTER, TRUNCATE and DROP SQLqueries,

Here, Let’s check how to use Insert or Update Query:

  1. Insert query :

  1. Update query :

3.SELECT Queries:

Let’s see the queries used to select the data from database

  1. To get row

For fetching only row data, the get_row method is used to select a row from your database. Let’s see the get_row function which executes select query and display the row data as result

SYNTAX:

Explanation:

$output_type and $row_offset are optional parameters.$output_type is used for the format of output. and defaults to ‘OBJECT’. $row_offset is used to get desired row.

OBJECT – result will be as an object.
ARRAY_A – result will be as an associative array.
ARRAY_N – result will be as a numerically indexed array.

Example:

  1. To get a column:

The get_col function is useful when you want to get only a column data.The get_col function is only used to retrieve specific values ​in a column from the database.

SYNTAX:

Explanation:

$column_offset is the optional parameter and it is used to retrieve specific column.If there is more than one column, this function is used to returns only one column data which will be either first column or any specific passed into the $column_offset variable.

Example:

  1. To get Single Variable

Let’s see the syntax of get_var function first

SYNTAX:

Explanation:

Parameters are same like get_row explained above.

Example:

Suggested Read: Transaction in MySQL

  1. To get multiple records

The get_results function in ezSQL class is used to retrieve the multiple records from the database and

SYNTAX:

Explanation:

It returns the result sets in following three formats and if no data, NULL value will be return.

OBJECT – result will be as an object.
ARRAY_A – result will be as an associative array.
ARRAY_N – result will be as a numerically indexed array.

If any error occur or no matched record found, it will return an empty array.

Example

Debug:

When you want to debug the last query you performed, an ezSQL class has the function to check queries if not work in some cases. It displays last query and all associated results.

ezSQL in WordPress:

WordPress is the very well known CMS and almost all who have sites tried WordPress once and if you’re a developer you might have idea that WordPress uses wpdb for the database to apply database queries. $wpfb is global variable in WordPress and is uses an ezSQL

Some other Useful Functions of ezSQL for database queries:

$db — Initiate new db object.
$db->vardumpPrint the contents and structure of any variable
$db->select — Select a new database to work with
$db->get_col_info — Get information about columns such as column name

In this article, ​you learned about the very useful class ezSQL. We have tried to set a good examples to the explain useful function to demonstrate clearly.

Share:
Share on Facebook2Share on LinkedIn0Tweet about this on TwitterShare on Google+0Buffer this pageDigg thisShare on Reddit0

Comments (3)

  1. Hello Bhumi,

    I am using ezSQL for database queries and connection and sometimes it gives data and sometimes it return no results.

    Here is the debug code

    Database table has records and it returns records if I run file after few minutes.

    1. Hello Ankit,

      I have checked your issue and added ticket about your query on GitHub account of ezSQL. And here is the response I got from them:

      You probably need to talk to your hosting provider as it sounds like the infrastructure you are using is flakey. Or there is a flakey network connection. Or mysql keeps dying on connecting for some reason.

      For example a database connection that sometimes works and sometimes doesn’t. Or perhaps a load balancer that is polling multiple machines with multiple databases and some of the machines do not have data on them.

      To be honest there are many possibilities for this type of behavior but I think ezSQL itself is very unlikely.

      1. Thanks for your response. I have updated a max_allowed_packet in my.conf and worked.

Leave a Reply

Your email address will not be published. Required fields are marked *

Most Popular Posts

How to allow more MIME types in WordPress

Posted on 4 years ago

Bhumi

How to use HTML5 download attribute?

Posted on 2 years ago

Bhumi

How to use jQuery Cookie

Posted on 5 years ago

Bhumi

Table layout using DIV tags

Posted on 5 years ago

Bhumi

Storing Objects in Html5 Local Storage

Posted on 1 year ago

Bhumi