Archive for the ‘PHP Coding’ Category

Remove whitespace from string

May 23, 2010 PHP Coding

I have been doing a lot of coding using the framework CodeIgniter lately so I have been creating many simple helper functions to perform different tasks. My most recent project involves creating PDF files with text from a database. The problem is that the text from the database is dirty, meaning that in a person’s name there could be multiple spaces between the person’s first and last name or even spaces at the end of their name. In dealing with legacy data that you cannot change but needs to be output correctly without those spaces you get creative. I am sure I could use trim(), ltrim() or rtrim() to remove the spaces but that does not remove the multiple spaces between names. The below function should help solve this issue.

<?php
function replace_whitespace($Value = '')
{
	// Replace any whitespace with only a single space
	return preg_replace('/\s+/', ' ', trim($Value));
}
?>

Usage:

<?php
echo '<pre>';
$Text = 'Daniel     Kassner';
echo $Text,' - ',strlen($Text),'<br />';
$Text = ReplaceWhitespace($Text);
echo $Text,' - ',strlen($Text);
echo '</pre>';
?>

Output:

Daniel     Kassner - 18
Daniel Kassner - 14

Tags: ,

Get date by position (ie. third Wednesday of January)

May 22, 2010 PHP Coding

In a recent project I needed to come up with code to calculate something like the first, second, third day of whatever month. Unfortunately the strtotime() function does not let you enter “third Wednesday of January” to calculate the date. This is where the code below works great for such things.

<?php
/*
Author: Daniel Kassner
Website: http://www.danielkassner.com
*/
function GetDayByPosition($Position, $Weekday, $Month, $Year) {
	// Sanatize some of the inputs
	$Position = strtolower($Position);
	$Weekday = strtolower($Weekday);
	
	// Go to next month so we can then go back 1 week at end of script for calculating last xxx of month
	if ($Position=='last') { $Month += 1; }
        // We cannot have 13 months so set as January of the next year.
	// This was pointed out by Evan who made a comment on the posting of this script
	if ($Month > 12) {
		$Month = 1;
		$Year += 1;
	}
	// Create new date object for the first of the month
	$D = new DateTime($Year.'-'.$Month.'-1');
	$DOW = $D->format('w'); // Get the current day of the week based on the 1st of the month
	$keys = array('sunday'=>0,'monday'=>1,'tuesday'=>2,'wednesday'=>3,
				  'thursday'=>4,'friday'=>5,'saturday'=>6);
	// Calculate what the offset is based on the current day of the week
	// vs the day in the week you want to get
	$offset = $keys[$Weekday] - $DOW;
	if ($offset<0) { $offset += 7; }

	switch ($Position) {
		// Don't need to add anything to first
		case 'second':
			$offset += 7; // Add 1 week
			break;
		case 'third':
			$offset += 14; // Add 2 weeks
			break;
		case 'fourth':
			$offset += 21; // Add 3 weeks
			break;
		case 'last':
			$offset -= 7; // Go back 7 days
			break;
	}
	// Add the current offset of days to the date object
	$D->modify('+'.$offset.' days');
	return $D->format('Y-m-d');
	
}
?>

Usage:

<?php
// This will output 2009-01-05
echo 'First Monday of January 2009: ',GetDayByPosition('first', 'monday', 1, 2009),'<br />';
// This will output 2009-01-13
echo 'Second Tuesday of January 2009: ',GetDayByPosition('second', 'tuesday', 1, 2009),'<br />';
// This will output 2009-01-21
echo 'Third Wednesday of January 2009: ',GetDayByPosition('third', 'wednesday', 1, 2009),'<br />';
// This will output 2009-01-22
echo 'Fourth Thursday of January 2009: ',GetDayByPosition('fourth', 'thursday', 1, 2009),'<br />';
// This will output 2009-01-30
echo 'Last Friday of January 2009: ',GetDayByPosition('last', 'friday', 1, 2009),'<br />';
?>

This can be used in a recurring calendar events page to allow similar event creation like the recurring events in Microsoft Outlook or any other popular calender applications.

Tags: , ,

Format US phone number using PHP

May 21, 2010 PHP Coding

Have you ever needed to format a phone number in a particular way? The following code will format into the USA standard phone numbers and has the option to convert letters into their number form.

