How to read excel files in php

In this tutorial I’m going to show you how you can use the php library called PHP Excel Reader to read excel files using php. Yes, things like excel readers for php has already been written by smart people in the past so I’m not going to show you how to build an excel reader from scratch. It’s not quite my level yet, I still consider myself a beginner. Anyway, let’s get to the main topic.

 

Requirements

 

Sample Spreadsheet

First you need to have a sample spreadsheet that you could  work on. Just make it simple, preferably a 2-column sheet with a  few data on it.

 

Setup Working Directory

If you haven’t already downloaded the php excel reader library go ahead and download it and then setup your working directory. Also put your sample excel file on that directory for easy access.

 

Modifying the Library

The php excel reader is a pretty old library. You need to open up the excel_reader2.php file and modify line 916. From this:

$this->_ole =& new OLERead();

To this:

$this->_ole = new OLERead();

You’ll get an error that assigning the return value of new by reference is deprecated especially if you’re using PHP Version 5.3 and above.

 

Main Story

Okay so let’s get to the main story. The documentation for this library is actually pretty useful so if you want to do some tinkering then you might as well read the official documentation. Almost all of the methods that I’ll be talking about is based on the documentation and the example that comes with the library. I recommend that you check out the example first before continue reading. If you can’t figure something out or if you want to do something that isn’t mentioned in the documentation then come back here. I cannot promise that I have the answers but I can assure you that I will cover the basic stuff that you may want to know on reading excel files.

php excel reader documentation

The example.php file is a good place to start, go ahead and make a backup of it so that you can safely modify your copy.

First, this line tells us that this file is going to use the php excel reader library:

require_once 'excel_reader2.php';

It then creates a new object of the php excel reader class:

$data = new Spreadsheet_Excel_Reader("Book1.xls");

You need to specify the file name as the argument. If the php file is in the same directory with the excel file that you’re trying to read then the example above is going to work fine.

Lastly, you need to turn off error reporting for notices. Try to remove this line and you’ll see some orange stuff in your page.

error_reporting(E_ALL ^ E_NOTICE);

Now that you’re done importing the needed files in your php file. It’s now time to show you some of the methods that you can use.

  • val() – This method requires 2 arguments and 1 optional argument. The first argument is the row number. The row number starts with the number 1. Row number 1 in most cases is the custom header name(Eg. student name, course, school, etc.) so the starting index for the row number is 2. The second argument is the column name. It is standard in excel (Eg. A, B, C, etc.), this is really useful because we can just put the column names in the php code. The third is the optional argument sheet index.  Sheet is zero-indexed. First sheet is 0, second index is 1 and so on.
$data->val(row_number, column_name, sheet_index);
  • dump() – This method is a beast. What it does is just to dump the whole content of the first sheet if you don’t specify the third argument. The first argument requires a boolean data type, true if you want to output the row numbers(1, 2, 3, etc.) and false if you don’t want it. The second argument is whether to display the column letters, just set it to true if you want it to be displayed. The third argument is the sheet index, by default it’s 0 and you don’t even need to specify it explicitly.
$data->dump(row_numbers, column_letters, sheet_index);
  • rowcount() – This method returns the total number of rows of the sheet.
$data->rowcount();
  • colcount() – This method returns the total number of columns of the sheet.
$data->colcount();

 

Now that you know the methods that you can use, you might as well try to experiment a bit.
Dumping the whole excel sheet is pretty useful but what if you want to do something with the data that’s in it? For that we could use a while loop to loop though all of the data in the current sheet.

First you need to know the total row count. I added 1 because the row is not zero-indexed, if I do not add 1, the loop that we will be creating later is just going to read up to the second to the last record:

$num_row = $data->rowcount() + 1;

Specify the index that were going to start with, as I have said earlier the row count starts with 2.

$index = 2;

Then the table heading, since the first row in our spreadsheet is the header we will just have to use it as the header for our table:

<table>
<tr>    <th><?php echo $data->val(1, 'A'); ?></th>    <th><?php echo $data->val(1, 'B'); ?></th>
</tr>

After that, use a while loop to repeat the table row as long as the index is not equal to the total row count.

<?php while($index != $num_row){ ?>
   //table rows
<?php } ?>

And for the table rows the only thing that’s changing is the index. If you want to do something with the data later on, this is the perfect time to be storing them in an array which you could loop through later on. If you’re going to save them into the database, you can also execute the query here.

