Sorting db output into separate tables

[Hooker]

Hi,

 

I've made 4 tables like so (excuse messy coding, it's all just a mock up atm):

 

CREATE TABLE IF NOT EXISTS `customer` (
  `card_no` varchar(20) NOT NULL,
  `title` varchar(5) NOT NULL,
  `firstname` varchar(42) NOT NULL,
  `lastname` varchar(42) NOT NULL,
  `address` varchar(100) NOT NULL,
  `postcode` varchar(10) NOT NULL,
  `LAT` varchar(20) NOT NULL,
  `LANG` varchar(20) NOT NULL,
  `phone` varchar(15) NOT NULL,
  `email` varchar(50) NOT NULL,
  PRIMARY KEY (`card_no`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 

CREATE TABLE IF NOT EXISTS `deliveries` (
  `card_no` varchar(64) NOT NULL,
  `window_UID` int(14) NOT NULL,
  `Bags` int(2) NOT NULL,
  `Checked` tinyint(1) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 

CREATE TABLE IF NOT EXISTS `drivers` (
  `uid` int(4) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

 

CREATE TABLE IF NOT EXISTS `windows` (
  `UID` int(10) NOT NULL AUTO_INCREMENT,
  `driver` int(4) NOT NULL,
  `area` int(1) NOT NULL,
  `window_start` varchar(14) NOT NULL,
  `window_end` varchar(14) NOT NULL,
  `date` varchar(14) NOT NULL,
  PRIMARY KEY (`UID`),
  UNIQUE KEY `driver` (`driver`,`window_start`,`window_end`,`date`),
  KEY `Date` (`date`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;

 

So we can keep track of deliveries a little easier (this is a pet project, at the moment everythings done on paper and it's annoying me), i'm pulling the data out of the database like so:

 

<?php
$host="xxx";
$username="xxx";
$password="xxx";
$db_name="xxx"; 

		$today = date("d.m.y");

		mysql_connect("$host", "$username", "$password")or die("cannot connect to db"); 
		mysql_select_db("$db_name")or die("cannot select DB");

		$sql=("SELECT  deliveries.bags, deliveries.checked, windows.area, windows.window_start, windows.window_end, windows.date, customer.title, customer.firstname, customer.lastname, customer.address, customer.postcode, customer.phone, drivers.name	
		FROM deliveries
		LEFT JOIN customer 
		ON deliveries.card_no = customer.card_no
		LEFT JOIN windows 
		ON deliveries.window_UID = windows.UID 
		LEFT JOIN drivers 
		ON windows.driver = drivers.uid
		WHERE windows.date = '$today'
		ORDER BY windows.UID, deliveries.card_no, deliveries.bags");
		$result=mysql_query($sql);

		$count=mysql_num_rows($result);

		if($count>=1){
			echo"<table width=\"40%\" border=\"0\" cellspacing=\"4\">";
			while($row = mysql_fetch_array($result)){
				echo "<tr>    <td colspan=\"5\"><b>Driver: " . $row['name'] . " * Area: " . $row['area'] . " * Window: " . $row['window_start'] . " - " . $row['window_end'] . "</b></td>  </tr>";

				echo("<tr>
    					<td>1. " . $row['title'] . " " . $row['firstname'] . " " . $row['lastname'] . "</td>
    					<td>" . $row['address'] . ", " . $row['postcode'] . "</td>
    					<td>" . $row['phone'] . "</td>
    					<td>Bags: " . $row['bags'] . "</td>
					<td>Checked: ");
					echo $row['checked']? 'Yes' : 'No';
    				echo("</td>
					 </tr>");

  				}
			echo"</table>";								
		} else {
			echo"<br>No windows found.";	
			echo date("d.m.y");
		}


?>

 

The plan is to get an overview of the days deliveries through the day as deliveries are added and have them in separate tables but at the moment the best i can do is list them with repeating headers, can anyone help me out with a simple way to list them like so:

 

Delivery Window 1

delivery 1

delivery 2

delivery 3

etc

 

Delivery Window 2

delivery 1

delivery 2

delivery 3

 

Delivery Window 3

delivery 1

delivery 2

delivery 3

 

Any help is more than appreciated, like i said for now it's a proof of concept mock up and everything is up for being changed/improved once i have something functional

[PFMaBiSmAd]

<?php
$last_heading = NULL; // initialize to a value that will never exist as data
while($row = mysql_fetch_array($result)){
$new_heading = $row['.....']; // get the data value that changes to trigger closing the previous section and start a new one
// detect if there is a change in the heading
if($last_heading != $new_heading){

	// detect if not the first section
	if($last_heading != NULL){
		// this is not the first section, code to close out the previous section goes here...
		echo "close out the previous section here...";
	}

	// code to start a new section goes here...
	echo "start a new section here...";

	$last_heading = $new_heading; // remember the new heading value
}

// code to output the data under the section goes here...
echo "data under each section..."
}

// detect if there were any sections at all and close out the last one
if($last_heading != NULL){
// code to close out the last section goes here...
echo "close out last section here...";
}

 

[Hooker]

Thanks so much!