Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Php Table Formatting with mysql results

Posted on 2012-03-19
4
Medium Priority
?
321 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 2000 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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

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…
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 i…
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 count occurrences of each item in an array.
Suggested Courses

782 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