<tr>
   <td><?php echo $data->val($index, 'A'); ?></td>
   <td><?php echo $data->val($index, 'B'); ?></td>
</tr>

Finally, increment the index after creating the table row:

$index++;

 

Output

Here’s the sample output. The one on the top is the output for the dump, and the one below is the output for the while loop that we created:

image

 

Conclusion

I guess that’s it for this tutorial. In this tutorial you’ve learned how to use the php excel reader library to read excel files in php. Making use of this library will make life easier for you if you need to do some operations to the data in the excel file like saving them into the database our outputting them in a customized fashion. If you have questions, suggestions for this tutorial, or if there are other details that you think I have missed please feel free to use the comments section below thanks for reading!

How to create a raffle program

In this tutorial I’m going to show you how to create a raffle program which will randomly select records from a mysql database.

 

Requirements

  • jQuery
  • Wampserver

 

Initial Script

First, create a new file and call it raffle.php. This is where we will put the codes for randomly selecting records using javascript.
Declare an array which will temporarily store the names of the participants and winners. Then initialize the winner_index to 0. If you have already spent some time in programming, you know that winner_index is a way to keep track how many winners were already selected on the current session. This is also a way of making sure that we store each of the winners on a different index in the array.

var participants = [];
var winners = [];
var winner_index = 0;

Once the document has been fully loaded, we initialize the total number of names to be displayed to 30. Then we create an event handler once btn_random is clicked by the user. We haven’t created the btn_random yet since where still on the script so hold your horses and keep reading.
Clicking the btn_random button will cause a post request to the server and this is through the $.post() function in jQuery. It’s a convenience function primarily used to post or load data from the server. In this case, were loading the data from loader.php a script which we will create later. But what it does is select random records from the database and then returns a JSON string which is then used by our raffle program. As you can see in the code, we used $.parseJSON() function before assigning the data to the array of participants. This is used to convert the JSON String into a Javascript Object. This is to prevent the data from being treated as a string. Finally, we execute the RunRaffle() function which takes up 2 arguments: the total number of names to raffle and then the array of names to raffle.

$(function(){
	var total_count = 30;	
	$('#btn_random').click(function(){
		$.post('loader.php', function(data){
		participants = $.parseJSON(data);
			
		RunRaffle(total_count, participants);
	                 });
	});				
});

 

Loading the data

I guess its only proper to first show you how to load the names that were going to raffle from the database before I go through the meat of the program.

Create a new file and name it loader.php. Include your database configuration. Then set the character set to UTF8, this makes sure that we don’t get any errors when dealing with names which are made up of characters which are not very friendly when you retrieve them from the database.
Nex, we select the records randomly from the database and that is by using the RAND() function in mysql. If you’re interested with the technicalities just visit the link. But basically the RAND() function is used to randomize the results which are being returned from a query but its only effective if there are a hundred records or more. Its almost always certain that this function will select the same records over and over again but only in a different order. If say you only have 30-100 records. And note that RAND() function is actually a very expensive operation when it comes to performance so don’t use it on production. But its perfectly fine if you use it on just raffle programs or similar operations.

<?php

