Printing Labels from Web-based Database

Printing addresses labels for a list of people or organizations from a database is a pretty common task.  Often the users doing the printing are not technical and using a variety operating systems and software tools.  Therefore, a good goal for a solution is to provide all the pieces without relying on anything pre-existing on the user's computer.  This goal rules out using Word to do a mail merge which is a pain anyway.  Another useful goal is to provide some flexibility so more tech saavy users can create new or modify existing label templates.

There are some web-based label printing solutions available.  These don't require the installation of software, but saving and sharing the label templates is complicated and getting to data into these solutions is non-trivial or impossible.

General Solution

The approach I settled on uses Avery DesignPro client software which is available for free for Windows and OSX.  DesignPro supports laying out and printing labels on any of the gazillion labels Avery sells.  DesignPro will pull data from dbase (.dbf) files or an ODBC source.

When I was trying to solve this problem, it was to print address labels for members of a club stored in a Joomla database.  So, I built a script to export the address data to a dbase file (.dbf) and download it to the user's machine.  Once the data was available, I used DesignPro to layout label template files (.zdl) by pulling the address data from the dbase file.  Then I posted the label template files along with a link to export and download the address data.

The steps for the end user are as follows:

• Download and install Avery DesignPro
• Download address data and save somewhere (here is an example)
• Download label template file and save with the address data (here is an example)
• Double-click the label template file and print.

Creating and Modifying Label Templates

The label template (.zdl) files can be modified in the Avery Design Pro software.

To create new template files for labels Avery sells, simply:

• Open Avery DesignPro and select Design from Scratch on the welcome screen.
• Select the desired Avery label style.
• Click the menu item Database > Open. Then click Open Database.
• Select the dbase file containing the address data (addresses.dbf in the example above).
• Click Insert Fields and double-click on the fields to add to the label template.
• Use the Database menu items to add additional fields and images.
• Use File > Print Preview to check your layout.

Exporting Addresses with PHP

It is very easy to create a dbase file with php.  Here is some example code that creates a dbase file using a temporary filename.

 mysql_connect($host, $user, $pass) or die(mysql_error());
mysql_select_db($db) or die(mysql_error());
  
$sql = "SELECT firstname, lastname, street, city, state, zipcode FROM addresses;";

$result = mysql_query($sql);

if ($result)
{
  $dataFile = tempnam($tmp_path, "lbl");

  // database "definition"
  $def = array(
    array("name",       "C", 40),
    array("address1",   "C", 40),
    array("address2",   "C", 40),
  );

  // creation
  $db = dbase_create($dataFile, $def);
  if (!$db)
  {
    echo "Error, can't create the database\n";
  }
  else
  {
    while($row = mysql_fetch_array( $result ))
    {
      $name = $row['firstname']." ".$row['lastname'];
      $address2 = $row['city'].", ".$row['state']."  ".$row['zip'];
      dbase_add_record($db,
        array($name,
              $row['street'],
              $address2));
    }
    dbase_close($db);
  }
}

The mysql table definition for the example given below.

CREATE TABLE `addresses1` (  
  `id` int(11) NOT NULL AUTO_INCREMENT,  
  `firstname` varchar(20),  
  `lastname` varchar(20), 
  `street` varchar(40),  
  `city` varchar(28),  
  `state` varchar(2),  
  `zip` varchar(10),  
  PRIMARY KEY (`id`)) 
ENGINE=MyISAM;

Example data and php file for download:

• addresses.sql
• dbf_addresses.php