Solved

Php Table Formatting with mysql results

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

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Php Array Diff 3 27
How to remove decimals from calculation amount 8 24
lastpass auto fill login form 5 24
Creating a slider 12 34
I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
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…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now