PHP Dudes: June 2013

Friday, 28 June 2013

Extracting data from Excel file using PHP

When we are doing large projects,their may arise situation like we have to import data to our database from Excel sheets. This is a basic requirement which is asked by all clients.
So here iam explaining how to extract data from an excel sheet simply using php.
If you need to do large data migration from an excel sheet to your MySQL database, the most conventional way is to convert the .xls or .xlsx files to simple CSV(comma seperated file) and read it using PHP file read function. However we can also read a .xls file without converting it to a csv file. We can even navigate through the various worksheets in a single file.

You can get source library from this link Dowload here

I will explain it with an example...

Iam converting the below excel sheet to php format :-

First we need to include the main class file in our PHP page and initialise an object for it as follows:

include 'reader.php';
$excel = new Spreadsheet_Excel_Reader();

Now we are loading the excel file using the above created object as:
$excel->read('sample.xls');

Now we navigate through the rows and columns of the first worksheet in the excel file and display it as a simple HTML table in the browser:

$x=1;
while($x<=$excel->sheets[0]['numRows']) {
echo "\t<tr>\n";
$y=1;
while($y<=$excel->sheets[0]['numCols']) {
$cell = isset($excel->sheets[0]['cells'][$x][$y]) ? $excel->sheets[0]['cells'][$x][$y] : '';
echo "\t\t<td>$cell</td>\n";
$y++;
}
echo "\t</tr>\n";
$x++;
}

In the above sheet[0] is used to read cells from the first work sheet, you can change it according to your needs. Now in sheets[0]['cells'], ['cells'] is a 2D array storing the data as shown in the above screen shot. After executing the entire source code, this is the output that is generated:

so code will be:-

1) download library file from the source i provided earlier..
2)the sample file would be like this:-

<html>
<head>
<style type="text/css">
table {
border-collapse: collapse;
}
td {
border: 1px solid black;
padding: 0 0.5em;
}
</style>
</head>
<body>
<?php
include 'reader.php';
$excel = new Spreadsheet_Excel_Reader();
?>
Sheet 1:<br/><br/>
<table>
<?php
$excel->read('record.xls');
$x=1;
while($x<=$excel->sheets[0]['numRows']) {
echo "\t<tr>\n";
$y=1;
while($y<=$excel->sheets[0]['numCols']) {
$cell = isset($excel->sheets[0]['cells'][$x][$y]) ? $excel->sheets[0]['cells'][$x][$y] : '';
echo "\t\t<td>$cell</td>\n";
$y++;
}
echo "\t</tr>\n";
$x++;
}
?>
</table><br/>

?>
</table>

</body>
</html>

Resolving "headers already send" warning in PHP

When I was learning PHP their were few things that had stumped me, I would usually take it hours to solve them if not days.
Today I am going to write about one such annoying problem that almost always comes in the way of PHP developers at least once.

Warning: Cannot modify header information – headers already sent by (output started at C:wampwwwaktesterror.php:2) in C:wampwwwaktesterror.php on line 4

Warning: session_start() [function.session-start]: Cannot send session cookie – headers already sent by (output started at C:wampwwwaktesterror.php:2) in C:wampwwwaktesterror.php on line 3

This is “header already sent” warning message, that we get whenever we try to redirect a user to some other page or location, when we try to set a cookie or if we try to start a session. Once we get this warning we know that page won’t redirect, session won’t start or cookie’s sent to user, So we can’t even ignore them.

Before we see the solutions to resolve this warning let’s see the source code of the error.php file that I used to generate this error.

<?php

session_start();
header("location:file.php");
?>

Please notice an empty line before “<?php”, this is the cause of errors in his page.

Why This Occurs??

This is requirement of the http protocol that header related information must be sent by the server before it can sent the content. When we try to send a header information after we have already sent some output to client, PHP responds by giving this warning.

The most common reasons are :-

An output is sent, either by normal HTML tags, blank lines in a file, or from PHP file itself(in my case this is the reason).
We read a file using include()/require() function, and that file may have empty spaces or he lines at the end, that will be sent as output.
Important thing to note is that output is sent before header information that you wanted to sent, and hence the warning.

How To Resolve??

This is really simple, just make sure their is no output sent before your call to header function. This might be a correct advice but this still does not help much.

What really needed is that we need to know where is the exact problem, that we can resolve.

Basically we have to make sure that no output is sent before call to any header related functions.

Some guideline that might help

Always start “<?php” at the first line and first column, in you php file.
Keep all you your session related function like session_start() at the start of file, just after “<?php”
If all you are writing is a php file then do not use “?>”, this can prevent empty space or lines from being included in the other files.
use ob_start(); at begining of php file. This function will turn output buffering on. While output buffering is active no output is sent from the script (other than headers), instead the output is stored in an internal buffer.The contents of this internal buffer may be copied into a string variable using ob_get_contents(). To output what is stored in the internal buffer, use ob_end_flush(). Alternatively, ob_end_clean() will silently discard the buffer contents.

If you have any doubts or you did not understand something leave a comment below, I will try to help you as time permits.

Thursday, 13 June 2013

Instant Search Implementation in a PHP site

Hope that everyone knows the word Instant search. If not then you would have certainly noticed the coming of suggestion keywords when you start typing something in google. In simple that process is called Instant search. You can also implement the same function in your website. here iam explaining how to make instant search function by fetching keywords directly from database. here iam using a jquery library, which i found to be very simple in merging with our code. below is the code.. Just copy & paste code..

Make sure that you have connected a databse and have a sample table `cms_instant` and with fields id, title.etc... So that you can customize above code in your other projects.

<?php

ob_start();

include("autoload.php");

$db = new MySql();

$db->connect();

$in=new Instant();

$intdet=$in->getal();// fetching keyword from database

<!doctype html>

<head>

<title>Instant search </title>

$(function() {

var availableTags = [

<?php for($i=0;$i<count($intdet);$i++){ ?>

<?php echo '"'; ?>

<?php echo $intdet[$i]['name']; ?>

<?php echo '"'; ?>

<?php echo ','; ?>

<?php } ?>

];

$( "#tags" ).autocomplete({

source: availableTags

});

</script>

</head>

<body>

</div>

</body>

</html>

Friday, 28 June 2013

Extracting data from Excel file using PHP

Resolving "headers already send" warning in PHP

Thursday, 13 June 2013

Instant Search Implementation in a PHP site

Translate