[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 363
  • Last Modified:

How to display SQL results in groups

In the code below I use the sql statement:

   $q = "SELECT * FROM _uploads_log WHERE category = '$current_file_name' ORDER BY sub_category, log_date " ;

to pull out the data I want and order it by sub_category then by log_date which is exactly what I want, but I can't get it to display the way I want.

Right now it displays like this:

sub_category_1  -  log_date 1
sub_category_1  -  log_date 2
sub_category_1  -  log_date 3
sub_category_2  -  log_date 1
sub_category_2  -  log_date 2

That is okay, but I really want it to look like this:

sub_category_1
  -  log_date 1
  -  log_date 2
  -  log_date 3
sub_category_2
  -  log_date 1
  -  log_date 2

I have been playing around with "foreach" statements for a while trying to get it to work, but I can't get it.  Anyone know how to do this?

// Show the files that fit this page
   $q = "SELECT * FROM _uploads_log WHERE category = '$current_file_name' ORDER BY sub_category, log_date " ;
   $result = mysql_query($q) or die('Query failed: ' . mysql_error() . "<br />\n$sql"); 
   /* Error occurred, return given name by default */
   $num_rows = mysql_num_rows($result);
   if(!$result || ($num_rows < 0)){
      echo "Error displaying info!";
      return;
   }
   if($num_rows == 0){
      echo "No files have been added.";
   }


/* Display table contents */
   echo "<table align=\"left\" border=\"1\" cellspacing=\"0\" cellpadding=\"1\" width=100%>\n";
	//echo "<tr><td><b>".$sub_category."</b></td><td></td><td></td></tr>";
	echo "<tr><td><b>CDE</b></td><td><b>File Description</b></td><td><b>File Size</b></td>";

// Check user level
    if(($session->userlevel) >= '7'){
echo "<td><b>Delete</b></td>";
 	}
echo "</tr>";
    for($i=0; $i<$num_rows; $i++){
      $file_description  = mysql_result($result,$i,"file_description");
      $log_date  = mysql_result($result,$i,"log_date");
      $log_size  = mysql_result($result,$i,"log_size");
      $log_size  = number_format($log_size/1024);
      $log_filename  = mysql_result($result,$i,"log_filename");
      $file_id  = mysql_result($result,$i,"log_id");
      $sub_category  = mysql_result($result,$i,"sub_category");

      echo "<tr><td>".$sub_category."<td><a href='http://www.jpeake.com/bytheowl/uploaded_files/".$log_filename."'>".$file_description."</a></td><td>".$log_size." MB</td><td>";

// Check user level
    if(($session->userlevel) >= '7'){
echo "<a href='http://www.jpeake.com/bytheowl/".$current_file_name.".php?action=delete&file=".$file_id."'><img src='img/x.png'></a></td></tr>";

 	}
echo "</tr>";
      
   }
   echo "</table><br>\n";

Open in new window

0
jbpeake
Asked:
jbpeake
  • 2
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
jbpeake,

Let me try this again.  Submitted this earlier, but seemed to have been having issues with it posting.

One approach that I usually use is a variable to store the last updated sub category to compare against.

At line 25, I would add a variable like this:
$prev_sub_category = '';

Open in new window


for($i=0; $i<$num_rows; $i++){
      $file_description  = mysql_result($result,$i,"file_description");
      $log_date  = mysql_result($result,$i,"log_date");
      $log_size  = mysql_result($result,$i,"log_size");
      $log_size  = number_format($log_size/1024);
      $log_filename  = mysql_result($result,$i,"log_filename");
      $file_id  = mysql_result($result,$i,"log_id");
      $sub_category  = mysql_result($result,$i,"sub_category");

      echo "<tr><td>".($sub_category!=$prev_sub_category)?$sub_category:""."</td><td><a href='http://www.jpeake.com/bytheowl/uploaded_files/".$log_filename."'>".$file_description."</a></td><td>".$log_size." MB</td><td>";

// Check user level
    if(($session->userlevel) >= '7'){
echo "<a href='http://www.jpeake.com/bytheowl/".$current_file_name.".php?action=delete&file=".$file_id."'><img src='img/x.png'></a>";

 	}
echo "</td></tr>"; 
      
      // update previous sub category before iteration
      $prev_sub_category = $sub_category;
   }

Open in new window


Since your data is ordered coming from SQL, you know that once the $sub_category changes to new value that you are on to next grouping and don't have any other rows for the same sub category.

Made a few tweaks on the HTML table tags to correct what looked like type-o's.

Hope that helps!
0
 
hieloCommented:
try:
// Show the files that fit this page
   $q = "SELECT * FROM _uploads_log WHERE category = '$current_file_name' ORDER BY sub_category, log_date " ;
   $result = mysql_query($q) or die('Query failed: ' . mysql_error() . "<br />\n$sql"); 
   /* Error occurred, return given name by default */
   $num_rows = mysql_num_rows($result);
   if(!$result || ($num_rows < 0)){
      echo "Error displaying info!";
      return;
   }
   if($num_rows == 0){
      echo "No files have been added.";
   }


/* Display table contents */
   echo '<table align="left" border="1" cellspacing="0" cellpadding="1" width="100%">',PHP_EOL;
	//echo "<tr><td><b>".$sub_category."</b></td><td></td><td></td></tr>";
	echo '<thead><tr><th>CDE</th><th>File Description</th><th>File Size</th>',PHP_EOL;

// Check user level
    if(($session->userlevel) >= '7'){
		echo "<th>Delete</th>";
 	}
echo "</tr></thead><tbody>";
$prev='';
$colspan=($session->userlevel >= '7')?4:3;
    for($i=0; $i<$num_rows; ++$i){
      $file_description  = mysql_result($result,$i,"file_description");
      $log_date  = mysql_result($result,$i,"log_date");
      $log_size  = mysql_result($result,$i,"log_size");
      $log_size  = number_format($log_size/1024);
      $log_filename  = mysql_result($result,$i,"log_filename");
      $file_id  = mysql_result($result,$i,"log_id");
      $sub_category  = mysql_result($result,$i,"sub_category");
		if($prev!=$sub_category){
			echo '<tr><td colspan="',$colspan,'">',$sub_category,'</td></tr>';
		}
		echo '<tr><td>&nbsp;&#45;&nbsp;</td><td><a href="http://www.jpeake.com/bytheowl/uploaded_files/',$log_filename,'">',$file_description,'</a></td><td>',$log_size,' MB</td>';
		$prev=$sub_category;
		// Check user level
    	if(($session->userlevel) >= '7'){
			echo '<td><a href="http://www.jpeake.com/bytheowl/',$current_file_name,'.php?action=delete&amp;file=',$file_id,'"><img src="img/x.png"></a></td>';
 		}
		echo '</tr>',PHP_EOL;
   }
   echo '</tbody></table><br>',PHP_EOL;

Open in new window

0
 
jbpeakeAuthor Commented:
Awesome, worked great!!!!
0
 
hieloCommented:
Glad to help.

Regards,
Hielo
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now