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:
1 2 3 4 | include('lib/ez_sql/ez_sql_core.php'); include('lib/ez_sql/ez_sql_mysql.php'); |
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:
1 2 3 | $db = new ezSQL_mysql('username', 'password', 'databasename', 'localhost'); //user, password, database name, host |
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:
1 2 3 | $db->query('query'); |
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:
- Insert query :
1 2 3 4 | // Insert into the database $db->query("INSERT INTO tablename (id, name) VALUES (NULL,'Creativedev')"); |
- Update query :
1 2 3 4 |
3.SELECT Queries:
Let’s see the queries used to select the data from database
- 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:
1 2 3 | get_row($query, $output_type, $row_offset); |
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 2 3 4 | $rowdata = $db->get_row("SELECT name FROM tablename WHERE id = 1"); echo $rowdata->name; |
- 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:
1 2 3 | get_col($query, $column_offset); |
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 2 3 4 5 6 | $coldata = $db->get_col("SELECT name FROM tablename",0); foreach($coldata as $column){ echo $column; } |
- To get Single Variable
Let’s see the syntax of get_var function first
SYNTAX:
1 2 3 | get_var($query, $output_type, $row_offset); |
Explanation:
Parameters are same like get_row explained above.
Example:
1 2 3 4 | $vardata = $db->get_var("SELECT count(*) FROM tablename"); echo $vardata; |
Suggested Read: Transaction in MySQL
- To get multiple records
The get_results function in ezSQL class is used to retrieve the multiple records from the database and
SYNTAX:
1 2 3 | get_results($query, $output_type); |
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
1 2 3 4 | $results = $db->get_results("SELECT * FROM tablename"); foreach ( $results as $data ) { echo $data->name; } |
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.
1 2 3 | $db->debug(); |
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->vardump — Print 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.
Comments (3)