Solved

Php Table Formatting with mysql results

Posted on 2012-03-19
4
305 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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

This article will explain how to display the first page of your Microsoft Word documents (e.g. .doc, .docx, etc...) as images in a web page programatically. I have scoured the web on a way to do this unsuccessfully. The goal is to produce something …
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 …
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

789 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