<?php
/*
Written by: Daniel Kassner
Website: http://www.danielkassner.com
Originally posted on: http://www.wlscripting.com
Date: 09-13-2007 and last updated: 05-21-2010
*/
if (!function_exists('format_phone_us')) {
	function format_phone_us($phone = '', $convert = true, $trim = true)
	{
		// If we have not entered a phone number just return empty
		if (empty($phone)) {
			return false;
		}

		// Strip out any extra characters that we do not need only keep letters and numbers
		$phone = preg_replace("/[^0-9A-Za-z]/", "", $phone);
		// Keep original phone in case of problems later on but without special characters
		$OriginalPhone = $phone;

		// If we have a number longer than 11 digits cut the string down to only 11
		// This is also only ran if we want to limit only to 11 characters
		if ($trim == true && strlen($phone)>11) {
			$phone = substr($phone, 0, 11);
		}

		// Do we want to convert phone numbers with letters to their number equivalent?
		// Samples are: 1-800-TERMINIX, 1-800-FLOWERS, 1-800-Petmeds
		if ($convert == true && !is_numeric($phone)) {
			$replace = array('2'=>array('a','b','c'),
							 '3'=>array('d','e','f'),
							 '4'=>array('g','h','i'),
							 '5'=>array('j','k','l'),
							 '6'=>array('m','n','o'),
							 '7'=>array('p','q','r','s'),
							 '8'=>array('t','u','v'),
							 '9'=>array('w','x','y','z'));

			// Replace each letter with a number
			// Notice this is case insensitive with the str_ireplace instead of str_replace 
			foreach($replace as $digit=>$letters) {
				$phone = str_ireplace($letters, $digit, $phone);
			}
		}

		$length = strlen($phone);
		// Perform phone number formatting here
		switch ($length) {
			case 7:
				// Format: xxx-xxxx
				return preg_replace("/([0-9a-zA-Z]{3})([0-9a-zA-Z]{4})/", "$1-$2", $phone);
			case 10:
				// Format: (xxx) xxx-xxxx
				return preg_replace("/([0-9a-zA-Z]{3})([0-9a-zA-Z]{3})([0-9a-zA-Z]{4})/", "($1) $2-$3", $phone);
			case 11:
				// Format: x(xxx) xxx-xxxx
				return preg_replace("/([0-9a-zA-Z]{1})([0-9a-zA-Z]{3})([0-9a-zA-Z]{3})([0-9a-zA-Z]{4})/", "$1($2) $3-$4", $phone);
			default:
				// Return original phone if not 7, 10 or 11 digits long
				return $OriginalPhone;
		}
	}
}
?>

To use the code:

<?php
$phone = '1-800-FLOWERS';
echo format_phone_us($phone); // Returns 1(800) 356-9377

$newPhone = format_phone_us($phone, false);
echo $newPhone; // Returns 1(800) FLO-WERS
?>

Tags: ,

PHP code list all Mondays for a year

May 20, 2010 PHP Coding

Ever need some code to list all the Mondays in a specified year? Here is some code to do just that:

<?php
/*
Author: Daniel Kassner
Website: http://www.danielkassner.com
*/
function getMondays($year) {
  $newyear = $year;
  $week = 0;
  $day = 0;
  $mo = 1;
  $mondays = array();
  $i = 1;
  while ($week != 1) {
   $day++;
   $week = date("w", mktime(0, 0, 0, $mo,$day, $year));
  }
  array_push($mondays,date("r", mktime(0, 0, 0, $mo,$day, $year)));
  while ($newyear == $year) {
   $test =  strtotime(date("r", mktime(0, 0, 0, $mo,$day, $year)) . "+" . $i . " week");
   $i++;
   if ($year == date("Y",$test)) {
     array_push($mondays,date("r", $test));
   }
   $newyear = date("Y",$test);
  }
  return $mondays;
}
echo '<pre>';
print_r(getMondays('2010'));
echo '</pre>';
?>

Tags: ,

Working with zero filled numbers in PHP

May 19, 2010 PHP Coding

A while ago I had posted some code to zero fill a number on another site I run. At the time it was the only way I knew of to perform that function. The code that I am referring to is listed below.