require_once('seminar_config.php'); $db->query("SET CHARACTER SET utf8"); $select_random = $db->get_results("SELECT participant FROM tbl_participants

WHERE state = 1 OR state = 4 ORDER BY RAND() LIMIT 30");

 

?>

We then loop through the records and assign it to an array. As you can see we don’t actually need to assign an index to the current record since php does this automatically for us, that’s just sweet! After were done looping through all the records we just use the json_encode() function to convert the $data array into its JSON String representation. There’s also a note from the php.net site that this function only works for UTF8 encoded data so setting the character set to UTF8 in mysql is  actually really useful. Yes you can try not setting the character set to UTF8 and the function will still work, but not for all characters. If json_encode() doesn’t understand it, it will probably be set to null and you’ll get some null values on your JSON string.

if(!empty($select_random)){
	foreach($select_random as $k=>$v){
		if($v->participant != null){
			$data[] = $v->participant;
		}
	}
	echo json_encode($data);
}

 

Meat

After loading up the data that we need its now time to do the code for the presentation of the raffle. The visual signal that will let the audience know that this is actually a raffle program. Because you can’t just pull out random names from the database without any visual impact. And were using jQuery for that purpose, all we have to do is to present a specified number of names. In this case we want 30 names to show up on the interface, then we just change the background color of its container in a successive fashion. There are 3 elements which makes this program tick:

  • Math.random() – although we already randomize what were selecting from the database, we also do this on the client side for presentation purposes.
  • .next() – jQuery’s .next() function which is basically used to determine the next element after the current element that is being selected.
  • setTimeout() – a Javascript function which is used for delaying the execution of another function. In the raffle program it is used to achieve the effect of slowly decreasing the speed in which the light is looping through the names while time passes by.

Declare the RunRaffle() function:

function RunRaffle(intCount, people){
   //magic happens here
}

Inside it we first initialize where we are going to append the list of names. In this case were appending it into an element which has the id of raffle. This element is an ordered list. After that, we just empty the current contents of the ordered list. This is to make sure that we don’t populate the whole interface with a bunch of names from the database.  Don’t worry we’ll also be writing all the html later.

var jRaffle = $("#raffle");
jRaffle.empty(); //this clears all the contents of the ordered list

Next, we just fill the ordered list with the names that we have fetched earlier from the database. But before we actually append it into the list we first check whether its null and that its not an existing winner. This is some sort of an assurance that we don’t put the names of the winners on the list of names to raffle in case the query failed us.

for (var intI = 0 ; intI <= intCount ; intI++){
			
if(people[intI] != null && check_existing(winners, people[intI]) == false){
	jRaffle.append("<li><div class='grey'>" + people[intI] + "</div></li>");
}
}

Next, we change the background color of the first name in our list.  Then Initialize the pause time, the pause time is the amount of time that we are going to run the program. Finally, the intDelay is the amount of time used to jump from one name to another.

var jCurrentLI = jRaffle.find("li:first").addClass("on");  //find the first name that was appended on our list
var intPause = 40; //set initial value of pause time
var intDelay = (4500 + (Math.random() * 2000)); //get time to wait before chaining pause time

Next, create an anonymous function and assign it to a variable called Ticker.

var Ticker = function(){

}

Inside it we define the next name to be selected, if we don’t have anything else on the list we go back to the first name on our list.

var jNextLI = jCurrentLI.next("li"); //next name to highlight
if (!jNextLI.length){ //check whether jNextLI has something in it     jNextLI = jRaffle.find("li:first"); //go back to the first name
}

Then we remove the highlight from the current name and add the highlight to the next name.

jCurrentLI.removeClass("on"); //remove highlight from current
jjNextLI.addClass("on"); //add highlight to next

Then check  whether to make the chaining slower. Remember that the higher the number of the variable intPause, the slower the movement from the current name to the next name will execute. This means that the intPause variable effectively determines the next winner after we reach a certain value.

jCurrentLI = jNextLI; //for the next iteration
if (intDelay > 0){
	intDelay -= intPause; //intDelay = intDelay – intPause
} else {
	intPause *= (1 + (Math.random() * .1)); //intPause = intPause * random value
}

Finally, when the intPause variable reaches a value of 400 or above we stop the current session of iterations. What we do here is to append the current winner to the list of winners and that is if the current name isn’t already stored in the array of winners, for this we used the check_existing() function which we will create later. We also submitted the name of the current winner to winners.php which basically just saves the name of the current winner into our database and changes its state so that it won’t be selected again. If for some reason a name is selected twice, we just clear out the container which stores the name of the current winner and we don’t add anything to the list.

if (intPause >= 400){
	iif(check_existing(winners, jCurrentLI.text()) == false){
		$('#winners').append('<li>' + jCurrentLI.text() + '</li>'); //append current winner to list of winners
		winners[winner_index] = $.trim(jCurrentLI.text()); //housekeeping  $.post('winners.php', {'winner' : $.trim(jCurrentLI.text())}); //save winner to database
		$('#divider').html(jCurrentLI.text()); //current winner
		
		winner_index++; //add 1 to the index
					
	}else{
		$('#divider').html(''); //clear the container for current winner
	}
				
} else { //while intPause is not >=400 increase delay for Ticker
	setTimeout(Ticker, intPause); //set delay for the ticker to be called
}

Then we just call the anonymous function through the variable Ticker. Note that were calling it just like a normal function.

Ticker();

 

Check Existing

For the check_existing() function it returns true if it found the name of the current winner in the array and false if it didn’t find it. It takes up two arguments, the winners array and the selected name.

function check_existing(arr,obj){
	 return (arr.indexOf(obj) != -1);
}

 

Commit Selection

The last thing that we have to do is to commit the selected winners to our database. Just change the state of whoever gets selected into something that’s not specified on the select statement.

$winner = mysql_real_escape_string($_POST['winner']); 

$db->query("UPDATE tbl_participants SET state=4 WHERE participant='$winner'");
$db->query("INSERT INTO tbl_winners SET winner='$winner'");

Yup I know what I said above might be confusing so here’s an explanation. For example on your query for selecting records you only select those whose state is 1:

SELECT * FROM tbl_participants WHERE state=1 ORDER BY RAND();

All you have to do in your update is to set a different state:

UPDATE tbl_participants SET state=4 WHERE participant=’something’

Then finally insert the name of the winner into another table. That is if you want to keep track of those who are selected. But you can probably just select all records with the state of 4 if you don’t want to do this extra step.

 

Markup

I explicitly delayed the html for the last part since this is the most boring part.

<div id="header">
	<img src="images/raffle_header.png" />
</div>
<div id="container">
<input type="button" id="btn_random" value="Select"> <!-execution->
<ol id="raffle"> <!-for viewing the names to iterate from–>
	
</ol>
</div>

<div id="bottom">
<div id="current">

	<p>Current:</p>
		<div id="divider"> <!—for viewing the current ->
		
		</div>

</div>
<div id="list">

<p>Winners:</p>
	<div id="winners"> <!—for viewing the list of winners->

	</div>
</div>
</div>

 

Sources

http://www.bennadel.com/resources/presentations/jquery/raffle.htm

CodeIgniter Beginner Tutorial 3– Playing with the Database

In this tutorial were going to play around some of the functions that are available in CodeIgniter that will allow us to manipulate the database ninja-style. Remember that you can always use the awesome codeIgniter user guide if you want to take a look at what other functions are available since I’m not going to tackle all of it in this tutorial. What I’m going to show is how to use some of those functions by implementing MVC.

And remember to check out the locally available user guide, it will be very helpful if you’re doing most of these offline. Which I also prefer since being connected to the internet tempts you to ask Google for everything.

 

Create the Model

First you need to create the Model which we will be working with. Nothing new here we just declared a new class for the mods model. If you don’t know what I’m doing then check out the first two tutorials on this codeIgniter beginner series.

class mods extends ci_Model{

}

Inserting Records

Inside the mods model create a function and call it insert_records(). In this function we specify a single parameter called $name. This argument for this parameter is going to be supplied in the controller in which we will load the mods model later on.

function insert_records($name){ $sql = "INSERT INTO testing SET name=?"; //query string //executes the query

//first argument->query string

//second argument, third argument,etc..->data bindings

$this->db->query($sql, $name); }

In this function were using something called the Query Bindings, its basically a safer way to execute queries on the database since the values which you supply to it are automatically escaped. This means that any malicious values supplied to queries won’t affect our database.

Next create a new controller and call it mods_controller.

class mods_controller extends ci_Controller{


}

Inside the mods_controller class create a function and call it inserts(). Inside the inserts() function we load the mods class. After the mods class has been loaded we can readily access its methods through the name of the class. And the method that were calling here is the insert_records() method, we just supplied a dummy name called blossom so that it will actually insert something on our table.

function inserts(){
	$this->load->model('mods'); //load the mods model class
	$this->mods->insert_records('blossom'); //call the insert_records() method
}

 

Updating Records

Updating records is pretty much the same with the inserting of records, but this time you’ll going to need a minimum of 2 arguments for your function. On the mods class create a new function called update_records, then supply 2 paramaters $name and $id. As usual we declare the sql query as a string. The only difference between the insert that we did earlier is that the second argument that you’re going to supply is actually an array composed of the values which will be supplied on the mods_controller controller.

function update_records($name, $id){
	$sql = "UPDATE testing SET name=? WHERE id=?";
	$this->db->query($sql, array($name, $id));
}

On the mods_controller all you have to do is to load up the mods model and call up the update_records() function. Remember to supply the values $id and $name. Supply a record which actually exist so that you can see if its really updating.

function updates(){
	$this->load->model('mods');
	$this->mods->update_records('jade', 2);
}

 

Deleting Records

Deleting records is still the same with inserting and updating records in the sense that were not using views for all of them. Though we can actually use views if we want to display the status of the query or something. Nothing new here, we just changed the sql statement to a delete query.

function delete_records($id){
	$sql = "DELETE FROM testing WHERE id=?";
	$this->db->query($sql, $id);
}

There’s also nothing new if we try to call the delete_records() function from the mods class. In here were trying to delete record with the id of 4 from our testing table.

function deletes(){
	$this->load->model('mods');
	$this->mods->delete_records(4);
}

 

Single Row Fetching

This time were going to use the Active Record Class to fetch a single row from our table.  First you have to create another function on the mods class and call it ar_getwhere() short for active record get where. The get_where() method accepts two arguments the first one is the name of the table, and the second is an array which stores the table field and value pair. In this case our table field is ‘id’ and the value is the argument which we will supply when we call the ar_getwhere() method.

function ar_getwhere($id){
	$where = array('id'=>$id);
	$query = $this->db->get_where('testing', $where);
	$row = $query->row();
		
	return $row->name;
}

If you want to include more fields in the where clause you can do so by adding more items on the $where array. In the example below we are including both the id field and the name field in our where clause.

$where = array('id'=>$id, 'name'=>$name);

This simply translates to the following query.

SELECT * FROM testing WHERE id='$id' AND name='$name'

Next we just have to call the function from our controller inside the mods_controller class.

function getname(){ $this->load->model('mods'); //load the mods class $row['name'] = $this->mods->ar_getwhere(1); //select * from testing where id is equal to 1 $this->load->view('mods_view', $row); //load the view in which we will display the name

//use the value returned from the query as the second argument }

And from mods_view we just simply echo out the name of the person with the id of 1.

<?php
echo $name; 
?>

 

Joins

You can just directly perform joins on your queries but the Active Record Class actually allows you to do this by using the join() function which takes 2 arguments, first is the table that you wish to join and the second is the common key between the table your selecting from and the table that you wish to join. Remember that you can use the join() function as many times as you would like but this ultimately depends on how many tables you wish to join together in a single query. After joining we just call the get() function. I can only guess what this does because I haven’t actually read about it. Maybe the get() function just puts the query and joins together in a single query. We then check if the number of rows returned is greater than 0. We won’t actually do anything if it returned 0 results. Next we just loop through the results and assign the rows returned to an array key.

function joins(){
	$this->db->select('name, clubname');
	$this->db->from('testing');
	$this->db->join('clubs', 'testing.id = clubs.person_id');
	$this->db->join('clubnames', 'clubs.club_id = clubnames.club_id');
	$query = $this->db->get();
	if($query->num_rows() > 0){
		foreach($query->result() as $v){
			$data['name'][] = $v->name;
			$data['clubname'][] = $v->clubname;
		}
	return $data;
	}
}

On the controller side of things we do what we have always done: load model, call function. The only thing that’s new in here is that we actually load a view called mods_view.

function joins_controller(){
	$this->load->model('mods');
	$data['info'] = $this->mods->joins();
	$this->load->view('mods_view', $data);
}

In the mods_view file we just loop through all the data stored in the $data array.

<table border="1"> <tr>   

<th>Student</th>   

<th>Club</th> </tr> <?php foreach($info['name'] as $k=>$v){ ?> <tr>   

<td><?php echo $v; ?></td>   

<td><?php echo $info['clubname'][$k]; ?></td> </tr> <?php } ?> </table>

 

Conclusion

That’s it for this tutorial. You’ve learned how to perform CRUD operations on your database using CodeIgniter. I hope you’re already getting comfortable on using the CodeIgniter user guide. It’s the ultimate reference believe me.

CodeIgniter Beginner Tutorial 1–Configuring CodeIgniter

In this tutorial I’m going to show you how to configure codeIgniter.


Requirements

 

Resources

Here are some of the links that you might want to check out in the process of learning how to do things in CodeIgniter.

  • Nettuts – Everything you need to know to get started with CodeIgniter – this is a pretty good tutorial. Though its already getting old, most of the concepts, methods and configurations that you need to do is still the same with the current version of CodeIgniter(v2.1.0)
  • CodeIgniter User Guide – A very good reference for the different methods and helper classes available in CodeIgniter. This is a very valuable resource once you know basics of CodeIgniter.
  • Nettuts CodeIgniter From Scratch – A series of screencast which discusses how to work with CodeIgniter. I recommend it if you’re not low on bandwidth.
  • Stackoverflow – If you get stuck in your quest on learning CodeIgniter you can just search for your problem on Stackoverflow. There’s a big probability that you’re problem has already been answered before.

 

Base URL

First you have to download CodeIgniter then unzip it on your web folder. If you’re using Wampserver its on c:\wamp\www if you haven’t configured anything when you installed Wampserver. If you’re using XAMPP your web folder is somewhere in c:\xampp\htdocs. But don’t take my word for it, I’m not a XAMPP user so you might as well do your research.

After unzipping you’ll have a folder structure similar to this.

image

Next open up the application folder and then the config folder.

application > config

Inside the config folder you will see the config.php file, open it up and set the base url. The base url is the root location that you will be working on. It looks similar to this.

$config['base_url']	= 'http://localhost:8080/tester/codeigniter/index.php/';

 

Database Configuration

Still on the same folder open up the database.php file. This is where you will set what database codeIgniter is going to use. Here are the stuff that you’re going to edit.

$db['default']['hostname'] = 'localhost';

$db['default']['username'] = 'root';

$db['default']['password'] = '';

$db['default']['database'] = 'database_name';

$db['default']['dbdriver'] = 'mysql'; //optional, you can use mysqli if you want

If you don’t know what you’re going to set just use localhost for the hostname, root for the username and an empty password. The database is up to you to fill out, just create a database for testing purposes if you’re just starting out with codeIgniter. You can create it in phpmyadmin or the mysql console.

 

Autoload

Next you need to specify the libraries, packages, helper files, custom configuration files, language files and models that you want to autoload in your application. This means that anything that you autoload in your application doesn’t need to be called every time you need to use it. To edit the libraries, helper files, and models that you want to autoload in your application just open up the autoload.php file which is in the application/config folder.
Below is an example where I included the database library in the libraries that will be autoloaded in the application. To do that just add database as an item to the libraries array.

$autoload['libraries'] = array('database');

There are other libraries available in codeIgniter which you can find on codeigniter/system/libraries folder. You can also add them to the libraries array but be sure you know how to use them and make sure not to load more than what you need.

image

You might also want to autoload some of the helpers that you might use later on. The url helper and the form helper. Helpers are used to make your life easier, if you translate it into coding this means that you get to write less code and do more.

$autoload['helper'] = array('url', 'form');

 

Default Controller

This is actually an optional configuration so you can just skip it if you want. But if you want to setup a default controller then read along.
The default controller is the controller that’s going to be loaded when you access the root of your application. In this case the root of the application points to the address below.

http://localhost:8080/tester/code_igniter/codeigniter/

To setup the default controller open up the routes.php file, still under the application/config folder and find the line similar to the following.

$route['default_controller'] = "site";

The default controller on the example above is the site controller, the site controller must be on the application/controllers folder otherwise this will fail. If you have a different name for your default controller, for example your default controller is index.php then change the default controller to index.

 

I guess that’s all you need to configure for now. Be sure to check out the resources that I’ve mentioned earlier.

Web Development and Programming Resources

 

VB.Net

Java

PHP

jQuery

Web Development


Podcasts


Microsoft

Newsletter

Video Tutorials

Pages

How to use prepared statements with mysqli

Prepared statements is basically a way to avoid sql injection on your script. If you want to learn more about prepared statements here’s a link to a Wikipedia article and stackoverflow question on best practices on using prepared statements.

To summarize the contents of the two links above it basically says that you need to use prepared statements in order to prevent sql injection attacks and if you need to insert, modify, or remove lots of data from you database. Prepared statements are basically faster than your average query since they are compiled before execution.

 

Methods

Here are a few methods that we need to use when using prepared statements.

  • prepare() – takes up a query as an argument. You need to mask the values that you want to supply to the query using the question mark symbol(?).
  • bind_param() used to bind parameters to a prepared statement. You specify how many arguments it would take by using a string which has the same length as the values that you want to pass in. For example if you want to supply a user id, password, department and role you need to input a string with a length which is equal to 4, and then the following arguments would be the actual values that you want to use on your query.
  • execute() as the name suggests this is used to execute the prepared statement along with the arguments that has been bind to it.
  • bind_result() – use this method to bind the results of a prepared statement into their respective variables.
  • fetch() – use this method to fetch the results that has been returned by the prepared statement. It doesn’t take up any arguments, just call it immediately after binding the results to their specific variables.

 

Inserting records

Next, let’s try to insert records. On line’s 1 to 4 we assigned the data that we want to insert to each of their variables. Next we try to check if the query is a valid query using an if statement. Note that we are using question marks(?) as a mask for the actual values that we want to insert in the query. And then we call up the bind_param() method. As you can see, we used “ssss” as our first argument this specifies that we are submitting for string arguments to the prepared statement. The next arguments are the actual values that you want to pass in. Make sure that they are of the same order with the fields that you used in your query. This means that if you have first set the value of UserID in your query then you also use it as first value that you bind. Just examine the example below if you’re confused with my messy explanation. Lastly, you execute it using the execute() method.

$user_id = "lily's"; $hashed_pw = md5("1234"); $department = 1; $role = "ROL3"; if($query = $db->prepare("INSERT INTO sys_users

SET UserID=?, strPassword=?, intDepartment=?, intRole=?")){

$query->bind_param("ssss", $user_id, $hashed_pw,

$department, $role); $query->execute(); }

 

Updating records

Updating records is almost the same with the inserting of records the only difference is the query that you are performing. Always remember that when calling bind_param, you use the actual values in the order that they appear on the query. In the case of an update query, the field that you use in the where clause will always be the last argument that you supply on the bind_param() method.

$user_id = 'yohasakura'; $hashed_pw = md5('9877'); $role_id = 'ROL9'; $dept_id = 3; if($query = $db->prepare("UPDATE sys_users SET strPassword=?, intRole=?, intDepartment=? WHERE UserID=?")){ $query->bind_param('ssss', $hashed_pw, $role_id,

$dept_id, $user_id); $query->execute(); }

 

Deleting records

Same story, different query. In a delete query often times we only need to supply one argument to the query that’s why I use just “s” as the first argument in the bind_param() method. And the second is the user id, the field the you used in your where clause.

$user_id = 'yohasakura';
if($query = $db->prepare("DELETE FROM sys_users WHERE UserID=?")){
	$query->bind_param("s", $user_id);
	$query->execute();
}

 

Fetching a single record

Same story, just used two additional methods namely bind_result() and fetch(). Note that when using bind_result() the order of the fields that you have selected in your query doesn’t actually matter, it is how you output the values which gives the meaning to it.

$user_id = 'tcollector'; if($query = $db->prepare("SELECT UserID, intRole, intDepartment FROM sys_users WHERE UserID=?")){ $query->bind_param('s', $user_id); $query->execute(); $query->bind_result($userID, $department, $role); $query->fetch(); printf("User: %s <br/> Role:%s <br/> Department: %s<br/>",

$userID, $role, $department); }

Also note that the %s is being substituted with the 2nd to the last arguments that you specify in the print_f() function, this is where the order of how you output things matters. But if you don’t want to take care of the order, its perfectly fine to do it this way

$user_id = 'tcollector';
if($query = $db->prepare("SELECT UserID, intRole, intDepartment 
FROM sys_users WHERE UserID=?")){

	$query->bind_param('s', $user_id);
	$query->execute();
	$query->bind_result($userID, $department, $role);
	$query->fetch();
	echo 'User: '. $userID.'<br/>';
	echo 'Role: '. $role.'<br/>';
	echo 'Department: '. $department.'<br/>';
}

 

Fetching multiple records

Same story, just added a while loop to loop through all the records that has been returned. This basically says that while a result is still being returned you need to output something. And yes, you can use the variables that was bind to output things out. Just make sure that when binding, you follow the same order indicated in your query. If you use SELECT * you might want to follow the order that is in your table, for example if UserID is the first field from the left then you also supply it as the first argument when using bind_result().

$department_id = 2;
if($query = $db->prepare("SELECT UserID, intRole, intDepartment 
FROM sys_users WHERE intDepartment=?")){

	$query->bind_param('s', $department_id);
	$query->execute();
	$query->bind_result($user, $role, $department);
	
	while($query->fetch()){
		echo $user.'<br/>';
	}
}

 

Conclusion

Okay I guess that’s it for this short introduction on how to use prepared statements in mysqli. Prepared statements is a great way to prevent sql injection on your queries, you won’t actually need to sanitize strings that you supply on your queries if you use prepared statements and you don’t have to worry about sql injection.

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.