Good Morning All,

This is a question I touched on previously, but don't think I presented what I really need to do.

I need to get this accomplished ASAP, and really have no clue how to go about it. Would even be willing to pay someone else if they have the solution and can get it implemented.

On the membership site I've been developing, they are ordering products of various types... the orders are gathered together and fulfilled periodically (daily generally).

When the order is fulfilled, I need to print labels (30 up on an 8 1/2 x 11 sheet) Avery Labels.

Currently, I am going into mysql and doing the query to extract the pertinent information, then exporting that to an excel spread sheet, then saving the excel sheet...

Then I go to the avery.com website, and use a tool that they have that loads data from an excel spreadsheet and arranges it one field at a time onto the label format, and then it does a merge of all the data to fill all the labels in the same format.

Then you save / print the document, and it creates a .PDF which I print onto the label stock as well as save for future reference.

Long story short.... I would like to accomplish ALL of this through a PHP program, so the admin of the site would be able to run a report of orders on file, and have it produce the labels programically, as opposed to me having to manually manipulate the data to extract it.

Any Suggestions / thoughts / solutions / volunteers... LOL

Thanks in advance for your responses.

Douglas

Recommended Answers

All 8 Replies

For PDF output, I prefer to get it right on the screen and then use HTML2PDF to produce the file. It uses TCPDF behind the scenes but the interface is very simple. The main requirement is that the html to produce the output has to be proper (i.e. closing tags for everything) with no shortcuts.

I presume in your post you meant 3-up labels (not 30).

commented: Interesting. +13

I use TCPDF for stufflike this. The following is adaption of example from tcpdf.org. Download the library, link to it and check it out. It is not perfect, you can fine tune it.

<?php

// some test data; you will read it from the database
$TEST_LABEL_DATA = array(

    array(
        'title' => 'Mr',
        'fullName' => 'Bean',
        'address' => '10 Bean st.',
        'zipCode' => '10000',
        'city' => 'London'),

    array(
        'title' => 'Ms',
        'fullName' => 'Piggy',
        'address' => '20 Muppet st.',
        'zipCode' => '10001',
        'city' => 'New York'),

    array(
        'title' => 'Herr',
        'fullName' => 'Vladimir Putin',
        'address' => '1 Weapon st.',
        'zipCode' => '1000',
        'city' => 'Moscow'),

    array(
        'title' => 'Mr',
        'fullName' => 'Bean',
        'address' => '10 Bean st.',
        'zipCode' => '10000',
        'city' => 'London'),

    array(
        'title' => 'Ms',
        'fullName' => 'Piggy',
        'address' => '20 Muppet st.',
        'zipCode' => '10001',
        'city' => 'New York'),

    array(
        'title' => 'Herr',
        'fullName' => 'Vladimir Putin',
        'address' => '1 Weapon st.',
        'zipCode' => '1000',
        'city' => 'Moscow'),
);

// make sure the path is correct
require_once('tcpdf.php');

// set page
$page_orientation = 'P';
$page_units = 'mm';
$page_format = 'A4';

// set top and left margin
$top_margin = 8;
$left_margin = 0;

// initialize object
$pdf = new TCPDF($page_orientation, $page_units, $page_format, true, 'UTF-8');

// set margins
$pdf->SetMargins(0, 0, -1, true);

//set auto page breaks
$pdf->SetAutoPageBreak(TRUE, PDF_MARGIN_BOTTOM);

// set font
$pdf->SetFont('times', '', 10);

// add a page
$pdf->AddPage();

// set cell padding
$pdf->setCellPadding(1);

// set cell margins
$pdf->setCellMargins(0, 0, 0, 0);

// set label dimensions (see the Avery label specification)
$lbl_width = 70;
$lbl_min_height = 35;

// set the number of  rows and columns on the sheet (see the Avery label specification)
$max_rows = 8;
$max_cols = 3;

// set the position of he first label
$x = $top_margin;
$y = $left_margin;

