Category Archives: PHP Code

Posts related to PHP code and Tricks

Convert a tab-delimited file to SQL inserts

This is useful when I’m migrating data from one system to another. It uses the quotesplit code I talked about here: Parsing CSV data files with PHP, using quotesplit.

You want your source file to be tab delimited, with the header row containing the database field names that you are going to load each piece of data into. I use Excel to prepare the file. Then you run this file using the PHP interpreter at the console, and redirect the output to a .sql file. In the Mysql console, source your new .sql file. For convienience, I’ve attached the file tabdelim-to-sqlinsert.zip

You can use this for comma-separated or pipe-separated or whatever as well, if you change t in your call to quotesplit.

< ?php
  ini_set('display_errors',1); 
  error_reporting(E_ALL);

  # User config variables:
  $Filename = 'sourcedata-tabdelim-file.txt';
  $dbname = 'databasename';


  ####################################################
  function format($in)
  {
    $out = trim($in);
    if (($out == '') || ($out == 'NULL')) {
      return 'NULL';
    } elseif (preg_match ('/bd{1,2}/d{1,2}/d{4}b/', $out)) {
      # incorrectly formatted date detected (ie 10/31/2012 or 12/31/9999)

      $datetimeparts = explode(' ', $out);
      $dateparts = explode('/', $datetimeparts[0]); # consider date part only
      $out = $dateparts[2] . '-' . $dateparts[0] . '-' . $dateparts[1];
    }
    return "'" . $out . "'";
  }
  #######################################
  function RemoveArrayElement($array, $removeKey)
  {
    unset($array[$removeKey]);
      foreach ($array as $value)
        $return[] = $value;
    return ($return);
  }
  #######################################
  function DealWithMultipleSurroundingQuotes($splitter, &$getstrings)
  {
   for($x = 0; $x < count($getstrings); $x += 2) //foreach even key
   {
      if (!stristr($getstrings[$x], $splitter)) //if splitter is not in row
      {
         if (trim($getstrings[$x-1]) == '') //if previous row is empty
            //remove previous row
            $getstrings = RemoveArrayElement($getstrings, $x-1);
         else
            //remove current row
            $getstrings = RemoveArrayElement($getstrings, $x);

         return false;
      }
   }
   return true; //Function finished successfully!
  }
  #######################################
  function quotesplit( $splitter=',', $s, $restore_quotes=false )
  {
   # First step is to split it up into the bits that are surrounded by quotes
   # and the bits that aren't. Adding the delimiter to the ends simplifies
   # the logic further down

   $getstrings = explode('"', $splitter . $s . $splitter);

   while(!DealWithMultipleSurroundingQuotes($splitter, $getstrings));

   # $instring toggles so we know if we are in a quoted string or not
   $delimlen = strlen($splitter);
   $instring = 0;

   while (list($arg, $val) = each($getstrings))
   {
      if ($instring == 1)
      {
         if($restore_quotes)
         {
            # Add string with quotes to the previous value in the array
            $result[count($result)-1] = $result[count($result)-1]. '"' . addslashes(trim($val)) . '"';
         } else {
            # Add the whole string, untouched to the array
            $result[count($result)-1] = addslashes(trim($val));
         }
         $instring = 0;
      } else {
         # Break up the string according to the delimiter character
         # Each string has extraneous delimiters around it (inc the ones
         #  we added above), so they need to be stripped off
         $temparray = explode($splitter, substr($val, $delimlen, strlen($val)-$delimlen-$delimlen+1 ) );
         while(list($iarg, $ival) = each($temparray))
            $result[] = addslashes(trim($ival));
         $instring = 1;
      }
   }
   return $result;
  }

  ####################################################
  $file = fopen($Filename, 'r');
  if($file == false)
  {
    print 'Error in opening file';
    exit();
  }
  $Filesize = filesize($Filename);
  $filerow = fgets($file, $Filesize);
  $headerrow = quotesplit("t", $filerow);
  $numcols = 0;

  while($filerow = fgets($file, $Filesize))
  {
    $row = quotesplit("t", $filerow);
    print 'INSERT INTO `' . $dbname . '` (';
    $first = true;
    foreach ($headerrow as $field)
    {
      if ($field == '') {
        # Do nothing
      } elseif ($first) {
        print '`' . $field . '`';
        $first = false;
        $numcols+=1;
      } else {
        print ', `' . $field . '`';
        $numcols+=1;
      }
    }
    print ') VALUES (';
    for ($i=0; $i

To use PhpStorm with Ubuntu Vagrant install Xdebug for PHP

How the Vagrant box is configured

To be able to step through code using the IDE of our choice, we need to install Xdebug onto our Vagrant Box.

Good instructions here: http://ubuntuforums.org/showthread.php?t=525257

Updates to the Provisioning Script:

Install php5-dev php-pear using apt-get, install xdebug using pecl, creating a properly owned folder in /var/log to store the xdebug log file.

Added to apt-get packages list:

php5-dev #needed for xdebug
php-pear #needed for xdebug

Also added:

echo "Creating xdebug log directory: /var/log/xdebug"
mkdir /var/log/xdebug
echo "Changing xdebug log directory owner to www-data"
chown www-data:www-data /var/log/xdebug

echo "Installing xdebug"
pecl install xdebug

Needed to add the following to the php.ini to configure it for xdebug:

;;;;;;;;;;;;;;;;;;;;;;;;;;
; Added to enable Xdebug ;
;;;;;;;;;;;;;;;;;;;;;;;;;;
; use the following command to find xdebug.so:
; find / -name 'xdebug.so' 2> /dev/null
zend_extension="/usr/lib/php5/20100525/xdebug.so"
xdebug.default_enable = 1
xdebug.idekey = "vagrant"
xdebug.remote_enable = 1
xdebug.remote_autostart = 0
xdebug.remote_port = 9000
xdebug.remote_handler=dbgp
xdebug.remote_log="/var/log/xdebug/xdebug.log"
xdebug.remote_host=10.0.2.2 ; IDE-Environments IP, from vagrant box.

How to configure your IDE

Your IDE needs to listen for connections on port 9000.

Your IDE needs to know the host IP as well as the path the files reside in on the server.

Your IDE (or you, via a url) need to start and stop xdebug.

Configuring PHPStorm

Create Project

First, create a new project. If you’re using SVN, configure PhpStorm to use SVN and check out the project into a new folder.

Add Remote Server

File -> Settings

Under Project Settings [project-name] on the left, browse to PHP -> Servers

Click the green +

Name: 192.168.50.4

Host: 192.168.50.4

Port: 80

Debugger: Xdebug

Check “Use path mappings (select if the server is remote or symlinks are used)”

Under File/Directory on the left, Browse to Project Files -> checkoutdirwwwproject-name

Next to the www directory, on the right under Absolute path on the server, enter: /data/www (or wherever your files are stored on the vagrant box)

Click OK

Add Debug Config

Under the Run dropdown menu, click Edit Configurations…

Click PHP Web Application, then click the green +

Name: vagrant

Server: 192.168.50.4 should be in the dropdown

Start URL: /

Browser: Chrome

Start Listening for Debug Connections

There’s a telephone icon in the icon bar, with a very small green bug and a red circle with a line through it.
Hovering over it, it will say Start Listen PHP Debug Connections
Clicking that will start the listener.

OR

Click Run -> Start Listen PHP Debug Connections

Start Debugger

Make sure vagrant is selected in the drop-down next to the green play arrow icon in the header.

Click the green bug icon in the header (hover text is Debug ‘vagrant’ Shift+F9)

OR

Click Run -> Debug vagrant

A new browser window will open with a url something like: “http://192.168.50.4/?XDEBUG_SESSION_START=15172”

If you have a breakpoint defined, the browser will appear to ‘hang’ or ‘spin’ or ‘load’ forever. It’s waiting for the IDE to give it the go signal. Press F9 OR click Run -> Resume Program to finish loading the page.

Debugging / Stepping through the program execution

First off, you need to have a breakpoint set, or you will not stop program execution, and so will not have the opportunity to utilize the debugger.

Set a breakpoint by clicking in the left margin right next to the code to put a red dot there.

Now click a link on the application in the browser, and the server will execute all the code up to the red dot, and stop, waiting for a command.

Pressing F7 for Step into will make sure to execute every single line of code

Pressing F8 for Step over will go to the next line but basically never leave the current file: it will not dive down into function calls. This is useful for skipping stuff you know you don’t care about.

Stopping the Debugger

Ctrl+F2,

Run -> Stop, or

Clicking the red square in the debug window all stop the PhpStorm debugger.

However, if you click a link on the browser it will start right back up again. It needs to send a header to the xdebug server and… it doesn’t appear to. We can send that ourselves.

1) Click Stop

2) Follow this link: http://192.168.50.4/?XDEBUG_SESSION_STOP

