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 ('/\b\d{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<$numcols; $i+=1)
    {
      if ($i==0) {
        print format($row[$i]);
      } else {
        print ', ';
        if (($headerrow[$i] == 'JobClass') || ($headerrow[$i] == 'JobClassCode') || ($headerrow[$i] == 'topadminJobClassCode'))
          print format(str_pad($row[$i], 4, "0", STR_PAD_LEFT));
        else
          print format($row[$i]);
      }
    }
    print ");\n";
    $numcols = 0;
    #   exit();
  }
  fclose($file);

?>

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 -> \checkoutdir\www\project-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>\r\n";
	$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>\r\n";
		else # Output body
			$log_output .= $buffer . "\r\n";
	}
	//* 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...\r\nhi... this is the second line of the body text.\r\nThird\r\nand fourth lines."; //mail body 
  $subject = "Subject for reviever"; //subject 
  $header = "From: ". $name . " <" . $email . ">\r\n"; //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

Set MySQL @variable for hostname user grants

The user grant syntax in mysql is problematic if you want to use a variable to set the hostname when you are creating users in a mysql script. The @ notation means the start of a variable normally, but it’s also used in the mysql grants… so if you try to do this:

SET @hostname='localhost';
GRANT SELECT, SHOW VIEW ON *.* TO 'username'@'@hostname' IDENTIFIED BY 'pass';

You get the error:

Lookup Error - MySQL Database Error: Malformed hostname (illegal symbol: '@')

Or if you take the single quotes out, you just get a syntax error.

The solution is to use a placeholder when you do the create user grants, then later replace all instances of the placeholder with the variable.

/* uncomment to set appropriate environment */
SET @hostname='localhost'; /* development */
/* SET @hostname='testing.hostname.com'; */
/* SET @hostname='production.hostname.com'; */

GRANT SELECT ON db_name.table_name TO 'username'@'env' IDENTIFIED BY 'pass';
GRANT SELECT, INSERT ON db_name.* TO 'username2'@'env' IDENTIFIED BY 'pass';
GRANT SELECT ON *.* TO 'username3'@'env' IDENTIFIED BY 'pass';
/* ... */

UPDATE mysql.user SET host = @hostname WHERE host = 'env';
UPDATE mysql.db SET host = @hostname WHERE host = 'env';
UPDATE mysql.tables_priv SET host = @hostname WHERE host = 'env';
FLUSH PRIVILEGES;

Remap Caps Lock key to Ctrl in Windows 7

I love having Control on the home row. To turn your (frankly useless and annoying) caps lock key into the ctrl key, browse to this key in regedit:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Keyboard Layout

Set it to this Hex code:

00,00,00,00,00,00,00,00,02,00,00,00,1d,00,3a,00,00,00,00,00

OR use the registry modification files they made here:
http://www.howtogeek.com/howto/windows-vista/disable-caps-lock-key-in-windows-vista/
(look for the Download Keyboard Mappings Registry Tweaks link)