How to use ezSQL

I guess its only appropriate for me to write an introductory article about ezsql since I’m using it on most of the articles that I create.
Just so you know, there’s already an ezsql documentation and an article written by Jean Baptiste Jung on Catswhocode:

Yeah, its already been written before so why the heck I’m still writing a how-to article on it. I might not have said this before but the main reason I’m writing a blog is for me to better understand the things that I have learned. Because I believe that you never really know something until you write or talk about it.

 

Requirements

 

Installation

First you need to download wampserver and ezsql library. I’ve already included the links in the requirements section.

Next, setup your test folder. Then create your database configuration file so that you won’t be including the ezsql files on every page that you create. The database configuration file will include the ezsql files, so the only thing that you will need to include on your pages is your database configuration file. This is what your database configuration file should look like.

<?php
include('ez_sql/shared/ez_sql_core.php');
include('ez_sql/mysql/ez_sql_mysql.php');

date_default_timezone_set('Asia/Manila'); //timezone

$db = new ezSQL_mysql('root', '', 'orgbpls', 'localhost'); //user, password, database name, host
?>

As you can see there are two files associated with the ezsql library. One is the ez sql core, and the other is the ezsql mysql. The ezsql core as the name suggests contains the core functionalities of ezsql. The ezsql mysql file contains the functionalities needed to query the mysql database. Database specific components as they might call it.

After creating the database configuration file, all you have to do now is to include it on your pages.

 

Create new records

To create new record on a table we call up the query function which takes up a single argument, the query that we need to perform in this case an insert query.

$user = 'bamboo'; $password = '1234'; $hashed_password = md5($password); $create_user = $db->query("INSERT INTO sys_users

SET UserID='$user', strPassword='$hashed_password'");

 

Updating records

We call up the same query() function to update existing records on our table. But this time we need to supply it with an update query. The code below will change the password of the user bamboo from the hashed version of 1234 to the hashed version of 6789.

$user = 'bamboo'; $password = '6789'; $hashed_password = md5($password); $update_user = $db->query("UPDATE sys_users

SET strPassword='$hashed_password' WHERE UserID='$user'");

 

Deleting records

Again, we use the query() function to delete records from the tables. The only thing that’s changing is the query that we supply. This time use a delete query.

$user = 'bamboo';
$delete_user = $db->query("DELETE FROM sys_users
WHERE UserID='$user'");

 

Note: the query() function returns 1 if the query is successfully executed on the database, and 0 if its not. Which means that you can actually use the value that it returns to determine if you have made a change into the database or not.

 

Selecting a variable

To select a single value from a field in a table row we use the get_var() function. The code below returns the department to which the user belongs. Make sure that you only select a single field in your table since only the first field specified in the query will be the one which will be returned if you select multiple fields.

$user = 'tcollector'; $user_department = $db->get_var("SELECT intDepartment

FROM sys_users WHERE UserID='$user'"); echo $user_department;

 

Selecting a row

To select a single row we use the get_row() function.  This allows us to select multiple fields in a single table row. To get the values in the specific rows you have to assign the results to a variable, in this case the variable is called $user. That is where the results are being stored whenever the query that you specified returned something. All you have to do now is to access the different field values by using their fieldname. In this case the fieldnames are UserID, intDepartment and intRole.

$user_id = 'tcollector';
$user = $db->get_row("SELECT intDepartment, UserID, intRole 
FROM sys_users WHERE UserID='$user_id'");

echo $user->UserID.'<br/>';
echo $user->intDepartment.'<br/>';
echo $user->intRole.'<br/>';

 

Selecting multiple rows

To select multiple rows we use the get_results() function. This returns an array so we need to use a loop in order to get the values that we want. The idea is the same with selecting a single row, the only difference is that you need to use a loop through the result that is being returned.

$users = $db->get_results("SELECT * FROM sys_users");
if(!empty($users)){
?>
<table>
	<thead>
		<tr>
		<th>UserID</th>
		<th>Department</th>
		</tr>
	</thead>
	<tbody>
	<?php foreach($users as $k=>$v){ ?>
		<tr>
		<td><?php echo $v->UserID; ?></td>
		<td><?php echo $v->intDepartment; ?></td>
		</tr>
	<?php } ?>	
	</tbody>
</table>
<?php } ?>

If for some reason you want to know how many records has been returned you can use the num_rows() function. All you have to do is to call it right after you perform a select query. The code below will output the number of records returned in the last query that you have performed.

$users = $db->get_results("SELECT * FROM sys_users");
$number_of_records = $db->num_rows;
echo $number_of_records;

 

Escaping illegal characters

You can also escape illegal characters from a string using ezsql’s escape() function. This function just adds up backslashes to any illegal characters that it finds on the string that you supply. Its basically the same with the addslash() function in php.

$super_password = $db->escape("di$ i$ super's password%z");
echo $super_password;

 

Debugging

There is also a function which you can use to debug your queries if they aren’t behaving in a way that you expected. To use it just call the debug() function, it doesn’t need any arguments as it uses the last query that you have performed, just make sure to call it right after performing a query.

$user = 'tcollector'; $user_department = $db->get_var("SELECT intDepartment

FROM sys_users WHERE UserID='$user'");

$db->debug();

Just to show you an example of how useful it is. It returns the actual result of the query that you have performed complete with all the fields that you have selected along with their respective data type. Cool right?

image

 

Conclusion

I guess that’s all there is to it. You’ve learned the basics of ezsql. How to perform queries and select records from the database easily.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s