<?php
function zerofill ($num,$zerofill) {
    // Get the current string length of the original number
    // Loop through that number until it has reached the count in $zerofill
    while (strlen($num)<$zerofill) {
        $num = "0".$num;
    }
    return $num;
}
?>

And would be used such as:

<?php
// Output will be 005
echo zerofill(5, 3);
?>

What this code does is takes 2 inputs, one for the number and the other for the length you want the number to be. As you see above we input the numbers 5 and 3 into the function arguments. The output of the function would return 005 and not 5.

One of the comments left on my older posting suggested to use the built in PHP function sprintf instead of the while loop. At the time that was posted I did not have the time to look into the proper usage of that function and then I lost track of things and never got around to checking it out. This is why I am posting this article now about using sprintf for zero filling a number. Using the same function name and arguments I have re-written the code for zero filling a number with the sprintf function.

<?php
function zerofill ($num, $zerofill = 3) {
	return sprintf("%0".$zerofill."s", $num);
}
?>

I did a very quick non intense test to see which code functioned faster with a loop of about 10,000 numbers recording the microtime before and after the loop then calculating the difference. The sprint function appears to be slightly faster.

Now that we have a function to add the zeros to a number you might want to remove said zero’s from the number for use someplace else. Below is a function that will do just that. Just enter the number you want to remove the zeros from as the only argument and it will return the newly formatted number.

<?php
function zeroremove($value) {
    return preg_replace("/(\.\d+?)0+$/", "$1", $value)*1;
}
?>

Tags: ,

Using Notepad++’s Function List plugin for PHP development

Jan 22, 2010 PHP Coding, Web Development, Work

One thing I really love about Notepad++ is the plugin’s that are available. One of such plugins that I enjoy is the Function List plugin to automatically search my code to pull classes, functions and much more into an easy to see pane on the right side of my screen. I had previously posted tutorials on another site but now I have made the process for “installing” these new rules easier by posting the XML as well as adding more features. Once you have followed the below steps the Function List plugin will show all of the following:

  • PHP classes
  • PHP class variables – (public, protected and private)
  • PHP functions – already does this by default
  • CodeIgniter Views
  • CodeIgniter Helpers
  • CodeIgniter Libraries
  • CodeIgniter Models
  • PHP include()
  • PHP include_once
  • PHP require()
  • PHP require_once()

Update: I updated the instructions based on a comment below to close Notepad++ while editing the .xml file

Step 1: Close Notepad++
Step 2: Navigate to C:\Program Files\Notepad++\plugins\Config – This is the default installation folder, it may be different on your system.
Step 3: Open the FunctionListRules.xml file in your favorite editor other than Notepad++. The settings may not save if you open this file in Notepad++.
Step 4: Do a search for the following line: (This will more than likely be at the end of the file.)

<Language name="PHP" imagelistpath="">

Step 5: Add the following code between the opening and closing Language tags:

<Group name="Classes" subgroup="Functions" icon="0" child="0" autoexp="4" matchcase="1" fendtobbeg="" bbegtobend="\{" keywords="">
            <Rules regexbeg="class" regexfunc="[\w_ ]+" regexend="" bodybegin="\{" bodyend="\}" sep="" />
        </Group>
        <Group name="Class Variables" subgroup="" icon="0" child="0" autoexp="4" matchcase="0" fendtobbeg="" bbegtobend="" keywords="">
            <Rules regexbeg="public\ " regexfunc="\$.*[^;]" regexend=".*;" bodybegin="" bodyend="" sep="" />
            <Rules regexbeg="protected\ " regexfunc="\$.*[^;]" regexend=".*;" bodybegin="" bodyend="" sep="" />
            <Rules regexbeg="private\ " regexfunc="\$.*[^;]" regexend=".*;" bodybegin="" bodyend="" sep="" />
        </Group>
        <Group name="Functions" subgroup="" icon="0" child="0" autoexp="4" matchcase="1" fendtobbeg="" bbegtobend="\{" keywords="">
            <Rules regexbeg="function[\s&amp;]+" regexfunc="[\w_]+" regexend="\s*\(.*\)" bodybegin="\{" bodyend="\}" sep=";" />
        </Group>
        <Group name="CodeIgniter Views" subgroup="" icon="0" child="0" autoexp="4" matchcase="0" fendtobbeg="" bbegtobend="" keywords="">
            <Rules regexbeg='\$this-&gt;(.*)load-&gt;view\([&apos;&quot;]' regexfunc='[&quot;\/\w_ ]+' regexend="" bodybegin="" bodyend="" sep="" />
        </Group>
        <Group name="CodeIgniter Helpers" subgroup="" icon="0" child="0" autoexp="4" matchcase="0" fendtobbeg="" bbegtobend="" keywords="">
            <Rules regexbeg="\$this-&gt;(.*)load-&gt;helper\(array\(" regexfunc='[&apos;,&quot;\w_ ]+' regexend="" bodybegin="" bodyend="" sep="" />
            <Rules regexbeg='\$this-&gt;(.*)load-&gt;helper\([&apos;&quot;]' regexfunc='[&quot;\w_]+' regexend="" bodybegin="" bodyend="" sep="" />
        </Group>
        <Group name="CodeIgniter Libraries" subgroup="" icon="0" child="0" autoexp="4" matchcase="0" fendtobbeg="" bbegtobend="" keywords="">
            <Rules regexbeg="\$this-&gt;(.*)load-&gt;library\(array\(" regexfunc='[&apos;,&quot;\w_ ]+' regexend="" bodybegin="" bodyend="" sep="" />
            <Rules regexbeg='\$this-&gt;(.*)load-&gt;library\([&apos;&quot;]' regexfunc='[&quot;\w_]+' regexend="" bodybegin="" bodyend="" sep="" />
        </Group>
        <Group name="CodeIgniter Models" subgroup="" icon="0" child="0" autoexp="4" matchcase="0" fendtobbeg="" bbegtobend="" keywords="">
            <Rules regexbeg="\$this-&gt;(.*)load-&gt;model\(array\(" regexfunc='[&apos;,&quot;\w_ ]+' regexend="" bodybegin="" bodyend="" sep="" />
            <Rules regexbeg='\$this-&gt;(.*)load-&gt;model\([&apos;&quot;]' regexfunc='[&quot;\w_]+' regexend="" bodybegin="" bodyend="" sep="" />
        </Group>
        <Group name="Include" subgroup="" icon="0" child="0" autoexp="4" matchcase="0" fendtobbeg="" bbegtobend="" keywords="">
            <Rules regexbeg='include\([&apos;&quot;]' regexfunc="*.*[\w]" regexend="" bodybegin="" bodyend="" sep="" />
        </Group>
        <Group name="Include Once" subgroup="" icon="0" child="0" autoexp="4" matchcase="0" fendtobbeg="" bbegtobend="" keywords="">
            <Rules regexbeg='include_once\([&apos;&quot;]' regexfunc="*.*[\w]" regexend="" bodybegin="" bodyend="" sep="" />
        </Group>
        <Group name="Require" subgroup="" icon="0" child="0" autoexp="4" matchcase="0" fendtobbeg="" bbegtobend="" keywords="">
            <Rules regexbeg='require\([&apos;&quot;]' regexfunc="*.*[\w]" regexend="" bodybegin="" bodyend="" sep="" />
        </Group>
        <Group name="Require Once" subgroup="" icon="0" child="0" autoexp="4" matchcase="0" fendtobbeg="" bbegtobend="" keywords="">
            <Rules regexbeg='require_once\([&apos;&quot;]' regexfunc="*.*[\w]" regexend="" bodybegin="" bodyend="" sep="" />
        </Group>

My full section including the Language tags looks like this:

