PHP

PHPExcel Help and Tips

I started using the PHPExcel module for outputting a bunch of results to Excel (or any application that can open Excel, such as Open Office, or Libre Office). One thing I noticed, was that the documentation was dodgy at best. So I’ve started taking notes for my own knowledge base.

If you want to write a cell value and ensure its a number:

$PHPExcelDocument->getActiveSheet()->setCellValue('D3', $number_to_add', PHPExcel_Cell_DataType::TYPE_NUMERIC);

Unfortunately (unlike the PEAR spreadsheet writer extension) you have to use cells, i.e. A1, D16 to reference the column/row to output something to, so you do need to keep a track of the cells you are using, etc. I wrote a small function to convert number columns to letters (for this reason):

/**
 * Takes a column number (i.e. 1) and converts it to a column letter (1 => A)
 * 
 * @param  integer $num
 * @return string
 */
function number_to_text($num) {
    return chr((int) $num+65);
}

Setting properties of the Excel file:

$spread = new PHPExcel();
$spread->getProperties()
    ->setTitle('My Export')
    ->setCreator('Chris Tate-Davies')
    ->setCreated(date('Y-m-d H:i:s'))
    ->setLastModifiedBy('Chris Tate-Davies');

Mac OSX 10.8 PHP, mysql.sock not found

I found that after install MySQL and Apache/PHP – that the database connections didn’t work from PHP. They worked if I tried to connect using the MySQL client though. Must be a problem with the PHP side.

There is a sock file that PHP was trying to use, and it was in /var/mysql/mysql.sock complaining that the file was not there.

Warning Error: PDO::__construct(): [2002] No such file or directory (trying to connect via unix:///var/mysql/mysql.sock) in [/filename.php, line number]

Sure enough, the file wasn’t there, and infact the folder was not there either. So I made a folder and created a symlink to the one that was there, in /tmp/mysql.sock

sudo mkdir /var/mysql
sudo ln -s /tmp/mysql.sock /var/mysql/mysql.sock

Restarted Apache and MySQL just to make sure. And hey presto its working.

sudo apachectl restart
sudo /usr/local/mysql/support-files/mysql.server restart

Error 403 on a localhost site on Mac

I was having trouble with my VirtualHosts here. Some sites worked but some did not.

It was down to the sites that use some sort of framework, and by adding the following code into the VirtualHost section for the misbehaving site:

<Directory /path/to/site/folder>
    Options +Indexes +FollowSymLinks +ExecCGI
    DirectoryIndex index.php
    AllowOverride All
    Order allow,deny
    Allow from all
</Directory>

I’m guessing it was preventing the mod_rewrite to do the re-writing.

Error 403 on a localhost site on Mac

I was having trouble with my VirtualHosts here. Some sites worked but some did not.

It was down to the sites that use some sort of framework, and by adding the following code into the VirtualHost section for the misbehaving site:

<Directory /path/to/site/folder>
    Options +Indexes +FollowSymLinks +ExecCGI
    DirectoryIndex index.php
    AllowOverride All
    Order allow,deny
    Allow from all
</Directory>

I’m guessing it was preventing the mod_rewrite to do the re-writing.

mysql_connect(): A connection attempt failed

mysql_connect(): A connection attempt failed because the connected party did not properly respond after a period of time

Shouldn’t be using these functions anymore anyway as they are discouraged, however, if you are using localhost as your database host, try using the IP address – 127.0.0.1 instead.

I had this problem on Windows 8 Customer Preview, and this has fixed it. I think there must be some confusion over the machine names, or something.

PHP Setting Error Reporting at runtime

You can use the PHP function error_reporting() at run time to set the level of error reporting in your application.

The different levels of reporting are based on bit masks or using a CONSTANT name. They are:

1       E_ERROR
2       E_WARNING
4       E_PARSE
8       E_NOTICE
16      E_CORE_ERROR
32      E_CORE_WARNING
64      E_COMPILE_ERROR
128     E_COMPILE_WARNING
256     E_USER_ERROR
512     E_USER_WARNING
1024    E_USER_NOTICE
2048    E_STRICT
4096    E_RECOVERABLE_ERROR
8192    E_DEPRECATED
16384   E_USER_DEPRECATED
32767   E_ALL

If you want to hide all errors : use zero:

error_reporting(0);

Otherwise, you can just use the bitmask or the name:

//show RECOVERABLE
error_reporting(4096);

//SHOW RECOVERABLE AND USER ERROR
error_reporting(4096 + 256);

//show warnings and deprecated
error_reporting(E_WARNING | E_DEPRECATED);

//show all except Notices
error_reporting(E_ALL ^ E_NOTICE);

Convert HTML table to Excel XLS

I recently needed to update a page that dumped a HTML table into Excel – and found it dumped everything, including hyperlinks, buttons, images, etc. This didn’t sit well with Excel for Mac, and probably not a good idea.

I thought I would re-write it, but in a way that was re-usable (as this code was situated on every datagrid in the application)

So, using the PEAR extension Spreadsheet_Excel_Writer and a simple HTML DOM class, I came up with this. Its by no means finished, but as it stands, it works pretty well for what I need. Feel free to rob it and use the code.

The call is from a form which uses jQuery to copy the table as HTML into an input and then uses that in the output script ($_REQUEST[‘gridcontent’])

When its all complete, I may put it on an open source repo, such as sourceforge.

FYI, there is a section in here that takes cell text with a
in it as a split cell. So if your TD has a BR in it, it will make all the other rows merged so that the data looks nice. This is by design.
Continue reading

Install Xdebug on Ubuntu

I always forget the little bit about error reporting, so here is a step by step guide to getting Xdebug running on you Ubuntu PHP installation.

1) Install PECL (if you don’t have it already)

> sudo apt-get install php-pear

2) Install Pear Xdebug extension

> sudo pecl install xdebug

3) Create a new config file and have it point at the new extension. Pay attention to the location of the xdebug.so extension in the above image. That is the path for this step.

> sudo nano /etc/php5/apache/conf.d/xdebug.ini

Add a line (if its not already there)

zend_extension=

I.e.

zend_extension=/usr/lib/php5/20090626+lfs/xdebug.so

4) Now, edit the PHP ini file to change the following settings

display_errors = On

and,

html_errors = On

 

Done, just restart apache and you should have nice var_dumps now