Web Design & Development Blog
RSS Feed
Subscribe to my
RSS feed

The Web Design & Development Blog is written, coded and maintained by Duncan Midwinter. It regularly features articles on HTML, CSS, Javascript, PHP, Web Design and more…

Posts for tag: PHP 4

How I created a set of Pantone swatches for the Mac OSX color picker

For a long time now I have wanted to have a full set of Pantone swatches in the Mac OSX color picker. I searched for a quick solution online but never found anything. So in the end I realised that if this was going to happen I would have to do something about it myself…

pantone colors in the Mac OSX color picker
Pantone Swatches in the Mac OSX Color Picker


Stage 1 – getting a list of swatches

Whilst it’s very easy to create a custom color palette and add your own colors to it I had no particular desire to try and enter an entire Pantone swatch book by hand. A quick search online revealed several sites with lists of swatches and their respective rgb values. So that’s great – the difficult part has already been done by someone else! I checked out a few lists and in the end settled on this one: http://en.labelpartners.com/pantone_coated_table.html which gave a full set with hex and rgb values laid out in a <table> just perfect for scraping with php.

Stage 2 – scraping the data into a database

The next task was to get the data off the webpage and into a database. For tasks like this I always use PHP Simple HTML DOM Parser. It’s very easy to learn – and makes this kind of task very simple.

Before writing a script to scrape the data I set up a myqsl database ‘pantone_swatches’ with a table ‘coated_swatches’ to hold the data. The table structure was as follows:

CREATE TABLE `coated_swatches` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `hex` varchar(7) DEFAULT NULL,
  `red` varchar(5) DEFAULT NULL,
  `green` varchar(5) DEFAULT NULL,
  `blue` varchar(5) DEFAULT NULL,
  PRIMARY KEY (`id`)

At this point I wasn’t really sure if I needed the rgb values or the hex values so I decided to get everything just in case.

Now, with the database ready it was time to scrape the data. I wrote the following php script making use of PHP Simple HTML DOM Parser to sort through each table row (<tr>) and get the text content from each table data cell (<td>) and inset as a row in the database:

//connect to mysql...
$cxn = mysqli_connect ($host, $user, $password, $database)
or die (mysqli_error($cxn));

//include simple html dom...

//get page source...
$html = file_get_html('http://en.labelpartners.com/pantone_coated_table.html');

$count = 0;

