Thursday, 13 October 2016

Cookies in PHP

Introduction to Cookies

Cookies are one of the most widely misunderstood concepts of Internet surfing to both users and developers. Many people think that cookies are dangerous, insecure 'things' that let people get your credit card details and what not. This is, for most cases, completely untrue. To the experienced web developer, cookies present a medium for allowing quicker and more user friendly access to your website.

Cookies are simply files stored in a special temporary location determined by your web browser (like Internet Explorer) which allow public information to be stored for your benefit usually. By public information, this can entail any information that you have entered into forms on websites, or certain non-identifying information such as your IP address or user agent.

What makes cookies so special is that, sites can set cookies holding your username and password so that you don't have to log in again. Or perhaps they will store your email address and favourite colour so that a website can change its colour so that it is more appealing to you. Most importantly though, is that other websites and people cannot access your cookies, hence making them fairly secure.

Having a secure method to store user information doesn't mean that you can store anything in cookies though. It is widely accepted that you never store a user's credit card number in a cookie, or a password that will give access to a highly secure area (such as online banking). For areas that require less security like forums, web-mail and unimportant user accounts, it is acceptable to store passwords in encrypted form. The topic of encrypting passwords will be discussed later.


How to Set Cookies


PHP has a very handy function for us called setcookie(), which does exactly as it says; sets a cookie. The function contains many different parameters, of which almost all are optional, however, a majority are quite important.

bool setcookie ( string name [, string value [, int expire [, string path [, string domain [, bool secure]]]]] )


This function definition shows us that only the name parameter is actually required, however, to do anything useful, we will be using value, expire and sometimes path and domain.

When you set a cookie, you must give it a name and you should give it a value, unless you are removing it. Cookies are also generally set to expire at a certain time (measured in seconds from 1972). This means, after a certain amount of time, a cookie will be deleted and the information lost. This is a means to remove redundant information from your system and to keep everything up to date. A cookie also has to be set for a particular domain and path. This means you can set cookies to only work in certain directories or sub-domains which can provide added security.

We will now create a very simple example of cookie usage. This is one of the most common methods of using cookies, so this should be quite useful.

First Cookie
<?php
$username 
'jonny4';

setcookie('username'$username);
?>


What we do here, is set a cookie called username containing the username jonny4. Now this cookie will exist in the domain and folder that the script is run in. So we are going to assume you run this script in www.example.com. If you wanted to access it from admin.example.com it would not work, and you will find that the cookie does not exist. However, it is possible to access the cookie from www.example.com/test/cookies/ as this is still in the correct domain.

As this cookie does not have an expiry time, then the cookie will be deleted once the user closes their browser. This can be very useful for keeping a user logged in for an indefinite amount of time, however, as soon as they close their browser (hence, have left the site), the cookie is removed.

There are two very important things to abide by when using cookies. Firstly, there are can be no HTML, text or white-space output before calling the setcookie() function. This is due to a 'protocol restriction' and you will find that header() and session_start() functions must also follow this rule.
The second rule that you must follow, is that cookie will not become available until the next page has loaded. So you cannot set a cookie, and then immediately access it. You must always first refresh or go to another page to get access to the cookie. This is usually a source of many beginner errors when using cookies, but it is simple to follow.

Accessing the Cookie


Naturally you will want to access the cookie that you have set. With PHP, this is extremely easy and intuitive. Assuming that we have used the above code to set the cookie, and we are now on a new page, we can access the data as follows:

Access the cookie data
<?php

/* Prints the value stored in the username cookie */
echo $_COOKIE['username'];

?>


This code uses the $_COOKIE superglobal to access any cookies set in the current domain. If we were to run this script, it would output jonny4.

It is essential to note that you cannot modify the $_COOKIE superglobal and expect it to update the value in the cookie. If you want to update the cookie, you must manually do it again using setcookie(), again remembering that there can be no output before calling this function.


Practical Cookies : User Logon

To give a more detailed look into how to use cookies, I am going to show you how to create a little login form so that you can store the username and password in a cookie. We will use more complicated cookies now so that you can learn the full use of setcookie().

<html>
<head>
<title>User Logon</title>
</head>
<body>
  <h2>User Login </h2>
  <form name="login" method="post" action="login.php">
   Username: <input type="text" name="username"><br>
   Password: <input type="password" name="password"><br>
   Remember Me: <input type="checkbox" name="rememberme" value="1"><br>
   <input type="submit" name="submit" value="Login!">
  </form>
</body>
</html>


This is the code for our login form, which will produce the following (CSS excluded):



Now that we have our form, we will create our login script. We must decide what restrictions we are going to place on the cookie. I have decided that this will only run on the www.example.com domain and in the /account directory only. Hence,

Login Code
<?php
/* These are our valid username and passwords */
$user 'jonny4';
$pass 'delafoo';

