Printing Address Labels 2

[pastorandy]

I have a mySQL DB with customer details and I would like to be able to generate a query to print out address labels.

How would I write the query so that it prints out to the screen in a format that is two labels across and 4 rows down? A total of 8 labels on each page but further pages if the dataset is greater than 8 customers.

Any ideas?

 

[vacunita]

Generating the labels is not a Query issue.

The query would only need to pull the relevant data from the DB, and then have PHP place the data in the appropriate places by outputting the correct HTML.


You'll most likely have to play around with DIVs' and CSs to get the placement, and then just have PHP output the data from the DB onto the divs, you've already placed.







----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[dkdude]

Like vacunita advices : divs & css. Question about that can be asked here forum215

;-)

 

[pastorandy]

I have got my layout with DIVS and CSS but how would i output the query across all of the DIVS?

If I output the variable $name in one DIV how would I get the output the the next record in the next DIV?

 

[dkdude]

seoandy,

We would need to see some code to be able to help you. We need to know what you've got already so we can see where to focus.

Regards

 

[pastorandy]

Hi
OK, the following isn't the exact code and may not be the correct syntax as I am still thinking about how to do this but say I had something like the following:

<?
$query= "select * from customers";
$result = mysql_query($query) or die (mysql_error()); 
?>

#envelope1 {
    position: absolute;
    display: block;
    left: 2cm;
    top: 0cm;
    height: 4cm;
    width: 8cm;
    border: 1px solid;
}

#address1 {
    position: relative;
    float: left;
    display: block;
    left: 1cm;
    top: 0cm;
    font-family: arial;
    font-size: 18px;
}


#envelope2 {
    position: absolute;
    display: block;
    left: 10cm;
    top: 0cm;
    height: 4cm;
    width: 8cm;
    border: 1px solid;
}

#address2 {
    position: relative;
    float: left;
    display: block;
    left: 1cm;
    top: 0cm;
    font-family: arial;
    font-size: 18px;
}

<?
while($row = mysql_fetch_row($result)){ 
?>
<div id="envelope1">
 <div id="address1">
<br>
    <b>$name</b><br>
    </div>
</div>



<div id="envelope2">
 <div id="address2">
<br>
    <b>$name</b><br>
    </div>
</div>
<? } ?>

How would I move to the next returned name instead of just displaying the same variable for the same customer in the second address?

 

[dkdude]

Okay,

First, get rid of the <br>'s. They're just gonna make trouble. Instead use margin - much better, and much more conform to css ;-)

I'd wrap the label div's inside a wrapper div with the propper page size.

You may find it usefull to print to a pdf while developing. This is what I use for just that:

http://www.pdf995.com/

It's free too -apart from a pop-up when you print to a pdf file ;-)

The envelopes should be relative, not absolute. If you float them inside a wrapper div that fits the page side, they should end up in the right place if you style them right.

<style type="text/css">
.wrapper { /* adjust these dimentions to the actual */
  width : 20cm; 
  height : 30cm;
  margin : 1cm;
}

.envelope { /* adjust dimentions ... */
  height: 4cm;
  width: 8cm;
  border: 1px solid;
  margin : 1cm;
  float : right;
  padding : 1cm;
}

.address {
  font-family: arial;
  font-size: 18px;
}
</style>
.. etc
.. etc
.. etc
<div class="wrapper">
  <!-- repeat for each label -->
  <div class="envelope">
    <div class="address">
      <?php echo $name; ?>
    </div>
  </div>
  <!-- /repeat -->
</div>

Make sense?

 

[pastorandy]

I am not worried about the format at the moment - just what I mentioned earlier...

>>
How would I move to the next returned name instead of just displaying the same variable for the same customer in the second address?
<<

 

[vacunita]

Instead of creating all the envelopes and then trying to output the information over them. Create one single envelope template, and use it in the While loop.

So something like dkdude pointed out:

<?
while($row=mysql_fetch_array){
?>
<div class="wrapper">
  <!-- repeat for each label -->
  <div class="envelope">
    <div class="address">
      <?php echo $row['field1];  
            echo $row['field2];

...

?>
    </div>
  </div>
  <!-- /repeat -->
</div>

<?
}
?>

This will create the envelopes one after the other with the relevant information on each, as the while loop progresses.

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[dkdude]

I see. Misleading thread title indeed ...

Have a look at the examples at

http://dk.php.net/manual/en/function.mysql-result.php

 

[pastorandy]

Surely the above code just outputs the row once doesn't it?

What I am confused about is how I would actually output the variables across the divs or tables, when they are outside of the query row?

How do I get the query to output across several tables or divs on the page?

 

[vacunita]

My above suggestion loops throuh the rows of information and prints out the labels as the loop progresses.

while ($row=mysql_fetch_array($results)){ [green]get next row of information[/green]

which each iteration of the while loop it moves to the next row and outputs its contents along with the envelope template html, producing the next envelope.

It does this until there are no more rows.

You should really visit the PHP online manual and familiarize yourself with While loops and how mysql_fetch_array works.







----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[pastorandy]

Many thanks!

Could you just give me a brief piece of code to see roughly what it needs to look like?

 

[vacunita]

Very roughly...

$results=mysql_query("Select *From ...." ,$DBconnection");
while($row=mysql_fetch_array($results)){
?>
  <div class="envelope">
<span class="recipient">[red]<? echo $row['fieldname'];?>[/red]</span>[green]close recipient span[/green]
    <div class="address">
      [red]<?php echo $row['otherfieldname];  
            echo $row['thirdfieldname];[/red]

[green]//output whatver other fields you need in the same manner. [/green]

red]?>[/red]
    </div>[green]//close address div[/green]
  </div>[green]//close envelope div[/green] 

<?

}
?>

Usng CSS style the divs so they work as an envelope.
adding fotn stylings and background colors borders etc... through CSS. Remember you are actually creating thr html for one envelope. THe While loop will take care of outputting it for each envelope it creates.

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[pastorandy]

Many thanks - I'll give it a go and post back if I get it working.

 

[jpadie]

Andy,
is the issue you are concerned about that you have to output two columns of labels and you need to know how to get two records inside each iteration of the while loop?

if so, there are multiple solutions. the easiest is to stick with the while loop and the single record and conditionally change the id/class names from a suffix of 1 to two

$cnt = 1;
while ($row = mysql_fetch_assoc($result)){
 echo <<<EOL
   <div class="Label$cnt">
     //insert label data
   </div>

EOL;

 //flip the counter back to the other setting
 $cnt = ($cnt===1) ? 2 : 1; 
}

the simple alternative is to put another call to mysql_fetch_array in your while loop

while ($row = mysql_fetch_array($result)){
  //add code for left hand label
  if ( ($row = mysql_fetch_array) !== false){
    //add code for right hand label
  }
  /*if it is the last record then the if statement won't output any data and the next while iteration will break the loop.  to break it explicitly after the if clause you can add a
  else {
   break(0);
  } */
}

 

[andyb2005]

Jpadie, that's what I am trying to do, but have you got a complete example with all the bits and pieces together so that I can see how this works?

 

[jpadie]

you really have been given all the information you need in the posts above.

but ....

let's say you have a database (addresses) containing a table (addresses) with the following fields in it:

firstname, lastname, address, city, postcode, country

let's say you want address labels in the 7 * 2 format to use Avery A4 labels L7163

lastly - let's say that you really want to do this on the web, even though it's a bad idea and pdf or MS Word would be better...

remember to change your browser's print settings to remove the headers and footers and set the margins to zero.

<?php
mysql_connect ("hostname", "username", "password") or die(mysql_error());
mysql_select_db ("addresses") or die(mysql_error();

$sql = "
		select 
			firstname, 
			lastname, 
			address, 
			city, 
			postcode, 
			country 
		from 
			addresses 
		order by 
			lastname asc, 
			firstname asc";

//run query
$result = @mysql_query($sql) or die (mysql_error());
//derive number of results
$numRows = @mysql_num_rows($result);
//calculate number of tables we will need
$numPages = ceil($numRows/14);

//instantiate a variable to hold the output
$output="";

//create the outer loop for the pages
for($page=0; $page<$numPages; $page++):
	//create loop for number of rows per page
	$output .= "<table class=\"addresstable\">\r\n";
	for($row=0; $row<7; $row++):
		$output .= "\t<tr class=\"addressRow\">\r\n";
		//create innermost loop for columns
		for($column=0; $column<2; $column++):
			//we do the work in this loop
			$record = @mysql_fetch_assoc($result);
			if ($record === false): //end of recordset
				$name=$firstname=$lastname=$address=$city=$postcode=$country="&nbsp;";
			else:			
			//iterate the $result array to make the output look pretty
				foreach ($record as $item=>$val):
					if(!empty($val)):
						${$item} =  nl2br($val) . "<br/>";
					else:
						${$item} = NULL;
					endif;
				endforeach;
				//format the name right
				$name = (empty($record['firstname'])) ? $lastname : $record['firstname'] . "&nbsp;".$record['lastname'];
			endif;

			//choose class for the label
			$class = $column===0 ? "LH" : "RH";

			//output label data
			$output .= <<<ITEM
		<td class="addressLabel_$class" valign="middle">
			<div class="addressee">
				$name
			</div>
			<div class="address">
				$address
				$city
				$postcode
				$country
			</div>
		</td>

ITEM;
		endfor;	//end innermost loop
		$output .= "\t</tr>\r\n";
	endfor;//close rows loop
	$output .=  "</table>\r\n";
endfor; //close outer loop
?>
<head>
<style type="text/css">
html, body {margin:0px; padding: 0px; font:"Times New Roman", Times, serif; font-size:10px;}
.addresstable {page-break-after:always; border-collapse:collapse; position:relative; top:1.59cm; left:0.47cm; right:0.47cm; table-layout:fixed;}
.addressLabel_LH, .addressLabel_RH {table-layout:fixed; width:9.9cm; height:3.81cm; padding-left:1cm; overflow:hidden;}
.addressLabel_RH {margin-left:0.26cm;}
</style>
</head>
<body>
<?=$output?>
</body>

 

[andyb2005]

Jpadie

I appreciate that but I haven't attempted something like this before and will learn from seeing the entire structure.

I'll post back with how I get on!

 

[pastorandy]

Jpadie

This works like an absolute treat! Many thanks.

One small question: I haven't seen this form of notation before:

 $output .= <<<ITEM

What does the <<<ITEM mean?