//loop through each <tr>...
foreach($html->find('tr') as $row) {

	//extract values from each <td>...    
	$name 	= $row->find('td', 0)->innertext;		
	$hex 	= $row->find('td', 1)->innertext;
	$red 	= $row->find('td', 2)->innertext;
	$green 	= $row->find('td', 3)->innertext;
	$blue 	= $row->find('td', 4)->innertext;
	//ignore <table> 'header' row...
	if($count > 0) {
		echo $count.': '.$name.'<br>';
		//inset in to db...
			(name, hex, red, green, blue)
			('$name', '$hex', '$red', '$green', '$blue')
		$result = mysqli_query ($cxn,$query) or die (mysqli_error($cxn));		

Once the script was ready I launched it and waited for it to complete then checked out the new table to see if everything was OK. The script performed perfectly but now, how to get those values into the Mac OSX color picker?

Stage 3 – building a list of NSColors

You can manually create a custom color palette by opening the color picker, clicking on the third tab ‘color palettes’ and then clicking on the small ‘gear’ icon and selecting ‘new’. When you do this the new palette is saved in ~/Library/Colors/ with .clr for its file extension. The first thing I tried to do was open one of these .clr files in a text editor. Once I had tried that I quickly realised it wasn’t as simple as copying the structure and saving a new file! So, back to the internet and a bit more research revealed a way to generate a .clr file using Xcode. .clr files can be generated by creating a new NSColorList object and setting colors, like this:

//create list
NSColorList *list = [NSColorList.alloc initWithName:@"My Custom Color Palette"];

//add colors
[list setColor:[NSColor colorWithRed:0.831 green:0.835 blue:0.831 alpha:1.0] forKey:@"Custom Color 1"];
[list setColor:[NSColor colorWithRed:0.765 green:0.773 blue:0.769 alpha:1.0] forKey:@"Custom Color 2"];
[list setColor:[NSColor colorWithRed:0.725 green:0.733 blue:0.733 alpha:1.0] forKey:@"Custom Color 3"];

//save file to ~/Library/Colors/
[list writeToFile: [NSHomeDirectory() stringByAppendingPathComponent:@"Library/Colors/"]];

So now, all I needed to do was generate the objective-c code from the data in the database, the only difference being that the rgb values in the database range from 0 to 255 whereas the rgb values for the NSColor class range from 0 to 1. This small difference was rectified by dividing the database rgb values by 255 and rounding the result to 3 decimal places. The code I used to output the objective-c code was as follows:

//connect to mysql...
$cxn = mysqli_connect ($host, $user, $password, $database)
or die (mysqli_error($cxn));

//get data...
$query = "SELECT * FROM $table";
$result = mysqli_query ($cxn,$query)
		or die (mysqli_error($cxn));
$nrows = mysqli_num_rows ($result);

//loop through data...
for($i=0;$i<$nrows;$i++) {

	$row = mysqli_fetch_assoc ($result);
	extract ($row);
	//divide rgb values by 255 and echo...
	echo '[list setColor:[NSColor colorWithRed:'.round(($red/255),3).' green:'.round(($green/255),3).' blue:'.round(($blue/255),3).' alpha:1.0] forKey:@"Pantone '.$name.'"];<br />';	

Which when executed produced the following result:

[list setColor:[NSColor colorWithRed:0.996 green:0.867 blue:0 alpha:1.0] forKey:@"Pantone Yellow C"];
[list setColor:[NSColor colorWithRed:1 green:0.843 blue:0 alpha:1.0] forKey:@"Pantone Yellow 012 C"];
[list setColor:[NSColor colorWithRed:0.996 green:0.314 blue:0 alpha:1.0] forKey:@"Pantone Orange 021 C"];
// etc…
// etc…
// etc…

Stage 4 – using Xcode to generate the .clr file

To generate the final .clr file I opened Xcode and created a new project of type Cococa Application. Once this was ready I opened the AppDelegate.m file and where the comment ‘// Insert code here to initialize your application’ appeared in the – (void)applicationDidFinishLaunching:(NSNotification *)aNotification method I replaced it with the following:

NSColorList *list = [NSColorList.alloc initWithName:@"Pantone Coated"];


[list writeToFile: [NSHomeDirectory() stringByAppendingPathComponent:@"Library/Colors/"]];

Once that was done I did a build and run and as soon as the application had launched I checked out the contents of my ~/Library/Colors/ folder to see a new Pantone Coated.clr file. The next thing to do was open an app (Sketch.app) and then open the color picker to check out the new color palette!

Using Pantone swatches in Sketch
Using Pantone swatches in Sketch



In hindsight the way I went about this was perhaps more lengthy that it could have been. There’s no reason why I couldn’t scrape the page and generate the objective-c code all in one step missing out the database. But as I was not sure how to proceed it seemed better to store the data then work out what to do next. Anyway here’s a short example that does just that:

//include simple html dom...

//get page source...
$html = file_get_html('http://en.labelpartners.com/pantone_coated_table.html');

$count = 0;

echo 'NSColorList *list = [NSColorList.alloc initWithName:@"Pantone Coated"];';

//loop through each <tr>...
foreach($html->find('tr') as $row) {

	//extract values from each <td>...    
	$name 	= $row->find('td', 0)->innertext;		
	$hex 	= $row->find('td', 1)->innertext;
	$red 	= $row->find('td', 2)->innertext;
	$green 	= $row->find('td', 3)->innertext;
	$blue 	= $row->find('td', 4)->innertext;
	//ignore <table> 'header' row...
	if($count > 0) {
		//divide rgb values by 255 and echo objective-c code...
		echo '[list setColor:[NSColor colorWithRed:'.round(($red/255),3).' green:'.round(($green/255),3).' blue:'.round(($blue/255),3).' alpha:1.0] forKey:@"Pantone '.$name.'"];<br />';

echo '[list writeToFile: [NSHomeDirectory() stringByAppendingPathComponent:@"Library/Colors/"]];';

There are many sites online with lists of Pantone colors. I also creates a Pantone Pastels, Pantone Metallics and a set of CMYK swatches using the CMYK values and creating the NSColors like this:

[NSColor colorWithDeviceCyan: C_VALUE magenta: M_VALUE yellow: Y_VALUE black: K_VALUE alpha:1.0]


Color Lists
PHP Simple HTML DOM Parser


You can download an archive of compiled .clr containing a set of Pantone Coated, Metallic, Pastel and CMYK swatches on my resources page:
Pantone Swatches for the OSX Color Picker

Ad campaign tracking with Magento

Ad campaign tracking with the Magento e-commerce system

I’m going to show you how to track ad campaigns in Magento to find out how many orders they are generating.

Imagine you have an ad campaign running on Facebook for your Magento store and you want to know how many of those clicks are being converted in to orders. The way to achieve this is to set a cookie for all visitors who arrive to your site via Facebook. If you give each cookie generated a unique id and then store the data in a database, you will be able to track each visitor if they then return within a given period and place an order in your store. To do this you will need to add a new table to your Magento database, add a few new files, and edit a few files…

1 First up, you will need to check your theme for the following files:


Don’t worry if they don’t exist in your theme. If you don’t have them, simply copy them in to your theme from the following locations:


Now, you will be able to modify these files without disturbing your installation of Magento.

2 Now, you will need to create a new template file for your ‘success’ page. The ‘success’ page is the page that you are sent to after completing an order. Don’t worry, this is not complicated — it’s just a case of duplicating the template file that you are currently using for the checkout process, and giving it a new name. In my case I am using a template file called ‘2columns-left.phtml’, so I duplicate it and rename it ‘2columns-left-success.phtml’.

The template files for your theme are located here:


3 Now, to get Magento to use your new template for the ‘success’ page, you will need to edit the ‘checkout.xml’ file that you copied in to your theme in stage 1.

Open it, and locate the following line (line 412):

<action method="setTemplate"><template>page/2columns-left.phtml</template></action>

Edit it to use your newly created template file:

<action method="setTemplate"><template>page/2columns-left-success.phtml</template></action>

4 OK, now it’s time to work on the database to store your tracking data. You need to create a new table in your Magento database called ‘tracking’ with the following structure:

| Field     | Type          | Null | Key | Default | Extra |
| id        | varchar(16)   | NO   |     | NULL    |       |
| source    | varchar(60)   | YES  |     | NULL    |       |
| time      | datetime      | YES  |     | NULL    |       |
| sale      | enum('0','1') | NO   |     | 0       |       |
| sale_time | datetime      | YES  |     | NULL    |       |
| order_id  | varchar(50)   | YES  |     | NULL    |       |

You will be storing the following data:

  • id – this will be a random 16 chr string that will be used as the name of each cookie created.
  • source – this will be the value of the cookie, and will contain the domain of the referring site.
  • time – this will contain the date and time of the cookie’s creation.
  • sale – a simple boolean, 0 if no purchase, 1 if a purchase is made before the cookie expires.
  • sale_time – if a purchase is made, this field will contain the date and time of the purchase.
  • order_id – finally, if a purchase is made, this field will contain the corresponding order number.

Here’s the SQL to create the table:

CREATE TABLE `tracking` (
  `id` varchar(16) NOT NULL,
  `source` varchar(60) DEFAULT NULL,
  `time` datetime DEFAULT NULL,
  `sale` enum('0','1') NOT NULL DEFAULT '0',
  `sale_time` datetime DEFAULT NULL,
  `order_id` varchar(50) DEFAULT NULL

5 Now that you have the database set up it’s time to start adding the necessary code. Firstly, in the root level of your Magento site, create a new file named ‘cookie_settings.php’.

Add the following php to this file and save it:


## add all domains you wish to track to this array ##

$cookie_arr[0] = "www.google.com";
$cookie_arr[1] = "anet.tradedoubler.com";
$cookie_arr[2] = "www.facebook.com";

## cookie parameters ##

$number_of_days = 30;

$cookie_path = '/';

$cookie_domain = '.yourdomain.com';


This pretty much speaks for it’s self.

Add any domains you want to track to the $cookie_arr array.
Set $number_of_days to the amount of days you want each tracking cookie to last for.
Leave $cookie_path as is.
Set $cookie_domain to the name of your domain (careful, don’t omit that leading full stop).

6 The landing page code. Open the template that is used for your landing page, and add the following code to the top of the template, the comments in the file pretty much sum it up:

//random string function (source: http://www.lateralcode.com/creating-a-random-string-with-php/)
function rand_string( $length ) {
		$chars = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";	
		$size = strlen( $chars );
		for( $i = 0; $i < $length; $i++ ) {
			$str .= $chars[ rand( 0, $size - 1 ) ];
		return $str;

//get the cookie settings...
require_once( 'cookie_settings.php' );

//get the refering url and parse it...


//if the referring url is in the list...
if(in_array($source,$cookie_arr)) {
    	//if it is a valid source, set a cookie...
	//set the name of the cookie as a random string of 16 chars...	
 	setcookie( $source, $identifier, $date_of_expiry, $cookie_path, $cookie_domain );
 	//register the information in the database...
 	$write = Mage::getSingleton('core/resource')->getConnection('core_write');
 	$write->query("insert into tracking (id, source, time, sale) values ('$identifier', '$source', NOW(), '0')");


CREDIT: the random string function comes from: http://www.lateralcode.com/creating-a-random-string-with-php/

7 OK, that’s the landing page dealt with — now it’s time to add the code to the success page. Open the ‘success.phtml’ file that you copied in to your theme in stage 1, then add the following php to the top of the file and save it:


//get the cookie settings...
require_once( 'cookie_settings.php' );

//loop through the process for each domain being tracked
foreach($cookie_arr as $value) {
	//if a valid cookie is set when we get to the success page...
	if(isset($_COOKIE[$value])) {
		//get the value...
		//update the database entry...
	 	$write = Mage::getSingleton('core/resource')->getConnection('core_write');
	 	$write->query("update tracking set sale='1', sale_time=NOW(), order_id='$order_id' where id='$cookie_value'");
	 	//delete the cookie...
	 	setcookie($value, '', 1 ,$cookie_path, $cookie_domain);



Once again, the comments sum it up.


OK, so now your done — each time a visitor lands on a product page from one of the sites you have specified in the cookie_settings.php file, a cookie will be created and recorded in the database. Should that visitor then make a purchase within the time limit specified in the cookie_settings.php file, the record will be updated to contain the purchase time and order number and the cookie will be deleted!

You can download the code from this post here: Magento Ad Tracking

P.S. in this particular setup, any page using the 2columns-left.phtml template functions as a landing page. If you wish to limit the landing page to 1 specific product. Simply create a new template file, perhaps 2columns-left-landing.phtml containing the landing page code, and specify that template for the chosen product in the Magento Backend (In this case DON’T add the landing page code to the normal 2columns-left.phtml file). If you follow this route, you will need to reference the new template in the following file to make it useable in the Magento Backend:


locate the <layouts> and add the following:

<two_columns_landing module="page" translate="label">
	<label>2 columns landing page</label>

How to paginate a random list using PHP and MySQL

Displaying a list of data from your MySQL database and the breaking the result up in to pages is fairly straightforward to achieve, you simply need to two variables in PHP. One for the amount of data you wish to display on each page $amount=20; and another to control the pagination $offset=0. The MySQL query is a follows:

$query="SELECT * FROM my_table LIMIT $amount OFFSET $offset";

You can then set up your NEXT / PREVIOUS links and pass the $offset value in the URL. If you have set $amount to 20, then $offset=0 will display page 1, $offset=20 page 2 & $offset=40 page 3 etc…

Very easy — you can adjust your query to display data in any order you like, alphabetical, reversed by date etc. — but what if you want to display the data in a random order?

$query="SELECT * FROM my_table ORDER BY RAND() LIMIT $amount OFFSET $offset";

You can see the problem instantly — the first page will display $amount of data in a random order, OK, but the second page will display $amount of data in a new random order — it may contain items that were displayed on the first page!

So how can you move between pages simply, but retain your random list? The solution is to ‘seed’ a random number. It works like this:


$query="SELECT * FROM my_table ORDER BY RAND($seed) LIMIT $amount OFFSET $offset";

Once RAND() is seeded MySQL will produce a randomized list that is repeatable. I this way it is possible to repeat the query using LIMIT & OFFSET to produce pagination. In the above example $seed is a fixed number, but it would be better to set $seed to a random number and store it as a $_SESSION variable.

Here’s an example of a page that has pagination and data presented in a random order. In this example, instead of storing the $seed in a $_SESSION variable, the seed is generated using a combination of the visitor’s IP address and the current date and hour…

//generate individual seed...

…in this way, everyone sees a different random list that is reordered every hour.

Dynamic CSS – how to use PHP in your stylesheets

Q: Can you use PHP in your stylesheets?

A: yes, of course!

But why would you want to? Well, maybe you want your users to be able to control the font size or colour scheme on your site. Or maybe you need so supply and style individual IDs to dynamically generated content. Or maybe you want to change the header image depending on the time of day (like I do on this site). Whatever your reason it’s very simple to achieve.

Perhaps the most simple method would be to include the styles in question in the <head> section of your HTML and manipulate them there, something like…

<style type="text/css">

## add you PHP here...


…however, that’s a bit untidy — if you want to move ALL your CSS, static & dynamic into one stylesheet, here’s how…

1 Rename your style sheet to give it a .php extension eg: style.css → styles.php

2 This on its own will not work, you need to inform PHP that it is dealing with CSS, this is done by adding a PHP header to your stylesheet. Add the following to the top of your stylesheet.

header("Content-type: text/css");

From now on you can add any PHP you require to your CSS file.

3 One more thing. You need to change the reference to your css file to make sure it is linking to the PHP file, something like:

<link rel="stylesheet" type="text/css" media="screen" href="css/styles.php" />

4 Or, if you prefer, and your site is hosted on an Apache server, you could use mod_rewrite to rewrite the url to do this, you would need to create a file called .htaccess* in the root level of your site containing the following code (if it already exsists, copy and paste the code into it):

Options +FollowSymLinks
RewriteEngine on
RewriteBase /
RewriteRule ^css/styles.css /css/styles.php [L]

this tells all calls to styles.css to access styles.php

* more on mod_rewrite & .htaccess files coming soon…