Link to home
Create AccountLog in
Avatar of t3chguy
t3chguyFlag for United States of America

asked on

Php Table Formatting with mysql results

I'm looking for a little advice concerning a table I'm trying to build.  

For each one of our warehouses that I pull from a database, I need to create a table cell for the month.  Inside the table cell, I need to check to see if a specific document exists, and if not, give the ability to upload a document.

The trouble I'm having seems to be with the filename.

I've ran my query, created a row for each warehouse, but in the next cell, I'm not sure how to get it to change months as the row progress.

If at all possible, I'm trying to avoid having to create a new table to hold the document links and information.  I'd rather do this purely using PHP with the exception of pulling the warehouse locations.

My code is below and an image of what I'm trying to achieve is attached.

$sql = "SELECT branch, branchname FROM dw_location WHERE hidden = '0' and branch <> '90'";
$res = mysql_query($sql) or die(mysql_error());

while ($row = mysql_fetch_assoc($res))
      {
      $branch = $row['branch'];
      $branchname = $row['branchname'];
      
      $janauditfn = $branch.'-01'.$year.'-Audit.pdf';
      $febauditfn = $branch.'-02'.$year.'-Audit.pdf';
      $marauditfn = $branch.'-03'.$year.'-Audit.pdf';
      $aprauditfn = $branch.'-04'.$year.'-Audit.pdf';
      $mayauditfn = $branch.'-05'.$year.'-Audit.pdf';
      $junauditfn = $branch.'-06'.$year.'-Audit.pdf';
      $julauditfn = $branch.'-07'.$year.'-Audit.pdf';
      $augauditfn = $branch.'-08'.$year.'-Audit.pdf';
      $sepauditfn = $branch.'-09'.$year.'-Audit.pdf';
      $octauditfn = $branch.'-10'.$year.'-Audit.pdf';
      $novauditfn = $branch.'-11'.$year.'-Audit.pdf';
      $decauditfn = $branch.'-12'.$year.'-Audit.pdf';
      
      $janforkliftfn = $branch.'-01-'.$year.'forklift.pdf';
      $febforkliftfn = $branch.'-02-'.$year.'forklift.pdf';
      $marforkliftfn = $branch.'-03-'.$year.'forklift.pdf';
      $aprforkliftfn = $branch.'-04-'.$year.'forklift.pdf';
      $mayforkliftfn = $branch.'-05-'.$year.'forklift.pdf';
      $junforkliftfn = $branch.'-06-'.$year.'forklift.pdf';
      $julforkliftfn = $branch.'-07-'.$year.'forklift.pdf';
      $augforkliftfn = $branch.'-08-'.$year.'forklift.pdf';
      $sepforkliftfn = $branch.'-09-'.$year.'forklift.pdf';
      $octforkliftfn = $branch.'-10-'.$year.'forklift.pdf';
      $novforkliftfn = $branch.'-11-'.$year.'forklift.pdf';
      $decforkliftfn = $branch.'-12-'.$year.'forklift.pdf';
      
      echo '<tr>
                  <td ' . $row_color . '>' . $branchname . '</td>
                  <td ' . $row_color . '><span class="nonboldblacklinks">';  if (file_exists($janauditfn)) { echo '<a href="../../docs/audits/'.$auditfn.'" target="iframe">View Audit</span></a>'; } else { echo 'Upload an Audit'; } echo '</td>
            </tr>';
      }
      
      echo '</table>';
3-19-2012-12-12-22-PM.png
Avatar of Shaun McNicholas
Shaun McNicholas
Flag of United States of America image

Here's a loop for the months

echo '<tr>';
echo '<td>Warehouse</td>';
for ( $i=1; $i<12; $i++) {
	$currentMonth = date_create('$i/1/$year');
	$currentMonthName = date_format($currentMonth,'B');
	echo '<td>$currentMonthName</td>';
}
echo '</tr>';

Open in new window


You just need to do the same loop for each warehouse and then use some logic to check to see if the documents exist for each warehouse for each month. Then fill in each cell appropriately...
ASKER CERTIFIED SOLUTION
Avatar of Shaun McNicholas
Shaun McNicholas
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Try this:
echo '<tr>
			  <td ' . $row_color . '>' . $branchname . '</td>
';
$months = array('jan','feb','mar','apr','may','jun','jul','aug','sep','oct','nov','dec');
foreach ($months as $month) {
	$varname = $month.'auditfn';
	echo '                  <td ' . $row_color . '><span class="nonboldblacklinks">';
	if (file_exists($$varname)) {
		echo '<a href="../../docs/audits/'.$auditfn.'" target="iframe">View Audit</span></a>';
	} else {
		echo 'Upload an Audit';
	}
}
echo '</td>
		</tr>';

Open in new window


You can use the double dollar sign to call variable variable names. Or if your variable is a global variable, you can access it through the $_GLOBALS array.
$_GLOBALS[$month.'auditfn']

Open in new window

I believe this will get you most of the way there. I added the naming formatting for the loop to check for file names etc...
<?php
$year=2012;
echo '<table>';
echo "<tr>";
echo "<td>Warehouse</td>";
for ( $i=1; $i<12; $i++) {
	$MonthTxt = "$i/1/$year 00:00:00";
	$currentMonth = strtotime($MonthTxt);
	$currentMonthName = strftime("%B",$currentMonth);
	echo "<td>$currentMonthName</td>";
}
echo '</tr>';


$sql = "SELECT branch, branchname FROM dw_location WHERE hidden = '0' and branch <> '90'";
$res = mysql_query($sql) or die(mysql_error());

while ($row = mysql_fetch_assoc($res))
      {
      $branch = $row['branch'];
      $branchname = $row['branchname'];

      echo '<tr>
                  <td ' . $row_color . '>' . $branchname . '</td>';
      
for ( $i=1; $i<12; $i++) {
	if ($i<10) { $monthNum = "0$i"; } else { $monthNum = $i; }
		$auditFileName = $branch."-".$monthNum.$year."-Audit.pdf";
		$forkliftFileName = $branch."-".$monthNum.$year."-forklift.pdf";
      echo '<td ' . $row_color . '><span class="nonboldblacklinks">';  if (file_exists($auditFileName)) { echo '<a href="../../docs/audits/'.$auditFileName.'" target="iframe">View Audit</span></a>'; } else { echo 'Upload an Audit'; } echo '</td>';
}

echo '</tr>';
}

echo '</table>';
?>

Open in new window