Send email from PHP to a log file instead of sendmail

Say you want to test email sent from a PHP application on your development environment, and you don’t want to set up sendmail. You can write a little PHP script to replace the sendmail call!

First, create the following file: /usr/local/bin/phpsendmail


#!/usr/bin/php
<?php
	$logfile = '/data/www/ap/sent-mail.htm';
	//* Get the email content
	$log_output = "<p>****" . date('Y-m-d H:i:s') . "****</p>rn";
	$handle = fopen('php://stdin', 'r');
	$count = 0;
	while(!feof($handle)) 
	{
		$count++;
		$buffer = trim(fgets($handle));
		if ($count <= 12) # Output header information
			$log_output .= $count . ": " . $buffer . "<br>rn";
		else # Output body
			$log_output .= $buffer . "rn";
	}
	//* Write the log
	file_put_contents($logfile, $log_output, FILE_APPEND);
?>

Then, edit your php.ini (mine is here: /etc/php5/apache2/php.ini) so that the mail portion of the file looks like this (note the commented-out lines and the sendmail_path):


[mail function]
; For Win32 only.
; http://php.net/smtp
; SMTP = localhost
; http://php.net/smtp-port
; smtp_port = 25

; For Win32 only.
; http://php.net/sendmail-from
; sendmail_from = me@example.com