<Language name="PHP" imagelistpath="">
        <CommList param1="#" param2="" />
        <CommList param1="//" param2="" />
        <CommList param1="/\*" param2="\*/" />
        <Group name="Classes" subgroup="Functions" icon="0" child="0" autoexp="4" matchcase="1" fendtobbeg="" bbegtobend="\{" keywords="">
            <Rules regexbeg="class" regexfunc="[\w_ ]+" regexend="" bodybegin="\{" bodyend="\}" sep="" />
        </Group>
        <Group name="Class Variables" subgroup="" icon="0" child="0" autoexp="4" matchcase="0" fendtobbeg="" bbegtobend="" keywords="">
            <Rules regexbeg="public\ " regexfunc="\$.*[^;]" regexend=".*;" bodybegin="" bodyend="" sep="" />
            <Rules regexbeg="protected\ " regexfunc="\$.*[^;]" regexend=".*;" bodybegin="" bodyend="" sep="" />
            <Rules regexbeg="private\ " regexfunc="\$.*[^;]" regexend=".*;" bodybegin="" bodyend="" sep="" />
        </Group>
        <Group name="Functions" subgroup="" icon="0" child="0" autoexp="4" matchcase="1" fendtobbeg="" bbegtobend="\{" keywords="">
            <Rules regexbeg="function[\s&amp;]+" regexfunc="[\w_]+" regexend="\s*\(.*\)" bodybegin="\{" bodyend="\}" sep=";" />
        </Group>
        <Group name="CodeIgniter Views" subgroup="" icon="0" child="0" autoexp="4" matchcase="0" fendtobbeg="" bbegtobend="" keywords="">
            <Rules regexbeg='\$this-&gt;(.*)load-&gt;view\([&apos;&quot;]' regexfunc='[&quot;\/\w_ ]+' regexend="" bodybegin="" bodyend="" sep="" />
        </Group>
        <Group name="CodeIgniter Helpers" subgroup="" icon="0" child="0" autoexp="4" matchcase="0" fendtobbeg="" bbegtobend="" keywords="">
            <Rules regexbeg="\$this-&gt;(.*)load-&gt;helper\(array\(" regexfunc='[&apos;,&quot;\w_ ]+' regexend="" bodybegin="" bodyend="" sep="" />
            <Rules regexbeg='\$this-&gt;(.*)load-&gt;helper\([&apos;&quot;]' regexfunc='[&quot;\w_]+' regexend="" bodybegin="" bodyend="" sep="" />
        </Group>
        <Group name="CodeIgniter Libraries" subgroup="" icon="0" child="0" autoexp="4" matchcase="0" fendtobbeg="" bbegtobend="" keywords="">
            <Rules regexbeg="\$this-&gt;(.*)load-&gt;library\(array\(" regexfunc='[&apos;,&quot;\w_ ]+' regexend="" bodybegin="" bodyend="" sep="" />
            <Rules regexbeg='\$this-&gt;(.*)load-&gt;library\([&apos;&quot;]' regexfunc='[&quot;\w_]+' regexend="" bodybegin="" bodyend="" sep="" />
        </Group>
        <Group name="CodeIgniter Models" subgroup="" icon="0" child="0" autoexp="4" matchcase="0" fendtobbeg="" bbegtobend="" keywords="">
            <Rules regexbeg="\$this-&gt;(.*)load-&gt;model\(array\(" regexfunc='[&apos;,&quot;\w_ ]+' regexend="" bodybegin="" bodyend="" sep="" />
            <Rules regexbeg='\$this-&gt;(.*)load-&gt;model\([&apos;&quot;]' regexfunc='[&quot;\w_]+' regexend="" bodybegin="" bodyend="" sep="" />
        </Group>
        <Group name="Include" subgroup="" icon="0" child="0" autoexp="4" matchcase="0" fendtobbeg="" bbegtobend="" keywords="">
            <Rules regexbeg='include\([&apos;&quot;]' regexfunc="*.*[\w]" regexend="" bodybegin="" bodyend="" sep="" />
        </Group>
        <Group name="Include Once" subgroup="" icon="0" child="0" autoexp="4" matchcase="0" fendtobbeg="" bbegtobend="" keywords="">
            <Rules regexbeg='include_once\([&apos;&quot;]' regexfunc="*.*[\w]" regexend="" bodybegin="" bodyend="" sep="" />
        </Group>
        <Group name="Require" subgroup="" icon="0" child="0" autoexp="4" matchcase="0" fendtobbeg="" bbegtobend="" keywords="">
            <Rules regexbeg='require\([&apos;&quot;]' regexfunc="*.*[\w]" regexend="" bodybegin="" bodyend="" sep="" />
        </Group>
        <Group name="Require Once" subgroup="" icon="0" child="0" autoexp="4" matchcase="0" fendtobbeg="" bbegtobend="" keywords="">
            <Rules regexbeg='require_once\([&apos;&quot;]' regexfunc="*.*[\w]" regexend="" bodybegin="" bodyend="" sep="" />
        </Group>
    </Language>

