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

• php excel reader v2.2.1
• Microsoft Excel (Optional)

 

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:

 

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 chat box using jquery and php

Yo! what’s up? It’s been 2  weeks since my last post so I ‘m going to try to make up for those 2 weeks that I haven’t posted anything on my blog.

In this tutorial I’m going to show you how to build a simple chat box using jQuery and php.

 

Requirements

• wampserver
• jquery v1.4.2 or higher
• jquery UI v1.8.2 or higher
• jquery chatbox plugin

 

Resources

I guess this is a new portion so let me introduce to you what resources is. Resources is where you can find the links to some of the sites that in one way or another has given me an idea on how to build something. In this case a chat box.

• How to create a simple Web-based chat application
• jReamDesign – Jquery + PHP + MySQL + AJAX chat box
• jQuery UI Chat box

 

Procedure

First thing that you have to do is to create your working folder where you will put all the files for this mini-project.

Next, create the database where we are going to store the user information and messages.

Here’s the table structure for users:

CREATE TABLE IF NOT EXISTS `users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(30) NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

And the table structure for the chat box:

CREATE TABLE IF NOT EXISTS `chat_box` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sender` varchar(30) NOT NULL,
  `sendto` varchar(30) NOT NULL,
  `message` varchar(140) NOT NULL,
  `date_sent` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=33 ;

You can use the table structures above. But you can also build your own if you want. Since what I’m showing you is very minimal. You might want to add up some fields and other tables as well.
You might also want to insert some data on the table for users using phpmyadmin or any database manager that you know. Since were not going to create a page where the users can register.

 

Next, create a page where the users can login.

<?php
require_once('tutorials_config.php'); //database configuration file
?>
<!-—form—->
<form action="login.php" method="post">
<p>
<label for="username">Username:</label>
<input type="text" name="username" id="username">
<input type="submit" value="login">
</p>
</form>

<?php
//form processing
if(!empty($_POST)){
	
	$username = clean($_POST['username']);
	$exist = $db->query("SELECT username FROM users WHERE username='$username'");
	if($exist == 1){
		header('Location:test.php'); //redirect to chatbox page
		$_SESSION['user_logs'] = $username; //create a session for user
	}else{
		header('Location:login.php'); //redirect to login page
	}
}
?>

<?php
//sanitize user input
function clean($str){
	return trim(mysql_real_escape_string($str));
}
?>

First, you have to include your database configuration file at the uppermost portion of your php file. Then create an html form. You might have notice that I only have a text field for the user id. You can also modify the code and add a password if you want. But as I have said earlier, I want to keep things as minimal and simple as possible. Please read the comments in the code to help you understand what it does.

If we have a login, we also need to have a logout.

<?php
session_destroy(); //destroy the session created from login page
header('Location:login.php'); //redirect to login page
?>

Again, this code is very minimal. It actually destroys the whole session. So if one person logs out, all the other people who is currently logged in will also be logged out. You can always improve this one if you like. That is by looping through all the users in the session and unsetting only the session for the user who wants to logout.

 

We’ve arrived at the point of interest. The chat box itself.
First, include the database configuration file. And check if a user session is already set:

<?php

require_once('tutorials_config.php');


if(empty($_SESSION['user_logs'])){ //check if user session is set
    header('Location:login.php'); //redirect to login page if user session is not set
}

?>

Next, include the files that you have downloaded earlier from the links in the requirements portion. The jquery file, jquery ui files, and the jquery chat box plugin files.

<link rel="stylesheet" href="ui-lightness.css" type="text/css" media="screen" />
<script type="text/javascript" src="jq.js"></script>
<script type="text/javascript" src="jq_ui.js"></script>    
<link type="text/css" href="jquery.ui.chatbox.css" rel="stylesheet" />
<script type="text/javascript" src="jquery.ui.chatbox.js"></script>

After that, create the html form for the chat box.

First, the link to the logout page. And the hidden field where we are going to store the username. I always use hidden fields when I want to store php generated values which I want to access through javascript later on. I don’t know what tricks you are using to achieve the same thing. But that’s the one that I’m using.

<p>
<a href="logout.php">Logout <?php echo $_SESSION['user_logs']; ?></a>
</p>
<input type="hidden" id="username" value="<?php echo $_SESSION['user_logs']; ?>">

Next, also create a text field where the user can choose whom to send the message. I used an auto-suggest field to fetch the usernames from the database except the username of the user who is currently logged in. I have previously written an article discussing about datalist you can check it out if you don’t know how to use datalist yet. I also mentioned there that datalist doesn’t work for Chrome yet, and I have no idea when will they have it on Chrome. So use Firefox 5 or higher if you want to make this work. On the other hand, you can use jquery ui auto-suggest so that you can make it work on all browsers.

Also create a button which will call up the chat box.

<p>
	<label for="chatwith">Chat with:</label>
	<input type="text" name="chatwith" id="chatwith" list="otherusers">
	<datalist id="otherusers">
	<?php 
	$current_user = $_SESSION['user_logs'];
	$users = $db->get_results("SELECT username FROM users WHERE username != '$current_user'");	
	if(!empty($users)){
	?>
		<?php foreach($users as $v){ ?>
			<option value="<?php echo $v->username; ?>"><?php echo $v->username; ?></option>
		<?php } ?>
	<?php } ?>
	</datalist>
	</p>
	<input type="button" id="btn_chat" name="toggle" value="chat" />

