PHP:EZSql class, manage your database connection easily

PHP:EZSql class, manage your database connection easily
by Janeth Kent Date: 11-04-2013

EZSql is a php class to make it very easy to deal with database connections, there’s already an ezsql documentation:

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 theaddslash()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.

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.

 
by Janeth Kent Date: 11-04-2013 hits : 6781  
 
Janeth Kent

Janeth Kent

Licenciada en Bellas Artes y programadora por pasión. Cuando tengo un rato retoco fotos, edito vídeos y diseño cosas. El resto del tiempo escribo en MA-NO WEB DESIGN AND DEVELOPMENT.

 
 
 
Clicky