; For Unix only.  You may supply arguments as well (default: "sendmail -t -i").
; http://php.net/sendmail-path
; sendmail_path =
sendmail_path = /usr/local/bin/phpsendmail

Restart apache (mine restarts with: sudo /etc/init.d/apache2 restart)

Now send a test email using your php script. If you want, you can create this file in your www root and run it:

<?php
  $name = "From PHP"; //senders name 
  $email = "testfromaddress@domain.edu"; //senders e-mail adress 
  $recipient = "testsenttoaddress@domain.edu"; //recipient 
  $mail_body = "The text for the mail...rnhi... this is the second line of the body text.rnThirdrnand fourth lines."; //mail body 
  $subject = "Subject for reviever"; //subject 
  $header = "From: ". $name . " <" . $email . ">rn"; //optional headerfields 

  echo date('h:i:s A') . ' *** ';
  if (mail($recipient, $subject, $mail_body, $header) === true)
    echo 'Mail sent successfully.'; 
  else
    echo 'Mail could not be sent.';
?>


Now you should have a sent-mail.htm in your www root! Browse to it with your web browser!

The following script and idea is modified from this website: http://www.howtoforge.com/how-to-log-emails-sent-with-phps-mail-function-to-detect-form-spam

Errors while trying to connect to Microsoft SQL Server using FreeTDS on Apache/PHP

If you get one of the following errors:


odbc_connect(): SQL error: [iODBC][Driver Manager]Specified driver could not be loaded, SQL state IM003 in SQLConnect

Ensure the path to the driver is correct. We are pointing to a file called libtdsodbc.so.


odbc_connect(): SQL error: [FreeTDS][SQL Server]Unable to connect: Adaptive Server is unavailable or does not exist, SQL state 08S01 in SQLConnect

Ensure that the Port number is correct.


odbc_connect(): SQL error: [FreeTDS][SQL Server]Unknown host machine name., SQL state 01000 in SQLConnect

Ensure that the Address is correct. Use only dns name, no /database name at the end.


odbc_connect(): SQL error: [FreeTDS][SQL Server]Login failed for user 'username'., SQL state 42000 in SQLConnect

Ensure that the username and password is correct. If you have a domain user, make sure to enter the username as 'domainusername'. (If the username is in double-quotes, be sure to escape the slash).


Configuring PhpStorm to make a Gvim user happy

PhpStorm 5

That gvim user is me! I like to say I’m a vi-guy, but I’ve learned that I have to type ‘vim’ at the console to be consistently happy with the editor I get in Linux. I run Windows on the desktop, and I’ve been using gvim for years, but… after inheriting another coder’s codebase, it’s clearly time for some more sophisticated tools.

Enter PhpStorm!

However, I like my keyboard commands. Luckily, there’s an app for that: IdeaVim. I found it a bit tricky to install, even though it’s dead simple.

Installation Steps for IdeaVim:

  1. Download and Extract the plugin.
  2. File -> Settings -> IDE Settings -> Plugins -> Install plugin from disk…
  3. Browse to (extracted zip folder) “IdeaVim” -> lib -> IdeaVim.jar.
  4. Next, browse to “/.PhpStorm/config/keymaps” In my case, this is: C:Userscurreri.WebIde50configkeymaps
  5. Copy (extracted zip folder) “IdeaVim” -> Vim.xml into C:Userscurreri.WebIde50configkeymaps
  6. Done!

Change the Font to Vera Sans Mono.

  1. File -> Settings -> IDE Settings -> Editor -> Colors & Fonts -> Font
  2. Save the Default Scheme as something new, say: Default-Modified
  3. Click the … and find: Bitstream Vera Sans Mono