if (isset(
$_POST['username']) && isset($_POST['password')) {
   
    if ((
$_POST['username'] == $user) && ($_POST['password'] == $pass)) {   
       
        if (isset(
$_POST['rememberme'])) {
            
/* Set cookie to last 1 year */
            
setcookie('username'$_POST['username'], time()+60*60*24*365'/account''www.example.com');
            
setcookie('password'md5($_POST['password']), time()+60*60*24*365'/account''www.example.com');
       
        } else {
            
/* Cookie expires when browser closes */
            
setcookie('username'$_POST['username'], false'/account''www.example.com');
            
setcookie('password'md5($_POST['password']), false'/account''www.example.com');
        }
        
header('Location: index.php');
       
    } else {
        echo 
'Username/Password Invalid';
    }
   
} else {
    echo 
'You must supply a username and password.';
}
?>


This code is fairly simple if you break it into parts. First, we have our valid username and password defined so that we can check if the user has entered the correct values. We then check if the user has actually submitted the form and the required values using isset($_POST['username']). If they haven't, a nice error message is displayed.

We then do the important check of if the entered values are equal to the preset username and password. If they aren't we display an error message, however, if they are, the cookie will be set. As you can see we set the cookie using two different methods. If the user has checked the Remember Me box, then the cookie is set to expire at the time of time()+60*60*24*365 which is equal to one years time. The time() function returns the seconds since the start of Unix operating system (1972).

We have used the domain and path parameters of setcookie() to restrict the domain to www.example.com/account as we have specified. If the user has not checked the Remember Me box, then the cookie does not have an expiry time (we have set it to false), hence it will be deleted when the user closes their browser.

You should have also noticed how we have set the password cookie. Instead of just saving the password to a cookie, we have encrypted or hashed it using the md5() function. This function hashes a string so that the original data cannot be recovered. This increases the security of storing the password, but doesn't make it much more difficult for us to deal with.

This script also utilises the header() function to redirect to the index.php page once the cookie has been set. It is important to note that this function can't have any HTML output before calling it, the same as setcookie(). Note: If you are using IIS and not Apache, then you have to use a HTML redirect (META tags) as header() will not work.


Accessing the Data


We currently have a form which submits a username and password, and a login script which sets the cookie on the user's machine. Now we need to access this data, so that it can be used. We are going to access it so that we can validate that the user viewing index.php has actually logged in.

Validating
<?php
/* These are our valid username and passwords */
$user 'jonny4';
$pass 'delafoo';

if (isset(
$_COOKIE[['username']) && isset($_COOKIE['password')) {
   
    if ((
$_POST['username'] != $user) || ($_POST['password'] != md5($pass))) {   
        
header('Location: login.html');
    } else {
        echo 
'Welcome back ' $_COOKIE['username'];
    }
   
} else {
    
header('Location: login.html');
}
?>


In this script, we just check that the cookie exists and is valid. If they aren't, then the user is redirected back to the login form. Otherwise a welcome message is included. The only important thing to notice is how we have validated the password. Before on the login.php script, we have encrypted our password using md5() and as this encryption cannot be undone, we must compare encrypted versions. Hence, we encrypt our preset value and compare it to the already hashed cookie value. This way, there is no chance of the original password becoming available.

Deleting Cookies

Cookies as very picky about how they are deleted and usually require that they be removed, using the same values as they were set with. Hence, if we have a domain or path specified, then we must specify this domain/path when deleting the cookie.

To delete a cookie, you simply set the value of the cookie to null, and also set the expiring time of the cookie in the past. Ideally, one would set the expiry time to about a year in the past, so that if the system time is off by a few minutes or days even, then the cookie will still delete correctly.

Let's dive right in:

<?php

setcookie
('username'''time()-60*60*24*365);

?>


The above script would delete a cookie called username that was in the domain and path that the script is run in. Notice how to expiry time has been set to a year in the past. This will ensure correct deletion of the cookie.

User Logout


Going back to our user logon system, we can now create a very simple logout script. This will mean, that the user will have to re-login on the next access attempt to index.php, even if the Remember Me option was set.

Logout
<?php

setcookie
('username'''time()-60*60*24*365'/account''www.example.com');
setcookie('password'''time()-60*60*24*365'/account''www.example.com');

header('Location: login.html');

?>


See how simple that was? All we had to do, is use the setcookie() function in the same way as before, except setting the value to being empty and the expiry in the past. It is important to set the path and domain otherwise there is a large chance that the cookie will not be deleted.

After our cookies are deleted, the script will redirect back to the login form so that the user can login again. Previously I had mentioned that IIS will not set cookies properly if the header() function is used to redirect. Here is how we could get around this problem:

<?php

setcookie
('username'''time()-60*60*24*365'/account''www.example.com');
setcookie('password'''time()-60*60*24*365'/account''www.example.com');

?>

<html>
<head>
<meta http-equiv="refresh" content="0;URL=login.html">
</head>
<body>
Redirecting...
</body>
</html>


All that we have done is use the meta refresh HTML tag to do the redirect. Using this method, IIS will work perfectly. So, if you are on a IIS server, then I recommend you replace all the previous header() redirects with this HTML code, replacing the page to be redirected to, to the page needed.

This is all there is to cookies for the majority of thinkable uses. They should provide a mechanism in increasing your sites usability and making it easier for you to manage users.

PHP in_array function use and examples

PHP in_array function use and examples



This short article how to use PHP in_array function with arrays. See the description with usage details and reusable code examples below. The details of how to use in_array with normal or multidimensional arrays is also included.

Step 1 - PHP in_array function usage examples


PHP in_array function use and examples

In cases when you want to check whether a value exists in an array you can use the in_array function. This function checks if a value exists in an array. You can use it from PHP version 4. The syntax is the following:
bool in_array ( mixed $what , array $where [, bool $strict = FALSE ] )
By default you simply use only the 2 mandatory parameters ($what and $where) as you can see in the following example:
Code:
  1. $myArray = array("Audi","BMW","Lexus","Mercedes");
  2.  
  3. var_dump($myArray);
  4.  
  5. if (in_array("Lexus", $myArray)) {
  6.     echo "Lexus was found in the array<br/>";
  7. }
  8.  
  9. if (in_array("opel", $myArray)) {
  10.     echo "Opel was found in the array<br/>";
  11. }
The strict parameter:
If you want to force th in_array function to check also variable types then, you need to set the optional strict parameter to true as you can see in the next code example:
Code:
  1. $myArray = array(10,20,30,40,50);
  2.  
  3. var_dump($myArray);
  4.  
  5. if (in_array("10", $myArray)) {
  6.     echo "A - 10 was found as string in the array without strict<br/>";
  7. }
  8.  
  9. if (in_array("10", $myArray, TRUE)) {
  10.     echo "B - 10 was found as string in the array using strict<br/>";
  11. }
Using in_array to find key in an associative array.
The in_array function only checks the values in the array and so if you want to check the keys in case of an associative array then you need to use the array_key_exists function as demonstrated below:
Code:
  1. $myArray = array("Audi"=>"A4","Audi"=>"A6","Lexus"=>"IS","Lexus"=>"LS");
  2.  
  3. var_dump($myArray);
  4.  
  5. if (in_array("Lexus", $myArray)) {
  6.     echo "Lexus was found in the array<br/>"; 
  7. }
  8.  
  9. if (array_key_exists("Lexus", $myArray)) {
  10.     echo "Lexus key was found in the array<br/>";
  11. }
  12.  
Using in_array to find values in a multidimensional array.
The next general question is how to find a value in a multidimensional array. Unfortunately in_array is not able to handle multidimensional arrays so you need to create your own function to solve this problem. However with a simple recursive function you can do this as you can see in the next example code:
Code:
  1. function in_array_multi($needle, $haystack) {
  2.     foreach ($haystack as $item) {
  3.         if ($item === $needle || (is_array($item) && in_array_multi($needle, $item))) {
  4.             return true;
  5.         }
  6.     }
  7.  
  8.     return false;
  9. }
  10.  
  11. $myArray = array(array(10,20),array(11,22),array(111,222));
  12.  
  13. var_dump($myArray);
  14.  
  15. if (in_array(11, $myArray)) {
  16.     echo "11 was found";
  17. }
  18.  
  19. if (in_array_multi(11, $myArray)) {
  20.     echo "11 was found with multi";
  21. }
    
    

PHP - Alternating row background colors

<?php

/*Description:
| This will output a table with alternating row background colors. with this the
| user can read the entries much easier|
------------------------------------------------------------------------------*/


     
// just some random data in an array
$data = array('this','is','just','an','example','to','show','the','alternating','row','colors');
$rows = count($data);

// the two colors to switch between
$rowcolor1 = '#F0F0F2';
$rowcolor2 = '#FFFFFF';

// the background colors on mouseover
$hovercolor1 = '#BAD4EB';
$hovercolor2 = '#DCE9F4';

echo '
<table style="caption-side: top;
    border: 0.1em solid #eee;
    border-collapse: collapse;
    margin: 1em;
    width: 30em;">
    <caption style="font-weight: bold;">Demonstration of alternate row colors</caption>';

for($n = 0; $n < $rows; $n++)
{
    // this is where the magic happens
    if($n % 2 == 1)
    {
        // add more things to swop with each cycle
        $style = $rowcolor1;
        $hoverstyle = $hovercolor1;
    }else{
        $style = $rowcolor2;
        $hoverstyle = $hovercolor2;
    }

    echo '
    <tr id="row'.$n.'" style="background:'.$style.';"
        onmouseover="this.style.background="'.$hoverstyle.'"
        onmouseout="this.style.background="'.$style.'">
        <td style="padding: 0.3em 1em;">'.$data[$n].'</td>
    </tr>';
}

echo '</table>';

PHP - Functions to find strings inside other strings.

<?php

/*------------------------------------------------------------------------------
|| |Description:
| Functions to find strings inside other strings.
|------------------------------------------------------------------------------*/

// InStr function
// checks for an occurance of a string
// within another string
function InStr($String,$Find,$CaseSensitive = false)
{
    $i=0;
    while(strlen($String) >= $i)
    {
        unset($substring);
        if($CaseSensitive)
        {
            $Find = strtolower($Find);
            $String = strtolower($String);
        }
        $substring = substr($String,$i,strlen($Find));
        if($substring == $Find) return true;
        $i++;
    }
    return false;
}

// A similar function, returns the number of occurances
function InStrCount($String,$Find,$CaseSensitive = false)
{
    $i = 0;
    $x = 0;
    while(strlen($String) >= $i)
    {
        unset($substring);
        if($CaseSensitive)
        {
            $Find = strtolower($Find);
            $String = strtolower($String);
        }
        $substring = substr($String,$i,strlen($Find));
        if($substring == $Find) $x++;
        $i++;
    }
    return $x;
}

// Another similar function, this will return the position of
// the string. returns -1 if the string does not exist
function InStrPos($String,$Find,$CaseSensitive = false)
{
    $i = 0;
    while(strlen($String) >= $i)
    {
        unset($substring);
        if($CaseSensitive)
        {
            $Find = strtolower($Find);
            $String = strtolower($String);
        }
        $substring = substr($String,$i,strlen($Find));
        if($substring == $Find) return $i;
        $i++;
    }
    return -1;
}

//Example
echo InStrPos("wel come","come");  // 4

string compare

snippet to check username / password against a text file
compares fields seperated by a pipe "|" to strings sent via POST variables 
<?php




// lets say you have a file where there's on each line something like
// username|password

$data = file('path/to/file.txt'); // read the file

for($x = 0; $x < count($data); $x++)
{
    $parts = explode('|',$data[$x]);
    $name_check = strpos($parts[0],$_POST['name']);
    if($name_check === true) // important are the ===
    {
        $name = 1;
    }else{
        $name = 0;
    }
    $pass_check = strpos($parts[1],$_POST['password']);
    if($pass_check === true) // important are the ===
    {
        $pass = 1;
    }else{
        $pass = 0;
    }
    if($name == 1 && $pass == 1)
    {
        echo 'hello '.$_POST['name'];
        // do whatever
    }
}


?>

PHP - insert at specific location

lets say you have a file where you want to insert a string into an existing file at a specific location.
put a key into the file where you want the new text appear, i.e.:

###INSERTHERE###

then use this function to insert the new data at this keypoint

insert at specific location” makes use of the built-in PHP functions file_exists( ), is_writeable( ), filesize( ), clearstatcache( ), fopen( ), fread( ), strpos( ), fclose( ), ftruncate( ), strtr( ) and fwrite( ).

<?php

/* Description:
| lets say you have a file where you want to insert a string into an existing file
| at a specific location. put a key into the file where you want the new text
| appear, i.e.: ###INSERTHERE### then use this function to insert the new data at
| this keypoint
|
------------------------------------------------------------------------------*/



/*------------------------------------
| function insert_string_in_file
|
| expects: $file          - required [path] to the file you want to work with
|          $insert_point  - required [string] which should be acted on
|          $string        - required [string] which should be inserted
|          $return        - optional [bool] returns message if true, true|false if not
|          $position      - optional [string] before|after - default is replace
|
| returns: [string] with status message
|
|-----------------------------------*/
function insert_string_in_file($file,$insert_point,$string,$return=FALSE,$position='')
{
    // check if file exists
    if(!file_exists($file))
    {
        return($return === TRUE ? 'ERROR - file ('.$file.') does not exist' : FALSE);
    }
    // check if file is writeable
    if(!is_writeable($file))
    {
        return($return === TRUE ? 'ERROR - file ('.$file.') is not writeable' : FALSE);
    }
    // contruct insertion scheme
    if(empty($position))
    {
        // if nothing specified, replace the insertion point with string
        $replacement[$insert_point] = $string;
    }else{
        if($position == 'before')
        {
            // insert the string before the insertion point
            $replacement[$insert_point] = $string.$insert_point;
        }elseif($position == 'after')
        {
            // insert the string after the insertion point
            $replacement[$insert_point] = $insert_point.$string;
        }else{
            return($return === TRUE ? 'ERROR - position ('.$position.') is invalid -
                    use &quot;before&quot; or &quot;after&quot;' : FALSE);
        }
    }
    // check filelenght
    $filesize = filesize($file);
    clearstatcache();
    if($filesize == 0)
    {
        return ($return === TRUE ? 'ERROR - file ('.$file.') is empty' : FALSE);   
    }
    // open file for read and write access
    if(!$handle = fopen($file, 'r+'))
    {
        return($return === TRUE ? 'ERROR - cannot open file ('.$file.')' : FALSE);
    }else{
        $old_contents = fread($handle, $filesize);
    }
    // insert string at the insertion point
    if(!strpos($old_contents,$insert_point))
    {
        fclose($handle);
        return($return === TRUE ? 'ERROR - insertion point ('.$insert_point.') not found' : FALSE);
    }else{
        ftruncate($handle,0);
        $new_contents = strtr($old_contents,$replacement);
    }
    // write new content to file
    if(fwrite($handle, $new_contents) === FALSE)
    {
        fclose($handle);
        return ($return === TRUE ? 'ERROR - cannot write to file ('.$file.')' : FALSE);
    }
    fclose($handle);
    return($return === TRUE ? 'SUCCESS - your string was inserted' : TRUE);
}

// use like
$the_file = 'path/to/file.txt';
$insert_key = '###INSERTHERE###';

$new_data = ' your text to insert';

// insert string before insertion point and get verbose messages
echo insert_string_in_file($the_file,$insert_key,$new_data,TRUE,'before').'<br />';

$new_data = ' should be here';

// simply replace insertion point
if(insert_string_in_file($the_file,$insert_key,$new_data))
{
    echo $insert_key.' was replaced by '.$new_data.' in the file '.$the_file.'.';
}else{
    echo 'insertion failed<br />';
}

?>

Automatically create a link to this URL

This script will detect URLs in a string and automatically create a link to this URL.
it removes the query-string and shows only the top-level of the site linked to as text.
“autolink” makes use of the built-in PHP functions preg_replace( ). 
<?php
$txt = '
 <p>
     this is a string with
     an url in it http://fundisom.com/g5/ and
     its enough to be like that
 </p>';
 
 $txt = preg_replace('/(http|ftp)+(s)?:(\/\/)((\w|\.)+)(\/)?(\S+)?/i', '<a href="\0">\4</a>', $txt);
 
 echo $txt;
  ?>

this is a string with an url in it fundisom.com and its enough to be like that

Wednesday, 12 October 2016

Creating and Editing Excel Spreadsheets in PHP

To create or edit Excel spreadsheets using pure PHP, we will use the PHPExcel library, which can read and write many spreadsheet formats, including xls, xlsx, ods and csv. Before we continue, double-check that you have PHP 5.2 or higher on your server and that the following PHP extensions are installed: php_zip, php_xml and php_gd2.

Creating a Spreadsheet

Creating a spreadsheet is one of the most common use cases in PHP applications and is used for exporting data to Excel spreadsheet. Look at the following code to see how to create a sample Excel spreadsheet with PHPExcel:

// Include PHPExcel library and create its object
require('PHPExcel.php');

$phpExcel = new PHPExcel;

// Set default font to Arial
$phpExcel->getDefaultStyle()->getFont()->setName('Arial');

// Set default font size to 12
$phpExcel->getDefaultStyle()->getFont()->setSize(12);

// Set spreadsheet properties – title, creator and description
$phpExcel ->getProperties()->setTitle("Product list");
$phpExcel ->getProperties()->setCreator("Voja Janjic");
$phpExcel ->getProperties()->setDescription("PHP Excel spreadsheet testing.");

// Create the PHPExcel spreadsheet writer object
// We will create xlsx file (Excel 2007 and above)
$writer = PHPExcel_IOFactory::createWriter($phpExcel, "Excel2007");

// When creating the writer object, the first sheet is also created
// We will get the already created sheet
$sheet = $phpExcel ->getActiveSheet();

// Set sheet title
$sheet->setTitle('My product list');

// Create spreadsheet header
$sheet ->getCell('A1')->setValue('Product');
$sheet ->getCell('B1')->setValue('Quanity');
$sheet ->getCell('C1')->setValue('Price');

// Make the header text bold and larger
$sheet->getStyle('A1:D1')->getFont()->setBold(true)->setSize(14);

// Insert product data


// Autosize the columns
$sheet->getColumnDimension('A')->setAutoSize(true);
$sheet->getColumnDimension('B')->setAutoSize(true);
$sheet->getColumnDimension('C')->setAutoSize(true);

// Save the spreadsheet
$writer->save('products.xlsx'); 

If you want to download the spreadsheet instead of saving it to the server, do the following:

header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="file.xlsx"');
header('Cache-Control: max-age=0');
$writer->save('php://output'); 

Editing an Existing Spreadsheet

Editing spreadsheets in PHP is similar to creating them:

// Include PHPExcel library and create its object
require('PHPExcel.php');

// Load an existing spreadsheet
$phpExcel = PHPExcel_IOFactory::load('products.xlsx');

// Get the first sheet
$sheet = $phpExcel ->getActiveSheet();

// Remove 2 rows starting from the row 2
$sheet ->removeRow(2,2);

// Insert one new row before row 2
$sheet->insertNewRowBefore(2, 1);

// Create the PHPExcel spreadsheet writer object
// We will create xlsx file (Excel 2007 and above)
$writer = PHPExcel_IOFactory::createWriter($phpExcel, "Excel2007");

// Save the spreadsheet
$writer->save('products.xlsx'); 

Preparing the Spreadsheet for Printing

To prepare the spreadsheet for printing, we will set paper orientation, size and margins:

$sheet->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
$sheet -> getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4); 
$sheet->getPageMargins()->setTop(1);
$sheet ->getPageMargins()->setRight(0.75);
$sheet ->getPageMargins()->setLeft(0.75);
$sheet ->getPageMargins()->setBottom(1); 

Using PHPExcel with Laravel

The PHPExcel library can also be used in the Laravel framework. Check out the following PHP package (here) and install it through Composer. After completing the installation steps, you could use the following code to export the data from the database into Excel spreadsheet:

Excel::create('Products', function($excel) {

                // Set the title
                $excel->setTitle('Product list');
   
                // Set the creator
               $excel->setCreator('Voja Janjic');
   
               // Set description
               $excel->setDescription('PHP Excel spreadsheet testing');
   
               $excel->sheet('Products', function($sheet) {
     
                                // Get data from the database
                                $products = Product::all(); 
    
                               // Generate header row
                               $sheet->row(1, array(
                                                'ID',
                                                'Product',
                                                'Price',
                                                'Quantity',         
                                ));
    
                                // Generate data rows 
                               $i = 2; 
                               foreach($products as $product) {        
                                                $sheet->row($i, array(
                                                                     $product->product_id,
                                                                     $product->product_name,
                                                                     $product->price,
                                                                     $variety->quantity,        
                                                ));
     
                                                $i++;
                               }

               });

})->export('xlsx'); 

Next Steps

PHPExcel offers many features that are not mentioned in this tutorial, such as functions, calculations and charts. Read the PHPExcel documentation to learn more about these.

Creating an RSS Aggregator with the PHP SimplePie Library

RSS aggregators such as Google Reader provide a great way to quickly peruse the latest updates and other news from websites that you follow regularly. All you need to do is provide the aggregator with each site's RSS feed location, and the aggregator will retrieve and parse the feed, converting it into a format (HTML in the case of Google Reader) that you can easily peruse.
But what if you want to integrate feeds into your website, or create your own version of an aggregator? Writing custom code capable of efficiently retrieving and parsing the XML that comprises a feed can be a difficult and tedious process, one which has grown increasingly complex with the added support for multimedia content such as podcasts. Thankfully, a number of open source libraries can handle the RSS retrieval and parsing tasks for you. Many of these solutions also offer a number of advanced features such as feed caching in order to reduce bandwidth consumption.
PHP developers are particularly lucky as a fantastic library named SimplePie not only offers the aforementioned features but also supports both RSS and Atom formats, multiple character encodings, and an architecture that makes integration with your favorite content management and blogging platforms a breeze. In this tutorial I'll introduce you to SimplePie, showing you how easy it is to create a rudimentary custom RSS aggregator using this powerful library.

Installing SimplePie

SimplePie requires PHP 4.3 or newer, in addition to PHP's PCRE and XML extensions, both of which are enabled by default. Presuming you meet these minimal requirements, browse to SimplePie's GitHub site and download the latest stable version. Unzip the download and place the directory somewhere within your PHP's include path.
To begin using SimplePie all you need to do is include the simplepie.inc within your PHP script, a task that is typically done using PHP's require_once statement:

require_once("simplepie.inc");
Provided that you have added the SimplePie directory to PHP's include path, you won't need to reference the path within the require statement.
Finally, create a directory named cache somewhere within your project directory, and change the directory owner to the server daemon owner and the permissions to 755, which will allow the server to write to it. SimplePie will use this directory to cache the RSS feeds.

Retrieving a Feed

To demonstrate SimplePie's capabilities let's retrieve and publish the WJGilmore.com RSS feed in HTML format. Believe it or not, you can retrieve and parse the feed using four simple commands:

01 $feed = new SimplePie('http://feeds.feedburner.com/wjgilmorecom');
02 $feed->set_cache_location('/var/www/dev.spiesindc.com/library/cache/');
03 $feed->set_feed_url('http://feeds.feedburner.com/wjgilmorecom');
04 $feed->init();
05 $feed->handle_content_type();
Line 01 instantiates the SimplePie class, exposing the methods we'll subsequently use to retrieve, parse and render the feed. Line 02 defines the location of the cache directory we created earlier in the tutorial. Line 03 defines the RSS feed we'd like to retrieve. Finally, line 04 retrieves and parses the feed, whether via the cache or by reaching out to the feed's online location.
When the feed has been retrieved and parsed, you can use a number of methods to access the feed data, including the feed title, description, and feed items' title and publication date.

foreach ($feed->get_items() as $item) {
  $permalink = $item->get_permalink();
  $title = $item->get_title();
  echo "{$title}
";
}
Executing this example produces the output presented in Figure 1.
SimplePie: Rendering a Feed's Items to a Web Page
Figure 1. Rendering a Feed's Items to a Web Page

Retrieving Multiple Feeds

Of course, any useful aggregator is going to be able to retrieve and simultaneously render multiple feeds. SimplePie is capable of doing not only this, but it can also merge and order the feed entries according to each feed item's publication date!
To retrieve the feeds, all you need to do is populate an array with the various feed addresses and then pass that array to the set_feed_url() method:

$feeds = array (
'http://feeds.feedburner.com/wjgilmorecom',
'http://rss.slashdot.org/Slashdot/slashdot',
'http://online.wsj.com/xml/rss/3_7455.xml'
);
...
$feed->set_feed_url($feeds);
To prove that the feeds are indeed being sorted according to each item's publication date, I've used the $item->get_feed()->get_title() method to place the feed's title next to each item. The results are presented in Figure 2.
SimplePie: Weaving Multiple RSS Feeds Together


Figure 2. Weaving Multiple RSS Feeds Together

Other SimplePie Features

Parsing feeds is only one of SimplePie's many capabilities. Among other features is the ability to retrieve podcasts, send item URLs to other aggregation services such as Digg, Reddit, and Newsvine, and give users the ability to subscribe to published feeds easily. Thanks to these great array of features it really would be possible to build a solution that compares to popular tools such as Google Reader!

Conclusion

SimplePie provides developers with a turnkey solution for integrating RSS feed support into their Web applications. It really is simple as, well, pie.

Creating MS Word documents on the fly

Introduction

Printing HTML pages directly from browsers usually does not give the best printing output, compared to output from word processors such as Microsoft Word. For web-based systems that generate letters, reports and documents, an output that looks authentic and original is very important. Furthermore, printing output directly from browsers is usually dependant on the browser's configuration. Therefore web-based applications may face difficulties in giving the best printing output especially when dealing with large user base.
This article provides a guideline on how to generate documents and letters in MS Word format using a pre-defined template. My customer (a government agency) required for Reminder Letters to be generated automatically in MS Word format from the Loan Management System that I delivered to them. Using this approach, they can generate a Reminder Letter for each loan which then can be printed nicely and saved for future reference.
This solution can be accomplished using COM extension. Please note that the server running this code MUST have MS Word installed.

Overview

Lets start with the big picture. This is how it works:
  1. First, we need to prepare a MS Word document (*.doc) that contains the sample output. The content of the Reminder Letter is normally the same, except for, in each case we need to put the Applicant Name, Loan Reference Number and Today's date in the letter to be generated.
  2. In this MS Word template document, we need to define places where PHP should insert the actual value (such as one pulled from database or evaluated from code). This can be done using the "Bookmark" feature in MS Word documents.
  3. Our PHP code will substitute the Bookmark with the actual value.
  4. And finally, we save it as a new document.
Preparing the MS Word Template
This step is actually simple, all we need is a MS Word document (*.doc) that contains the sample output and Bookmarks.
To create a Bookmark in a MS Word document, click on Insert/Bookmark. You can highlight a word or sentence and make them a Bookmark. In this code, we will substitute the Bookmark with the actual value, therefore the word you highlighted and saved as a Bookmark will be substituted.
A Bookmark name can only contain letters, numbers and underscores. To see all of bookmarks in the document, click on Tools/Options and check Bookmarks under Show group in the View tab.
The code we use in the next section just substitutes a specified bookmark with text, therefore the formatting, images, tables and etc will not be lost.
 

Substituting with the actual value

The following code opens the specified MS Word template document, substitutes the value and saves it as a new file. We defined a Bookmark named "TODAYDATE" in the document, which will be replaced with today's date.

<?php //1. Instanciate Word $word = new COM("word.application") or die("Unable to instantiate Word"); //2. specify the MS Word template document (with Bookmark TODAYDATE inside) $template_file "C:/reminder.doc"; //3. open the template document $word->Documents->Open($template_file); //4. get the current date MM/DD/YYYY $current_date date("m/d/Y"); //5. get the bookmark and create a new MS Word Range (to enable text substitution) $bookmarkname "TODAYDATE"; $objBookmark $word->ActiveDocument->Bookmarks($bookmarkname); $range $objBookmark->Range; //6. now substitute the bookmark with actual value $range->Text $current_date; //7. save the template as a new document (c:/reminder_new.doc) $new_file "c:/reminder_new.doc"; $word->Documents[1]->SaveAs($new_file); //8. free the object $word->Quit(); $word->Release(); $word null; ?>
That's it. Open the new document (c:/reminder_new.doc) and you will see today's date at the bookmark's location.
  1. Initially we instantiated the Word object using this code:

    <?php
    $word 
    = new COM("word.application") or die("Unable to instanciate Word"); ?>
  2. Then we specified the template document that contains the sample output and the Bookmark TODAYDATE:

    <?php
    $template_file 
    "C:/reminder.doc"; ?>
  3. Next we opened the document:

    <?php
    $word
    ->Documents->Open($template_file); ?>
  4. Then we got today's date using date() function. The Bookmark TODAYDATE will be replaced with this value:

    <?php
    $current_date 
    date("m/d/Y"); ?>
  5. Next we found the Bookmark in the document and created a new MS Word Range. Range is used to perform text substitution or insertion.

    <?php
    $bookmarkname 
    "TODAYDATE"; $objBookmark $word->ActiveDocument->Bookmarks($bookmarkname); $range $objBookmark->Range; ?>
  6. Then we substituted the bookmark with actual value:

    <?php
    $range
    ->Text $current_date; ?>
  7. Saved the template as a new document (c:/reminder_new.doc)

    <?php
    $new_file 
    "c:/reminder_new.doc"; $word->Documents[1]->SaveAs($new_file); ?>
  8. Finally, we free the object:

    <?php
    $word
    ->Quit(); $word->Release(); $word null; ?> 
     

    Improving the code

    The above code will return an error if the specified bookmark is not found in the document. To check whether the bookmark exists in the template document, use this:

    <?php if($word->ActiveDocument->Bookmarks->Exists($bookmarkname)) 
    {
        
    //then create a Range and perform the substitution<br>
        
    $objBookmark $word->ActiveDocument->Bookmarks($bookmarkname);
        
    $range $objBookmark->Range;
        
    //now substitute the bookmark with actual value
        
    $range->Text $current_date;

    }
    ?>
    The approach above replaces Bookmarks with the actual value, therefore the word highlighted and defined as the bookmark will be deleted. If you want to insert text before the bookmark, use the following:

    <?php
    $range
    ->InsertBefore("Today's date is: "); ?>
    Alternatively, to insert text after the bookmark, use this:

    <?php
    $range
    ->InsertAfter("Have a nice day!"); ?>
    To select all of the text within the template document, use the following:

    <?php
    $pagecontent 
    $word->ActiveDocument->Content->Text; ?>
    To automatically load the document, use header functions (add this to the end of the PHP file):

    <?php
    header
    ('Content-Type: application/msword'); header("Content-Disposition: attachment; filename=\"Reminder New.doc\""); readfile($new_file); ?>
    Alternatively, to prompt Open/Save dialog box, use:

    <?php
    header
    ('Content-Type: application/msword'); header("Content-Disposition: attachment; filename=\"Reminder New.doc\""); header('Cache-Control: must-revalidate, post-check=0, pre-check=0'); readfile($new_file); ?>
    Note that the above two examples may not work on certain browsers and/or versions.
    You may want to terminate/kill WINWORD.EXE instance if the code triggers an error or terminates unexpectedly. Run Windows Task Manager and end WINWORD.EXE instance.
    That's it. Feel free to share your thoughts and improved versions of the above code.
     
 

Read CSV Data into a PHP Array

The CSV ("Comma Separated Values") file format is one of the oldest in existence that is still used today to exchange data between dissimilar applications. In fact, it's been around for far longer personal computers have! CSV files are often employed to import data from an application such as MS Excel into a database. The glue that holds the import process together can be any programming language, but, as we'll see here today, there are some very good reasons why PHP is one of the best for this purpose. In today's tutorial, we'll write a PHP script that reads in a CSV file and converts its contents into a multi-dimensional array for insertion into a WordPress database.

More on the CSV Format

The name "CSV" would seem to indicate the use of the comma to separate data fields. In reality, the CSV format can vary quite a bit. For that reason, never assume anything; open the file in a text editor and make sure that the format matches your expectations. The field delimiter can be any character from tabs to the "|" symbol. Moreover, fields may be enclosed within single or double quotation marks. Sometimes, the first record is a header containing a list of field names.

Here is a sample file created by Excel's Save As command. It includes header fields and uses the standard comma delimiter:
- Advertisement -

Store,Opening Hour Mon-Wed,OH Thurs,OH Fri,OH Sat,Sun
Aberdeen Beach,11:30-22:00,11:30-22:00,11:30-22:30,11:30-22:30,11:30-22:00
Basildon,12:00-22:30,12:00-22:30,12:00-23:00,12:00-23:00,12:00-22:30
Bath,12:00-22:30,12:00-22:30,12:00-23:00,12:00-23:00,11:30-22:30
Birmingham,11:30-23:00,11:30-23:00,11:30-23:00,11:30-23:00,11:30-23:00
Bluewater,12:00-22:30,12:00-22:30,12:00-23:00,12:00-23:00,12:00-22:30
Braehead,11:30-22:30,11:30-22:30,11:30-23:00,11:30-23:00,11:30-22:00
Braintree,12:00-22:30,12:00-22:30,12:00-23:00,12:00-23:00,12:00-22:00
...

Converting File Entries into Associative Arrays

PHP 5 is very well suited for reading in CSV files. Starting with the file_get_contents() function, we can load the file contents into a string variable. Explode() then splits each line of the file into an array. As an extra step, I like to remove the headers from the file using the array_shift() function, which is then fed into str_getcsv(). That's a specialized function for parsing a CSV string into a numerically indexed array. From there I use a foreach loop to create a key for each field from the headers using array_combine():

$csv = file_get_contents('./input_data.csv', FILE_USE_INCLUDE_PATH);
$lines = explode("\n", $csv);
//remove the first element from the array
$head = str_getcsv(array_shift($lines));

$data = array();
foreach ($lines as $line) {
  $data[] = array_combine($head, str_getcsv($line));
}

The above code produces the following array construct:

array(72) {
  [0]=>
  array(6) {
    ["Store"]=>
    string(14) "Aberdeen Beach"
    ["Opening Hour Mon-Wed"]=>
    string(11) "11:30-22:00"
    ["OH Thurs"]=>
    string(11) "11:30-22:00"
    ["OH Fri"]=>
    string(11) "11:30-22:30"
    ["OH Sat"]=>
    string(11) "11:30-22:30"
    ["Sun"]=>
    string(11) "11:30-22:00"
  }
  [1]=>
  array(6) {
    ["Store"]=>
    string(8) "Basildon"
    ["Opening Hour Mon-Wed"]=>
    string(11) "12:00-22:30"
    ["OH Thurs"]=>
    string(11) "12:00-22:30"
    ["OH Fri"]=>
    string(11) "12:00-23:00"
    ["OH Sat"]=>
    string(11) "12:00-23:00"
    ["Sun"]=>
    string(11) "12:00-22:30"
  }
  [2]=>
  array(6) {
    ["Store"]=>
    string(4) "Bath"
    ["Opening Hour Mon-Wed"]=>
    string(11) "12:00-22:30"
    ["OH Thurs"]=>
    string(11) "12:00-22:30"
    ["OH Fri"]=>
    string(11) "12:00-23:00"
    ["OH Sat"]=>
    string(11) "12:00-23:00"
    ["Sun"]=>
    string(11) "11:30-22:30"
  }
  [3]=>
  array(6) {
    ["Store"]=>
    string(10) "Birmingham"
    ["Opening Hour Mon-Wed"]=>
    string(11) "11:30-23:00"
    ["OH Thurs"]=>
    string(11) "11:30-23:00"
    ["OH Fri"]=>
    string(11) "11:30-23:00"
    ["OH Sat"]=>
    string(11) "11:30-23:00"
    ["Sun"]=>
    string(11) "11:30-23:00"
  }
  [4]=>
  array(6) {
    ["Store"]=>
    string(9) "Bluewater"
    ["Opening Hour Mon-Wed"]=>
    string(11) "12:00-22:30"
    ["OH Thurs"]=>
    string(11) "12:00-22:30"
    ["OH Fri"]=>
    string(11) "12:00-23:00"
    ["OH Sat"]=>
    string(11) "12:00-23:00"
    ["Sun"]=>
    string(11) "12:00-22:30"
  }
  [5]=>
  array(6) {
    ["Store"]=>
    string(8) "Braehead"
    ["Opening Hour Mon-Wed"]=>
    string(11) "11:30-22:30"
    ["OH Thurs"]=>
    string(11) "11:30-22:30"
    ["OH Fri"]=>
    string(11) "11:30-23:00"
    ["OH Sat"]=>
    string(11) "11:30-23:00"
    ["Sun"]=>
    string(11) "11:30-22:00"
  }
  //...
}

Working with the Imported Data

With the CSV data loaded into an array, we are ready to load the data into our system. In this particular case, that involves fetching the post ID so that we can save each location's opening times as WordPress meta data. Luckily, the store names are unique. The location name should be sanitized before inclusion in a query because you can never fully trust external data. WordPress provides the $wpdb->prepare() function for that purpose.

Each location array is passed to the array_slice() function in order to separate the opening times from the store name. Inside the foreach loop, the data is formatted into HTML so as to display the label (array key), colon (:), and value on each line. The formatted string is then saved to the database via the update_post_meta() function, using the post ID as the unique identifier:

foreach ($data as $location) {
  //Get the ID for the store name.  Should only be one.
  $ids = $wpdb->get_col( $wpdb->prepare( "select ID from $wpdb->posts where post_title = %s", $location["Store"] ) );
  
  if (count($ids) >= 1) {
    $opening_times = '';
    foreach (array_slice($location, 1) as $days => $hours) {
       $opening_times .= $days . ': ' . $hours . '<br>';  
    }
    update_post_meta($ids[0], 'opening_times', $opening_times);
  }
}

Here is an example of the formatted markup and its presentation in the browser:

Mon - Wed: 11:30-22:00<br>
Thurs: 11:30-22:00<br>
Fri: 11:30-22:30<br>
Sat: 11:30-22:30<br>
Sun: 11:30-22:00
Mon - Wed: 11:30-22:00
Thurs: 11:30-22:00
Fri: 11:30-22:30
Sat: 11:30-22:30
Sun: 11:30-22:00

Conclusion

Besides having many specialized functions such as str_getcsv(), another distinct advantage to using a PHP script to import CSV data is that it's easy to set it up as a cron job that runs at a given interval, such as every day at 6 Am. The file_get_contents() function can even accept a file URL so that it can retrieve a file directly from an external partner's server.