Next create a div for the chat box. This is where all the conversations will show up.

<div id="chat_div">
</div>

Next, write the jquery code that will serve as the processor for all the stuff which we are going to do in the chat box. And that includes: selecting the receiver of the message, saving the message into the database, updating the contents of the chatbox so that the most recent messages will be previewed without refreshing the whole page.

First, let’s set what we want to do once the page loads.

$(function(){
    $('#chat_div').hide();  setInterval(load_messages, 2500);

    var username = $.trim($('#username').val()); 
    var box = null;
});

Here’s what the code above does:

• $(function(){ .. });  – the built in jquery function which serves as an initializer. Its equivalent to the document.ready() function in javascript. Anything which goes inside it will get executed once the page loads
• $(‘#chat_div’).hide(); – we hide the chat box div since the recent chat messages from the database will get loaded into the div which is not enclosed in the chat box
• setInterval(load_messages, 2500); – we call the load_messages() function every 2500 milliseconds
• var username = $.trim($(‘#username’).val()); – we store the username of the user
• var box = null; – we set the value of the chat box to null ensure that it doesn’t store anything on page load

 

Next, we set what happens when the user clicks on the chat button. Remember that the user must first select another user to chat with before clicking on the chat button. But I guess I didn’t include the code that checks if the text field indeed contains a username which is stored in the database. So you might as well include that while you’re following this tutorial.

$("#btn_chat").click(function(event, ui) {
		  
      if(box){
         box.chatbox("option", "boxManager").toggleBox();
       }else{
       
          box = $("#chat_div").chatbox({id: username, user:{key : username}, title : "chat", 
                                                messageSent : function(id, user, msg){
													
	 $.post('send_message.php', {'username': username, 'sendto' : $.trim($('#chatwith').val()), 'message' : msg});
													
          $("#chat_div").chatbox("option", "boxManager").addMsg(id, msg);
                                                }});
              }
          });

Here’s what the code above does:

• box.chatbox(“option”, “boxManager”).toggleBox(); – toggles the chat box. When the user clicks on the chat button for the first time the chat box shows up. And when the user clicks on it again, it hides.
• box = $(“#chat_div”).chatbox(); – setting the options for the chat box.
• id – the id of the user who currently uses the chat box
• user – json string which stores the name of the current user
• title – title of the chat box. In facebook the title is the name of the user you are chatting with
• messageSent – what you want to do once the user sends the message(pressing enter). The function assigned to this variable has 3 return values. The id, user, and msg 
• $.post(); – we call up a php script which inserts the message into the database. In the code above, we passed in 3 arguments: the sender, receiver, and message. Scroll down to the latter part of this tutorial to view the code for inserting the message into the database
• $(“#chat_div”).chatbox(“option”, “boxManager”).addMsg(id, msg); – lastly we call up the addMsg() function which appends the name of the user and the message into the chat box

 

Next, write the function which calls up the php script which loads up the recent messages from the database. Scroll down to the latter part of this tutorial to view the code of the load_messages.php script. Remember that this is the function that we call from the setInterval() function so that it will be called up every 2500 milliseconds.

 function load_messages(){
	$('#chat_div').load('load_messages.php');
 }

Next, create a new php file and name it send_message.php. The code is pretty self-explanatory, it’s the one that we use to save the user information, message and current date into the database.

<?php
require_once('tutorials_config.php');

$username = clean($_POST['username']);
$sendto = clean($_POST['sendto']);
$message = clean($_POST['message']);
$date = date('Y-m-d');

$send_message = $db->query("INSERT INTO chat_box SET sender='$username', sendto='$sendto', message='$message', date_sent='$date'");
?>


<?php
function clean($str){
	return trim(mysql_real_escape_string($str));
}
?>

Next, create a new php file again and name it load_messages.php. This one is also self-explanatory, we just fetch the recent messages, and sender from the database. We only fetch the messages which has the same date as the current date.

<?php
require_once('tutorials_config.php');

$user = $_SESSION['user_logs'];
$date = date('Y-m-d');
$messages = $db->get_results("SELECT * FROM chat_box WHERE date_sent='$date' AND sendto='$user' OR sender='$user'");

if(!empty($messages)){
foreach($messages as $v){
?>


<b><?php echo $v->sender; ?> :</b> <?php echo $v->message; ?><br/>
	

<?php } ?>
<?php } ?>

 

Output
Here’s a sample output. Yep! that’s me talking to myself just to test if its really working. Of course, the auto-suggest didn’t work in Chrome, so I had to type the name manually.

 

Conclusion
I guess that’s it for this tutorial. You’ve learned how to create a very simple chat box using jquery and php. If you like this tutorial, please don’t forget to share it. Thanks for reading!

Paginate mysql results in php

This time I’m going to show you how you can paginate mysql results in php. Basically what were going to do here is to divide the entirety of the records which are being fetched by php from the mysql database. We divide it into multiple pages so that the user will not scroll down endlessly if there are over a thousand records.

Requirements

• Wampserver
• EZ Sql

Step

First, let’s include the ez sql class into our script. Then declare an object of it.

<?php
	include('shared/ez_sql_core.php');
	include('mysql/ez_sql_mysql.php');
	
	$db = new ezSQL_mysql("root", "", "payroll", "localhost");
?>

Then let’s capture how many records are returned by selecting all the records from the employee table. And store it in a variable called $max. We also create a variable called $current. Which will just store the upper limit of the mysql query that were going to create later:

$current = $_GET['page'];
$max = $db->query("SELECT * FROM employee");

Next, let’s make sure that we are only going to output something as long as the upper limit ($current) is not greater than the total number of results returned ($max). And that the upper limit ($current) will not be lesser than 0 (negative numbers).

if((!($current > $max)&&($current >= 0))){

}

Anything which will stay inside this script will not be outputted unless the condition is met.

Next, we set how many results are displayed on every page. I choose 5 since there are currently 15 rows in the employee table.

$disp = 5;

Then we set the upper limit to zero. If it doesn’t have any value.

if(!$current)	
$current = 0;

Then we set the value for the next and previous links. Its self explanatory but I’m going to explain it anyway. For the value for $next what were doing is just to add the number of results which are shown, in this case it is 5. To the current upper limit ($current). And for $prev, were just doing the opposite. 

$next = $current + $disp;
$prev = $current - $disp;

Were basically done with setting up the values for the previous and next links. What were going to do now is to put up some pages in the output. So that the user will have the idea how much data he is browsing. First, we declare a variable called $index which we initialize to 0. Were going to use this as the page which will be outputted. We can’t use the upper limit for this, because it increments by 5 every time your turn the page. What we want is something like 1  2  3    and not 5  10   15. Then we declared a for loop which will only loop until the final row. Then the increment value that we used works just like the $next variable.

$index = 0;

for($x=0; $x < $max; $x =$x + $disp){

}

Next, we put something inside the for loop. What the code below does is to output the page numbers. The higher the value of the $disp variable the fewer the pages would be. But of course it all depends on how many rows are being returned. As you can see, there’s an if statement which checks whether the upper limit ($current) is not equal to the value of $x. Which stores the current page value plus the number of rows shown per page. If its not equal, we just output it as plain link. If its equal, then we show some visual to the user by using the bold tag. This way, the user will have an idea what page he is currently is.

if(!($current == $x)){
	echo "  <a href='mysqlpagination1.php?page=$x'>$index</a>  ";
	}else{
	echo "  <b><a href='mysqlpagination1.php?page=$x'>$index</a></b>  ";
	}
	
	$index++;

And we almost forgot the previous and next button. For the previous link, we first check whether we are not already at the first page. This means that the previous link will not show up unless we are at a page greater than 0. For the next link, we check whether the current page is not greater than the value of max rows ($max) and number of rows displayed per page ($disp). This basically means that the next link will not show up if were already at the last page.

<?php if(!($current <= 0)){ ?>
<a href="mysqlpagination1.php?page=<?php echo $prev; ?>">prev</a>
<?php } ?>

<?php if(!($current >= $max - $disp)){?>	
<a href="mysqlpagination1.php?page=<?php echo $next; ?>">next</a>
<?php } ?>	

Finally we display the results in a table. Yup you are not mistaken, were still inside the first if statement that we created earlier.

<?php	
$employees = $db->get_results("SELECT * FROM employee LIMIT $current, $disp");	
?>

<table border="1">
<tr>
<th>Firstname</th>
</tr>	
<?php
	foreach($employees as $e){
		echo '<tr><td>'. $e->Firstname. '</td></tr>';
	}
?>
</table>

 

Conclusion

That’s it for this tutorial. I’ll see you again next time that I have vacant time.

Automatically include a path in php

This is a quick tutorial on how to automatically include a path in every php file that you create. This does not mean that you no longer need to do an include:

include_once('class.string.php');

I just cleared things up. Since that was my assumption when I first learned that I can explicitly include paths in every php file that I create without actually including it.

Things you’ll need
Wampserver
Text editor

Things to do
First you have to launch wampserver, then right left click on the tray icon >php>php.ini

Open the file using a text-editor if windows prompts you what program should you use to open the file.

After the file has been opened. Press ctrl + H on your keyboard then type include_path, click on find next until you see the windows word right above the word include_path.

Uncomment the line by removing the semicolon before it:

; Windows: "path1;path2"
include_path = ".;C:php_includeshome_made"

If you want to include multiple paths then you can separate them using a semicolon, like the example that has already been given just above the include path.

The path that I included contains php classes that I could use for login, connecting to mysql database, sessions, and formatting strings and dates:

You can also include files which you will include as a header, footer or a sidebar. But be sure to reference the css and javascript files needed by those files which you have put in the include path.
Save the file and restart all services from the wampserver tray icon.

All you have to do now is to include those files inside the files where you need them:

require_once('class.sessions.php');

It’s a bit easier when you include it using the full path isn’t it?
Don’t forget to declare an object of the class if it’s a class that you are trying to include.

$sessions = new sessions();

 

Conclusion

That’s it for this quick tutorial. Just make sure that the paths that you included exists, and that the files that you have included exists in the included path. Because you will get a nasty error on every page if the path doesn’t exist.

How to read doc files in php

In this tutorial I’m going to show you how to read .doc files in php. Doc files are files that are made using Microsoft Word if you’re on Windows. And Open Office if you’re on Linux.

Requirements

• Antiword

First you have to extract the contents of the zip archive into your project folder. Rename the extracted folder to antiword if it isn’t already named antiword.

From the antiword folder, copy the file named  8859-1.txt and paste it on:

C:antiword

Just create an antiword folder directly under c: drive or where Windows is installed if its not created yet.

Next, just paste in the code below, and edit it to match your configuration. Note that tuts.doc in the example below is adjacent to the php script. But you can also change that if you want a separate folder for doc files alone. The –f parameter will just format the output. It will wrap words that are bold with * * and italics with / /. I’m still yet to figure out how to convert those into actual html.

<?php
$filename = 'tuts.doc';
$content = shell_exec('C:wampwwwtesterread_documentsantiwordantiword -f '.
$filename);
echo $content;
?>

Here’s a sample output, as you can see it’s a little messy:

 

And not just little, I think its really messed up. Compared to the result in the command line:

 

I won’t put any conclusion on this tutorial. I really think its incomplete. So if ever you know how to format the result of the script then please shout them out in the comments below.

Php login system for multiple users

Seems like I’m in blogging mode for the past few days. This time I’m going to talk about how to make a login system in php. Wait, if you still remember I have made one before but using only sessions. And its not actually designed to handle multiple users. Since the logout script just destroys the whole session. Which means that other users who are actually currently logged in will also be logged out. And that’s not really good.

What we will be doing today is a login system which can handle multiple users. And we will also be implementing object oriented programming. To make things more organized in clean.

 

Building the table

First, you have to create a database. Then just paste these sql statements below. You can use phpmyadmin to do the job. Just go to the sql tab, and paste it in the box that will appear. Then click on go.

CREATE TABLE IF NOT EXISTS `users` (
  `User_ID` int(11) NOT NULL AUTO_INCREMENT,
  `Username` varchar(20) NOT NULL,
  `Password` varchar(35) NOT NULL,
  `Registered_IP` varchar(15) NOT NULL,
  `Email` varchar(30) NOT NULL,
  `Registration_Date` datetime NOT NULL,
  `Last_Login` datetime NOT NULL,
  `User_Class` int(11) NOT NULL,
  `Active` int(11) NOT NULL,
  PRIMARY KEY (`User_ID`),
  UNIQUE KEY `Username` (`Username`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
 
 

 

User class

Let’s create a class where we will put all the functions and variables which can be associated with the user. The codes below maybe a little bit unfamiliar since we will not be using the usual way of connecting to the mysql database. This time we will use ez sql, a framework which can manipulate different kinds of database. You can use the links below to learn about ez sql:

http://www.catswhocode.com/blog/php-fast-and-easy-sql-queries-using-ezsql

http://jvmultimedia.com/docs/ezsql/ez_sql_help.htm

First, you have to import the ez sql core files into the user class. Then create an object:

require_once('ez_sqlsharedez_sql_core.php');
require_once('ez_sqlmysqlez_sql_mysql.php');

$mdb = new ezSQL_mysql('root', '', 'login_system', 'localhost');

Then declare the variables, this should match the ones that are on the table that we created earlier:

class users{

private $user_id;
private $username;
private $password;
private $reg_ip;
private $email;
private $reg_date;
private $last_login;

}

Next, create a function that will check if the user is registered or not. This function will be called every time a user will login.

function check_user($uname, $pword, $ip){
	global $mdb;
	$exist = $mdb->query("SELECT Username, Password, Registered_IP FROM users 
         WHERE Username='$uname' AND Password='$pword' AND Registered_IP='$ip'");
	return $exist;
}

This function takes up 3 arguments. The username, the password, and the ip address of the computer from where the page is being accessed. It then queries the database if it finds a match for all those data. And returns true if it finds it. If it finds a record which matches the username, password, and ip. Then the function returns 1. If not, it returns 0.

Next, create a function that will set the last login date for the user:

function set_last_login($uname){
	global $mdb;
	$mdb->query("UPDATE users SET Last_Login=NOW() WHERE Username='$uname'");
}

NOW(), is a mysql function which returns a datetime stamp. Like this one:

2011-05-12 20:01:07

After that, we set the user as active:

function set_active($uname){
	global $mdb;
	$mdb->query("UPDATE users SET Active=1 WHERE Username='$uname'");
}

Sessions and cookies can’t be shared between browsers and computers that’s why we need to do this. Setting the active field into 1 means the user has already been logged in.
Then we create another function which will be called upon user logout. This will switch the active value to 0 for the corresponding user:

function set_inactive($uname){
	global $mdb;
	$mdb->query("UPDATE users SET Active=0 WHERE Username='$uname'");
}

Then we create another function which will check if the user is  logged in or not. This will ensure that the user is logged in only one place. A sort of security feature.

function check_active($uname){
	global $mdb;
	$active = $mdb->query("SELECT Username, Active FROM users WHERE Username='$uname' AND Active=1");
	return $active;
}

 

Session class

Next, lets create a session class. This will contain all the functions related to manipulating the session array.

class sessions{

function create_session($username, $ip){
	$_SESSION['log_users'][] = array('username'=>$username, 'ip'=>$ip);

}


}

Okay, so what we just did is to declare a new session. Then assigned an array into it, which contains the username and the ip address.
Next, we create another function which checks the session if it contains the username and the ip address of the current user. It loops through the session array and if it finds a record which matches the username and ip, it returns 1.

function check_session($username, $ip){
	
	foreach($_SESSION['log_users'] as $lu){
	
		if(($lu['username']==$username)&&($lu['ip']==$ip)){
			return 1;
		}
	}
}

Lastly, we create a function which logs the user out of the system. This one also loops through the session array, similar to the check_session() function. But this time, when it finds the username which is equal to the one specified as an argument. It unsets it from the session array. I just used the actual index where the username and ip is stored, so that those will be both unset, and the actual index will be unset too. Because if you just unset the  username and ip individually. The index where they are stored is just emptied but still exist.

function unset_session($uname){
	foreach($_SESSION['log_users'] as $id=>$lu){
	
		if($lu['username']==$username)){
			unset($_SESSION['log_users'][$id]);
		}
	}
}

 

Building the login form
Create a new php file, then paste this code:

<form id="form1" name="form1" method="post" action="loginForm.php">
  <label for="username">Username:</label>
  <input type="text" name="username" id="username" />
  <br />
  <label for="password">Password:</label>
  <input type="text" name="password" id="password" />
  <br />
  <input type="submit" name="login" id="login" value="login" />
<br />
</form>

Next, import the classes which we made earlier, and initialize them by declaring an object:

require_once('class.users.php');
require_once('class.sessions.php');
$users = new users();
$sessions = new sessions();

Then, check if the POST array is not empty. So that the codes that we will put inside will only be executed if the POST array has contents:

if(!empty($_POST)){

}

Inside of that condition, we then assign data extracted from the POST array to a variable:

$uname = $_POST['username'];
$pword = md5($_POST['password']);
$ip = $_SERVER['REMOTE_ADDR'];

The passwords that are stored in the database are hashed, so we need to call the md5 function or whatever function we used to hash the password before we submit it to any functions.
We then call the check_user() function from the user class. And assign the value which has been returned to the variable $exist:

$exist = $users->check_user($uname, $pword, $ip);

Next, we call the user_check() function from the users class and set the returned value to the variable $active. There are only 2 possible values here, 1 and 0. The function will return 1 if the username specified as an argument has an active value of 1. Otherwise it returns 0.

$active = $users->check_active($uname);

We then issue another condition, this will check if the value returned by check_user() function is equal to 1. And that the check_active() function returns a value except 1.

if(($exist == 1)&&($active != 1)){

}

Inside the condition, we call the create_session() function from the sessions class:

$sessions->create_session($uname, $ip);

What this does is to add the current user into the session array. It stores both the ip and the username, so that if somebody tried to access the page in a different computer with the same username. Then it won’t be able to view the user page since the user is already logged in somewhere else.

Then we create a cookie, which stores the current username and ip address in the local machine. In case you don’t know, cookies are stored in the local machine and sessions are stored in the server. If you are developing and testing it on the same machine where you are developing then there’s really not much difference since your local machine and server is the same. The only difference is that the cookie might be stored inside a folder in the browser. And the session is stored inside a folder in the web server.

setcookie("user", $uname, time()+900);
setcookie("ip", $ip, time()+900);

The cookie above will expire after 15 minutes. Which means, the user will have to log in every 15 minutes. Just change the value that is being added to the current time if you want it to be longer.
Then we call the set_last_login() function from the user class:

$users->set_last_login($uname);

After that, we just call the set_active() function from the user class. This will switch the status of the user to active or 1.

$users->set_active($uname);

Lastly, just redirect the page to the actual page that can only be accessed by users who are logged in:

header('Location:userpage.php');

 

Userpage

Next, create another php file. Name it to userpage.php or anything similar.
Import the session class again, then declare an object of it:

require_once('class.sessions.php');
$sessions = new sessions();

Then check if the cookie that we set upon log in exists. Then if those values exist, it assigns them to a variable:

if(!empty($_COOKIE['user'])&&($_COOKIE['ip'])){
	$username = $_COOKIE['user'];
	$ip = $_COOKIE['ip'];
}

If it doesn’t exist, then just put an else statement which will redirect the page into the login page:

else{
	header('Location:loginForm.php');
}

Then we call the check_session() function from the sessions class. And assign the returned value into the $active variable:

$active = $sessions->check_session($username, $ip);

Create another condition which checks if the $active has a value of 1:

if($active == 1){

}

Everything that is not supposed to be accessed by users who are not logged in goes inside of the condition above. First, let’s do the usual thing, greeting the user who is logged in:

echo 'userpage';
echo '<hr/>';
echo 'Hi! '. $username;

Then create a link which logouts the user:

<a href="userpage.php?logout=1&user=<?php echo $username; ?>">Logout</a>

The code below is then executed if the user clicks on the logout link:

if(!empty($_GET['logout'])){
	$sessions->unset_session($username);
	setcookie("user", "", time()-9999999);
	setcookie("ip", "", time()-9999999);
         $users->set_inactive($username);
	header('Location:loginForm.php');
}

On the 2nd line, we just call the unset_session() function inside the sessions class. We just assigned the username fetched from the $_COOKIE[‘user’] variable.
3rd and 4th line, we unset the user and ip cookie. That is by setting the cookie expiration date in a time in the past. I couldn’t think of a number so I just put 999999.
5th line, we just switch back the value of the active field for the corresponding user. This means that the user is already logged out or inactive.
6th line, we just redirect to the loginForm.

 

Conclusion
That’s it for this tutorial. The codes above are not in any way optimized or is the best way to do things. I will suggest that you don’t only use the ip address , username and password to authenticate the user. You might as well generate a unique string of characters and place it in the session as well.

Automatically start session on every page in php

Are you sick of having to type this code:

   1: <?php session_start(); ?>

On every page which requires user authentication and other aspects which require the use of session variables? Welcome to the club. In this article I’m going to show you some of the ways on how to ditch the freakin’ code.

 

Use headers

If you’re already using headers then ditch this part. Headers are php files which are used to perform operations which are repeated throughout the program. It can also be used to style every page in your application. Its also a place where you can link the javascript files which you need throughout the entire program that you are creating.

Here’s what a php header file might look like:

   1: <link rel="stylesheet" type="text/css" href="../css/adminstyle.css" />

   2: <link rel="stylesheet" type="text/css" href="../css/grayaccordion.css" />

   3: <link rel="stylesheet" type="text/css" href="../css/validationEngine.jquery.css" />

   4:  

   5: <script  type="text/javascript" src="../js/grayaccordionload.js"></script>

   6: <script  type="text/javascript" src="../js/topnav.js"></script>

   7: <script src="../js/jquery.validationEngine-en.js" type="text/javascript"></script>

   8: <script src="../js/jquery.validationEngine.js" type="text/javascript"></script>

Its not necessary to put <?php ?> tags on a header file. As you can see in the code above. But if you will need to perform operations which require the power of the server side. Then you should enclose them in <?php ?> tags.

Then you can just import them later, in a different file:

   1: <?php require_once('header.php'); ?>

 

 

Edit  php.ini

You can also edit the php.ini file and automatically start a session of every page.

Just search for this line:

   1: ; Initialize session on request startup.

   2: ; http://php.net/session.auto-start

   3: session.auto_start = 0

And change it to this:

   1: ; Initialize session on request startup.

   2: ; http://php.net/session.auto-start

   3: session.auto_start = 1

Restart php, or better yet all the services associated with it (Apache, mysql). Then create a new session without doing the session_start() code.

   1: $_SESSION['student'][] = array('idnum'=>2800570, 'name'=>'natsu');

Make another file and paste this code:

   1: print_r($_SESSION['student']);

See if it outputs the session that you created earlier. It should look like this:

   1: array([0]=>'idnum'=>2800570, 'name'=>'natsu');

Multiple file upload using jquery and php

In this tutorial I’m going to show you how you can handle multiple file uploads using php and jquery.

 

Requirements

• BlueImp Jquery File Upload Plug-in
• Wamp
• Jquery Core, Jquery UI, Jquery UI css

 

I’m assuming that you already know the basics of php and jquery. I’ll try to make this tutorial as short as possible. But you can also check out the documentation of the blueImp jquery file upload if you are pretty confident in your skills.

First, you need to include the css files for the file upload plugin. Remember to change the code inside the href attribute to match your folder structure.

<link rel="stylesheet" href="../jqui.css" id="theme">
<link rel="stylesheet" href="../jquery.fileupload-ui.css">

Then the html form that will be used to upload files. Remember that this can be used to upload multiple files to the webserver.

<form id="file_upload" action="upload.php" method="POST" enctype="multipart/form-data">
    <input type="file" name="file" multiple>
    <button>Upload</button>
    <div>Upload files</div>
    
</form>

Then just after the form. Insert a table tag. This will be used later on to display the selected files.

<table id="files"></table>

Next, include the scripts you downloaded earlier.

<script src="../jq.js"></script>
<script src="../jqui.js"></script>
 
<script src="../jquery.fileupload.js"></script>
<script src="../jquery.fileupload-ui.js"></script>

Then insert another script tag. This script will communicate with ‘upload.php’. And you can also define here  some rules regarding filesize , filetype and other restrictions in uploading a file.

<script>
/*global $ */
$(function () {
    $('#file_upload').fileUploadUI({
        uploadTable: $('#files'),
        downloadTable: $('#files'),
        buildUploadRow: function (files, index) {
            return $('<tr><td>' + files[index].name + '</td>' +
                    '<td class="file_upload_progress"><div></div></td>' +
                    '<td class="file_upload_cancel">' +
                    '<button class="ui-state-default ui-corner-all" title="Cancel">' +
                    '<span class="ui-icon ui-icon-cancel">Cancel</span>' +
                    '</button></td></tr>');
        },
        buildDownloadRow: function (file) {
            return $('<tr><td>' + file.name + '</td></tr>');
        }
});
);
</script>

That’s pretty much what you need if you don’t need to restrict the user from uploading a 200TB file. Or a javascript file that will mess up your whole system.

If however you want to limit the user from doing that. Then include the function below. Under the beforeSend event. The code below will limit the file size to 1Mb or 1 Million Bytes? Yeah, that’s a question mark since I’m not sure if 1Mb is really equal to 1 Million Bytes.

 function (event, files, index, xhr, handler, callBack) {
         if (files[index].size > 1000000) {
             handler.uploadRow.find('.file_upload_progress').html('FILE TOO BIG!');
             setTimeout(function () {
                 handler.removeNode(handler.uploadRow);
             }, 10000);
             return;
         }
         
         
        
         callBack();
 }

If you’re not satisfied with just limiting the file size. And you want the user to be able to upload only specific file types. Like for example, images:

var regexp = /.(png)|(jpg)|(gif)$/i;
      
       if (!regexp.test(files[index].name)) {
           handler.uploadRow.find('.file_upload_progress').html('ONLY IMAGES ALLOWED!');
           setTimeout(function () {
               handler.removeNode(handler.uploadRow);
           }, 10000);
           return;
 }

Lastly, the php script that will do the transfer from the clients computer to a folder inside the webservers web accessible directory. Here’s a short story on how this works: First the user will click on the upload button. Then selects multiple files. Blue Imp Jquery File Uploader will handle all the processes needed to forward the file information (temporary file path, filename, file size, file type) to the php script that you defined. In this case ‘upload.php’. As you can see from the code below. We don’t even have to create an array for all the files that are selected since its already done by Blue Imp. As you can see, the selected files will be uploaded to a folder named ‘img’. We used the ‘move_uploaded_file’ function because the file is not being materialized directly to the folder where we want it to reside. After every upload, it first resides on a ‘tmp’ or temporary folder in your webserver. In wamp the temporary folder is in: ’ C:wamptmp’ . You might notice that all the files in that folder have .tmp file extension and that the filename is mainly composed of unusual characters. That is why we need to issue this ‘$_FILES[“file”][“name”]’ as a parameter to get the original filename.

<?php
 
$file = $_FILES['file'];
echo '{"name":"'.$file['name'].'","type":"'.$file['type'].'","size":"'.$file['size'].'"}';
 
 
$tempath=$_FILES["file"]["tmp_name"];
$transform=explode('.', $tempath);
 
 
$truefile=explode('.', $_FILES["file"]["name"]);
$filenamepath=$transform[0]. ".". $truefile[1];
 
move_uploaded_file($_FILES["file"]["tmp_name"],
      "img/" . $_FILES["file"]["name"]);
?>

This is what it will look like. If you haven’t customized it.

Notice that it didn’t actually upload the txt file, and the mkv file which is 70Mb in size. But one thing that has caught my attention is the txt file which I changed the file extension to png. It actually uploaded it. I wonder if its really possible to detect the real extension of a file noting that the extension was only edited.

 

Conclusion

That is all with this tutorial. I hope you learned something. And please share this site using the buttons below if this tutorial has helped you.

Use gmail to send email in php

This time I’m going to show you how you can send emails in php using gmail smtp server.

 

Requirements

• phpMailer
• A gmail account
• Wamp

 

First you have to download phpMailer from the link that I gave above. What I’m going to use in this tutorial is phpmailer for php version 5 and 6.

Just download the .zip file so that you won’t have any trouble extracting it. Especially if you are on windows.

Extract the file inside the wamp directory. Rename the extracted folder to phpmailer. So that it will be easier to remember.

To be consistent. After opening your phpmailer folder. It should contain most of these files:

Now, open up the class.phpmailer.php in a decent text editing tool like notepad++ or programmer’s notepad.

Press ctrl + h in your keyboard and search for $host or anything similar:

Until you find these lines:

public $Host          = 'localhost';
public $Port          = 25;

Those are the defaults. Change those to:

public $Host          = 'ssl://smtp.gmail.com';
public $Port          = 465;

You can also change it to other hosts. But this tutorial is only for using gmail smtp. So just search it on your own if you want to use other smtp servers.

 

Next, create a new php file. This time, inside your project folder. Include those 2 files that you edited earlier. Remember to change the example below based on the actual location of your phpmailer folder.

include('../../phpmailer/class.phpmailer.php');

Then create an object of the phpMailer class. And specify that it will be using SMTP or Simple Mail Transfer Protocol to send emails.

$mail = new PHPMailer();  
$mail->IsSMTP();

Also add this line to enable SMTP authentication. To authenticate that you are indeed a real human. And not a spammer bot.

$mail->SMTPAuth = true; 

Then add these lines. To specify which email address you are going to use as the sender email address:

$mail->Username = "blablabla@gmail.com"; 
$mail->Password = "mypasswordisthis"; 

I haven’t tested yet if you can use other  email addresses besides gmail. But you can certainly send emails to yahoo or hotmail account using this method.

Next, specify the email address where the recipient is going to reply:

$webmaster_email = "replyemail@gmail.com"; 

Then, the recipients email address. This can be yahoo, hotmail , or other email accounts as I have said a while ago.

$email="recipient@gmail.com"; 

Then, specify the name of the recipient together with the  email address that the recipient will see as the one who sent the mail.

$name="Master Buten"; 
$mail->From = $webmaster_email;

In the example above, we will send the email to master buten.

You can also specify your name after that:

$mail->FromName = "Master Kayosama";

Then we call the phpMailer function which uses the variables that we defined earlier as an argument:

$mail->AddAddress($email,$name);

Then call the other function that adds reply to information to the email that we are going to send:

$mail->AddReplyTo($webmaster_email, $mail->FromName);

You can also set the word wrap attribute to anything you want. Word wrap will allow you to format the body of your email in a nice way. If you specify it to be 50 then it creates a new line before or when 50 characters have been entered.

$mail->WordWrap = 50;

Then for adding attachments. You call the function addAttachment:

$mail->AddAttachment("embarassingmoments.jpg");

Then specify if the email is html.

$mail->IsHTML(true);

Add the subject and the body of the email:

$mail->Subject = "Training";
$mail->Body = "I send you this email because 
we will have a training inside Mt. Pinatubo on Mar 31, 2011";

You can also add an alternative body if you want. This is the one that the recipient is going to see if he switches to plain text format.

$mail->AltBody = "I send you this email because
 we will have a training inside Mt. Pinatubo on Mar 31, 2011";

Lastly, add these lines in order for you to determine if the mail is indeed sent or not:

if(!$mail->Send())
{
echo "Mailer Error: " . $mail->ErrorInfo;
}
else
{
echo "Message has been sent";
}

Oops, were not ready to fire up that file yet. There’s one more thing to do.

Left click on the wamp tray icon. Select php-> php extensions.

Then you just have to ensure that php_openssl is checked. If not, then click it.

Next, restart all services.

Wait for the wamp tray icon to turn white before you do anything funny.

Fire up the php file that you have created. You should see something like the one below. If not, then review the instructions that I provided and see if you followed them well.

 

Conclusion

That’s an easy way to send email in php using gmail smtp. Please share this article if you like it. If you have questions or suggestions just throw them in the comments. Thanks for reading!

Escaping invalid characters in php

In this tutorial. I’m going to show you how you can escape all invalid characters in php. This will be useful when you are writing an application which connects and manipulates a database. For example, a mysql database wherein you perform insert, update, delete queries.

 

Why escape invalid characters?

Simple, since not every character that you can type using your keyboard can be valid when performing queries. As an example, try opening up phpmyadmin and perform the query below on the database of your choice:

SELECT * FROM table_name where field_name='dog's'

Note: substitute the table_name and field_name with the corresponding table name and field name that you are querying.

Next thing you will see is this nasty error:

#1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘s” at line 1

You can now imagine what will happen if you just did something like this in your code:

<?php
 
include_once('connection.php');
 
$uname=$_POST['uname'];
$pword=$_POST['pword'];
 
mysql_query("INSERT INTO users(Username, Password) 
VALUES('$uname','$pword')");
 
?>

You’ll get the same nasty error. If the user inputs an invalid character in the form.

Here are some of the ways on how to avoid invalid characters from getting to your queries. These can also be used to avoid sql injections and other nasty attacks. But don’t take my word for it. Research on ways on how to avoid sql injections if you want to make your application more secured.

 

mysql_real_escape_string()

Use the built in php function to sanitize user input:

$userinfor=array($_POST['uname'], md5($_POST['pword']));
 
 
$newvalue=array();
foreach($userinfor as $key=>$value){
    $newvalue[$key]=mysql_real_escape_string($value);
    
}
 
$uname=$newvalue[0];
$pword=$newvalue[1];
 
$db->query("INSERT INTO users(Username, Password) 
VALUES('$uname','$pword')");

Note: the code for executing the query is a bit different because I’m using a php class called ezsql. As the name implies, it’s a class used to easily manipulate database which uses the standard query language to manipulate their database.

If the code above didn’t work for you, then you can always do it like this:

$uname=mysql_real_escape_string($_POST['uname']);
$pword==mysql_real_escape_string($_POST['pword']);
 
$db->query("INSERT INTO users(Uname, Hpword) 
VALUES('$uname','$pword')");

 

PDO’s

You can also use pdo’s in php. Don’t ask me what’s the meaning of pdo. Because I can’t find anything on the internet what pdo means. So if you know what it means then please leave a comment.

To keep things short, I use pdo to automatically sanitize user input without having to write any functions that will handle it.

PDO is a built in class in php, so you don’t have to download anything if you already have php installed on your computer.

Begin by creating an object of the pdo class:

$pdo=new pdo("
  mysql:host=localhost;
  dbname=testdb", 
  "root", 
  "yourmysqlpassword");

As I have always said the defaults are:

• localhost -this is your local computer. This is the default for the host.
• testdb -the database that you want to manipulate.
• root- the default user. You can leave it as it is.
• yourpassword- yeah this is your mysql password. Most of the time this is blank.
   

Then here’s an example on how to insert records in the database using pdo:

$inserts=$dbh->prepare('INSERT INTO testtable(LNAME, FNAME) 
VALUES(:fname, :lname)');
$inserts->bindParam(':fname', $fname);
$inserts->bindParam(':lname', $lname);
 
$fname=$_POST['fname'];
$lname=$_POST['lname'];
 
$inserts->execute(); 

There’s an extensive tutorial  on how to use pdo’s. You might want to read it if you want to learn more about pdo’s.

And an article on why you should be using pdo’s

 

Conclusion

What you have just red are some of the ways that you can sanitize user input to avoid any error when performing sql queries. Knowing and applying these things into your future projects will make the data in your database more reliable. And as you can see on the pdo example, it will also make your code cleaner as you implement classes in your application.