Step 6: Save and close, then open up a PHP file in Notepad++ to see if it shows the desired output in the Function List tool.

Tags: , , , , ,

Calculate users age with PHP

Jan 19, 2010 PHP Coding, Web Development

Recently I needed some code to calculate a users age based on their birthday. There are many different functions that are already created out there that I could have used, but instead I wrote this piece of code. It uses PHP 5 DateTime class to return an array of information pertaining to how old a person is. By default if you leave the second parameter off it will calculate the difference from the start date to the current day. If you are trying to calculate how old somebody was when they had passed away or how old they were on a certain date you can pass the second parameter. There are several different checks in this code snippet to make sure you are using a valid date for the start and end date.

This function requires dates to be passed in standard: YYYY-MM-DD format
Code:

<?php
if (!function_exists('calculate_age')) {
        /*
        Author: Daniel Kassner
        Website: http://www.danielkassner.com
        */
	// Checked this function against http://www.timeanddate.com/date/duration.html

	function calculate_age($startdate = '0000-00-00', $enddate = '0000-00-00') {
		
		// Check to see if start date is valid

		@list($year, $month, $day) = explode('-', $startdate);
		if (@!checkdate($month,$day,$year)) {
			return false;
		}
		
		// Create a new datetime object to do our calculations with
		$s = new DateTime($startdate);
		// We have to have an end date so if it is empty or 0000-00-00 we will make it right now
		if (empty($enddate) OR $enddate == '0000-00-00') {
			$enddate = 'now';
		} else {
			// Check to see if end date is valid

			@list($year, $month, $day) = explode('-', $enddate);
			if (@!checkdate($month,$day,$year)) {
				return false;
			}
		}
		// Create a new datetime object to compare as an end date
		$e = new DateTime($enddate);
		
		if ($s->format('Ymd') > $e->format('Ymd')) {
			// End date cannot be before start date
			return false;
		}
		
		// Calculate our base numbers here
		$years = $e->format('Y')-$s->format('Y');
		$months = $e->format('m')-$s->format('m');
		$days = $e->format('d')-$s->format('d');

		// We cannot have negative days
		// Calculate how many days are in the startdate month
		// subtract the startdate day and add 1
		// Then add back in the day of the enddate
		// Subtract 1 month
		if ($days < 0) {
			$days = $s->format('t')-$s->format('d')+1;
			$days += $e->format('d');
			$months--;
		}
		
		// We cannot have negative months
		// Subtract 1 year and add back 12 months to make months positive
		if ($months < 0) {
			$years--;
			$months += 12;
		}

		return array('years'=>$years,'months'=>$months, 'days'=>$days);
	}
}
?>

Usage:

<?php
// Current date is 2010-01-19
$birthday = '1987-09-23';
$age = calculate_age($birthday);
var_dump($age);
echo '<hr />';
$startdate = '1985-09-06';
$enddate = '1987-09-23';
$difference = calculate_age($startdate, $enddate);
var_dump($difference);
?>

Output:

array(3) { ["years"]=> int(22) ["months"]=> int(3) ["days"]=> int(27) }
——————————————————————————————————–
array(3) { ["years"]=> int(2) ["months"]=> int(0) ["days"]=> int(17) }

Tags: ,

Creating a CSV file with PHP

Dec 31, 2009 PHP Coding, Technology, Web Development

While working on several coding projects I find myself needing to export data in CSV format. All this basically means is a text file with many lines of text separated by commas(,) which can then be opened up in a spreadsheet application or imported into another program. So for an example you have the following saved in a file called export.csv:

first value, second value, third value
fourth value, fifth value, sixth value

Every value before a comma is a new cell in the spreadsheet and everything after each new line is the start of a new row. Which means that the above values would produce something like the following in a spreadsheet application:

first value second value third value
fourth value fifth value sixth value

To accomplish this in PHP you can use arrays, joins and a few header functions to download the file. You could also save this output to a file instead of forcing the browser to download the content.

<?php
$row = array();
$row[] = 'first value';
$row[] = 'second value';
$row[] = 'third value';
$data .= join(',', $row)."\n"; // Join all values without any trailing commas and add a new line

$row = array(); // We must clear the previous values
$row[] = 'fourth value';
$row[] = 'fifth value';
$row[] = 'sixth value';
$data .= join(',', $row)."\n";

