Solved

Php Table Formatting with mysql results

Posted on 2012-03-19
4
304 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:t3chguy
  • 3
4 Comments
 
LVL 9

Expert Comment

by:Shaun McNicholas
ID: 37739219
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...
0
 
LVL 9

Accepted Solution

by:
Shaun McNicholas earned 500 total points
ID: 37739252
Sorry I had that all wrong... here is the appropriate formatting

<?php
$year=2012;
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>';

?>

Open in new window

0
 
LVL 22

Expert Comment

by:Kim Walker
ID: 37739315
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

0
 
LVL 9

Expert Comment

by:Shaun McNicholas
ID: 37739413
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

0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to dynamically set the form action using jQuery.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question