// set the label column and row count (for line breaks and page breaks)
$current_row = 1;
$current_col = 1;
$total_labels_count = 0;

// Read label data from the database and use while loop;
// I will use the $TEST_LABEL_DATA array and foreach loop
foreach($TEST_LABEL_DATA as $label) {

    // calculate label position (you can improve this)
    $x = (($current_col - 1) * $lbl_width);
    $y = (($current_row - 1) * $lbl_min_height);

    // create a label text
    $lbl_txt  = $label['title'] . ' ' . $label['fullName'] . "\n";
    $lbl_txt .= $label['address'] . "\n";
    $lbl_txt .= $label['zipCode'] . ' ' . $label['city'];

    // add cell (a label)
    $pdf->MultiCell($lbl_width, $lbl_min_height, $lbl_txt, 1, 'L', 0, 0, $x, $y, true);

    // increase the label count
    $current_col++;

    $total_labels_count++;

    if($current_col > $max_cols) {

        // reinitialize column count after max number of columns
        $current_col = 1;

        // increase the row count
        $current_row++;
    }
}

//Close and output PDF document
// put D instead of I to download it
$pdf->Output('labels.pdf', 'I');
?>

Thank you all for your feedback.

I should be able to resolve the issue from these options, so I'm going to mark this resolved...

I appreciate the depth of explanation that I get from this forum...

Douglas

Wouldn't you know.... Just when I think I found the answers that I needed, they decided that isn't what they really wanted...

Gotta love the clients, or you would probably kill them.

Now they decided rather than printing out labels on avery forms, that they just want to be able to do a mail merge into word and print the addresses on the envelopes rather than dealing with the labels.

I'm thinking that just giving them the ability to query the database and create an excel file that could be merged using mailmerge would do the trick, but need to be able to produce the excel file in the format necessary to be read by word.

Any suggestions would be welcome to expedite the completion of this PITA little project.

Thanks in advance
Douglas

Member Avatar for diafol

Have a look at Zend LiveDocx, seems to answer your needs:

http://framework.zend.com/manual/en/zend.service.livedocx.html

BTW, there's also the commercial Livedocx (no relation to Zend? I'm not sure - seems to do the same stuff though). You can use the free account for set use per month. You can find it here: http://www.livedocx.com/

//EDIT
Seems it may be the same outfit. AS a link to PHP implementation took me here: http://www.phplivedocx.org/articles/brief-introduction-to-phplivedocx/ which is Zend - or maybe they couldn't be bothered to write their own??

Further reading leads me to sugegst the Soap (or NuSoap) approach if you're not using the Zend framework.

commented: solid, direct and to the point +2

One quick solution would be to produce csv file using fputcsv() function (see http://php.net/manual/en/function.fputcsv.php).

Another option is to read the data from the db table, create a html table and send appropriate headers to the browser that sees the table as an excel file:

<?php

    // connect to the db and query the data from the table usual way
    // ...

    // begin the html table
    $labels = '<table>';

    // loop through the db result and add rows
    while($row = mysqli_fetch_assoc($result)) {

        $labels .= "<tr><td>{{$row['field1']}</td>";
        $labels .= "<td>{{$row['field2']}</td>";
        $labels .= "<td>{{$row['field3']}</td>"</tr>;
    }

    // end the html table
    $labels .= '</table>';

    // send the headers (make sure no content has been sent before)
    // I found those on the web and must confess that haven't studied each one of
    // them but they work fine for me
    header("Pragma: no-cache");
    header("Pragma: public");
    header("Expires: 0");
    header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
    header("Content-type: application/x-msdownload");
    header("Content-Type: application/force-download");
    header("Content-Type: application/octet-stream");
    header("Content-Type: application/download");
    header("Content-Disposition: attachment;filename=bia_values_1_8.xls ");
    header("Content-Transfer-Encoding: binary ");

    // echo the table with label data
    // the browser should open/download it as an excel file
    echo $labels;
?>
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.