// Output the headers to download the file
header("Content-type: application/x-msdownload");
header("Content-Disposition: attachment; filename=log.csv");
header("Pragma: no-cache");
header("Expires: 0");
echo $data;
?>

Notice the join function adding the commas between the values and then finally adding the new line with the \n before finally outputting the data as a downloadable file.

Now you may be noticing one problem with the above code. Sure the code works but what happens if there is a comma, quote or new line in one of the $row variables? If you tried to put a comma between the first and value in our first value like:

first, value, second value, third value
fourth value, fifth value, sixth value

You would get:

first value second value third value
fourth value fifth value sixth value

Obviously not the output we were looking for. To fix this you have to escape the commas and new lines with quotes like:

"first, value", second value, third value
fourth value, fifth value, sixth value

Now if you are wanting to use quotes in your CSV file you have to escape those with double quotes such as:

"""first"" value", second value, third value
fourth value, fifth value, sixth value

Now that you know the above information about escaping values you can use the below function to escape all of the values before joining them with commas.

<?php
/*
Created by: Daniel Kassner
Website: http://www.danielkassner.com
*/
function escape_csv_value($value) {
	$value = str_replace('"', '""', $value); // First off escape all " and make them ""
	if(preg_match('/,/', $value) or preg_match("/\n/", $value) or preg_match('/"/', $value)) { // Check if I have any commas or new lines
		return '"'.$value.'"'; // If I have new lines or commas escape them
	} else {
		return $value; // If no new lines or commas just return the value
	}
}
?>

Usage:

<?php
$SafeValue = escape_csv_value('your value here');
?>

Now you may also be wondering about an easy way to export data straight from your MySQL database into a CSV file for download. Using some basic PHP functions for working with databases we can easily create a new export based on the query you run. To change the export just change the query that you run.

<?php
$db = mysql_connect('localhost', 'user', 'password'); // Connect to the database
$link = mysql_select_db('database name', $db); // Select the database name

/*
Created by: Daniel Kassner
Website: http://www.danielkassner.com
*/
function escape_csv_value($value) {
	$value = str_replace('"', '""', $value); // First off escape all " and make them ""
	if(preg_match('/,/', $value) or preg_match("/\n/", $value) or preg_match('/"/', $value)) { // Check if I have any commas or new lines
		return '"'.$value.'"'; // If I have new lines or commas escape them
	} else {
		return $value; // If no new lines or commas just return the value
	}
}

$sql = mysql_query("SELECT * FROM tablename"); // Start our query of the database
$numberFields = mysql_num_fields($sql); // Find out how many fields we are fetching

if($numberFields) { // Check if we need to output anything
	for($i=0; $i<$numberFields; $i++) {
		$keys[] = mysql_field_name($sql, $i); // Create array of the names for the loop of data below
		$head[] = escape_csv_value(mysql_field_name($sql, $i)); // Create and escape the headers for each column, this is the field name in the database
	}
	$headers = join(',', $head)."\n"; // Make our first row in the CSV
	
	$data = '';
	while($info = mysql_fetch_object($sql)) {
		foreach($keys as $fieldName) { // Loop through the array of headers as we fetch the data
			$row[] = escape_csv_value($info->$fieldName);
		} // End loop
		$data .= join(',', $row)."\n"; // Create a new row of data and append it to the last row
		$row = ''; // Clear the contents of the $row variable to start a new row
	}
	// Start our output of the CSV
	header("Content-type: application/x-msdownload");
	header("Content-Disposition: attachment; filename=log.csv");
	header("Pragma: no-cache");
	header("Expires: 0");
	echo $headers.$data;
} else {
	// Nothing needed to be output. Put an error message here or something.
	echo 'No data available for this CSV.';
}
?>

Please note that the above code also puts in a header row of what each of the columns is based on the database table fields. You can change what the headings are in your MySQL query by using the SQL as syntax. Examples:

SELECT fname, minitial, lname FROM users

Could be changed to:

SELECT fname as 'First Name', minitial as 'Middle Initial', lname as 'Last Name' FROM users

This would produce the desired output quickly and easily with a single MySQL query.

Enjoy working with CSV files that are properly escaped.

Tags: , , ,

Newer Entries

Switch to our mobile site