Looking for proper way to display data

whughesiii · Aug 15, 2011

I have a web page I am developing for personal use maintaining backup logs. I have a mysql table called volumes which has fields (index_num, volume, last_bu) and another table called backup_log with fields (index_num, volume (which is a reference to the index_num of volumes), snapshot_num and bu_date). Part of my function is working where it displays an HTML table showing dates from a date range pulled from backup_log and displayed as seperate columns. For example:

Volume | 2011-08-08 | 2011-08-09 | 2011-08-10 etc...

What I want to do, is for each volume, display the snapshot_num in the column represented by that date. For example:

Volume | 2011-08-08 | 2011-08-09 | 2011-08-10 etc...
Server1| 800 | 801 | 802 etc..

Here is what I have in my code right now:

Code:

function san_bkup()
 {
  $q = "SELECT bu_date FROM backup_log WHERE bu_date BETWEEN '2011-08-08' AND '2011-08-14' GROUP BY bu_date"; 
  $r = mysql_query($q);
  $i = 0;

?>
  <html><title>Backup Log</title></head>
  <p align="center"><font size="4" color="blue">Backup Log</font></p>
  <table align="center" border="2" width="800">
   <tr>
    <td align="center">Volume</td>
     <?php 
       while($row = mysql_fetch_array($r)){ 
           echo '<td align="center">' . $row[$i] . '</td>';
        }
      ?>
   </tr>
<?php
   $q1 = "SELECT * FROM backup_log as s, volumes as v WHERE bu_date BETWEEN '2011-08-08' AND '2011-08-14' AND s.volume = v.volume";
   $r2 = mysql_query($q1);
   $i=1;
   while($row2 = mysql_fetch_array($r2)){
    echo '<tr><td>' . $row2['volume'] . '</td>';
    foreach($row2 as $row){
     $i=2;
     echo '<td>' . $row2[$i] . '</td>';
     $i++;
    }
   }
 ?>
   </table>
  </html>
<?php
 }
?>

This is not really urgent, but any help is greatly appreciated. I am thinking that my logic relating to the foreach is wrong but I am not sure. It has been a long time since I have done anything like this.

jpadie · Aug 15, 2011

not sure I have correctly modelled the tables (have not tested with data) but might something like this work?

Code:

<?php
$start = '2011-08-08';
$stop = '2011-08-14';
san_bkup($start,$stop);
function san_bkup($start, $stop){
  $sql = <<<SQL

	SELECT 
		v.volume, s.snapshot_num, v.last_bu
	FROM 
		volumes v
	JOIN
		backup_log s
	ON	cast(v.last_bu as date) = cast(s.bu_date as date)
	AND	v.index_num = s.volume
	WHERE 
		case (v.last_bu as date) between cast('$start' as date)  AND cast('$stop' as date)
	GROUP BY 
		v.volume
	ORDER BY v.volume ASC, CAST (v.last_bu as date) ASC
SQL;

	$result = mysql_query($sql) or die(mysql_error());
	
	while($row = mysql_fetch_object($result)):
		$rows[$row->volume][$row->last_bu] = s.snapshot_num;
		$headings[] = $row->last_bu;
	endwhile;
	
	echo '<table><thead><tr><th>Volume</th>';
	$headings = array_unique($headings, SORT_STRING);
	sort($headings, SORT_STRING);
	echo '<th>' . implode('</th><th>', $headings) . '</th></tr></thead><tbody>';
	foreach($rows as $volume=>$data):
		echo '<tr><td>'.$volume . '<td>';
		foreach($headings as $h):
			echo isset($data[$h]) ? '<td>'.$data[$h] . '</td>' : "<td>&nbsp;</td>";
		endforeach;
		echo '</tr>';
	endforeach;
	echo '</tbody></table>';
}
?>

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Looking for proper way to display data

whughesiii

MIS

jpadie

Technical User

Similar threads

Part and Inventory Search

Sponsor