Update: PHPStorm 6, PHPStorm 7

  1. After updating I don’t seem to have the IDEVim plugin installed. I haven’t used this software for a long time, I’m going to try going without.
  2. Vera Sans Mono isn’t available now either, apparently. So do:
    1. File -> Settings-> Editor -> Appearance -> Colors & Fonts -> Fonts
    2. Save Default Scheme as Default-Modified
    3. Select Source Code Pro as the only selected font.
  3. Tab settings are weird. Go to File -> Settings -> Code Style -> PHP. Check Use Tab character and Smart Tabs. Set all size boxes to 2.
  4. Click Wrapping and Braces, change the force braces to always, and check if() ‘else’ on new line.
  5. Uncheck comments at first column.
  6. On the Other tab, check Align key-value pairs and Align consecutive assignments
  7. File->Settubgs->IDE Settings->Appearance, change Theme to Alloy. Default Theme.

Installing PHPUnit on Windows

Hello, just dropping in to post a link. I use WAMP to do PHP development on Windows 7 (though with the amount of open source software I use, I wonder why I don’t just give Linux on the desktop another go. It has been about ten years since I used it last, I bet it’s loads better now!)

Anyway, this was exactly what I needed to get PHPUnit working on my WAMP install (which also didn’t have PEAR).

http://mnshankar.wordpress.com/2011/02/26/installing-pear-on-wamp/

Thanks, Internet!

Parsing CSV data files with PHP, using quotesplit

I originally got this function from the comments on this page: http://us2.php.net/manual/en/function.split.php. But I recently put a bit of time into making it compatible with fields quoted with multiple quotes. This function can deal with input like:

"one"," "two"", """three"""

It will parse data that is not CSV (comma separated values) as well, just pass in a different delimiter.

I have two helper functions here, one which removes an element from the array, then rebuilds the array to re-create the array keys. The other deals with the multiple quote issue by stepping through the initial array and removing the extra rows created due to the fact that we have multiple quotes.

	
#######################################
function RemoveArrayElement($array, $removeKey)
{
   unset($array[$removeKey]);
      foreach ($array as $value)
         $return[] = $value;
   return ($return);
}
#######################################
function DealWithMultipleSurroundingQuotes($splitter, &$getstrings)
{
   for($x = 0; $x < count($getstrings); $x += 2) //foreach even key
   {
      if (!stristr($getstrings[$x], $splitter)) //if splitter is not in row
      {
         if (trim($getstrings[$x-1]) == '') //if previous row is empty
            //remove previous row
            $getstrings = RemoveArrayElement($getstrings, $x-1);
         else
            //remove current row
            $getstrings = RemoveArrayElement($getstrings, $x);

         return false;
      }
   }
   return true; //Function finished successfully!
}
#######################################
function quotesplit( $splitter=',', $s, $restore_quotes=false )
{
   # First step is to split it up into the bits that are surrounded by quotes
   # and the bits that aren't. Adding the delimiter to the ends simplifies
   # the logic further down

   $getstrings = explode('"', $splitter . $s . $splitter);

   while(!DealWithMultipleSurroundingQuotes($splitter, $getstrings));

   # $instring toggles so we know if we are in a quoted string or not
   $delimlen = strlen($splitter);
   $instring = 0;

   while (list($arg, $val) = each($getstrings))
   {
      if ($instring == 1)
      {
         if($restore_quotes)
         {
            # Add string with quotes to the previous value in the array
            $result[count($result)-1] = $result[count($result)-1]. '"' . addslashes(trim($val)) . '"';
         } else {
            # Add the whole string, untouched to the array
            $result[count($result)-1] = addslashes(trim($val));
         }
         $instring = 0;
      } else {
         # Break up the string according to the delimiter character
         # Each string has extraneous delimiters around it (inc the ones
         #  we added above), so they need to be stripped off
         $temparray = split($splitter, substr($val, $delimlen, strlen($val)-$delimlen-$delimlen+1 ) );
         while(list($iarg, $ival) = each($temparray))
            $result[] = addslashes(trim($ival));
         $instring = 1;
      }
   }
   return $result;
}

PHP file download error in Internet Explorer 7

When trying to download a file that is auto-generated on the fly by a PHP script I wrote that sends excel headers, Internet Explorer users were getting the error:

Windows Internet Explorer
Internet Explorer cannot download [filename] from [website].
Internet Explorer was not able to open this Internet site. The requested site is either unavailable or cannot be found. Please try again later.

This is caused by doing a session_start(). If you don't actually need a the session in your php file you can remove it, and IE users will be able to download whatever your PHP script is making. The problem is session_start() by default sends a cache control header including "no-store". Internet Explorer has a bug with sending a cache header.

If you need the session, then before session_start(), add "session_cache_limiter('none');"

Problem solved!