By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,703 Members | 1,297 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,703 IT Pros & Developers. It's quick & easy.

Group results by year then parse using respective year as header for each group

P: 2
I am keenly aware that my coding skills are extremely noob but please indulge me a second.. Take a look at these queries..

Expand|Select|Wrap|Line Numbers
  1.     $sql = "SELECT DISTINCT year FROM _current_floats_config WHERE active = 'yes' ORDER BY year DESC LIMIT 2, 1";
  2.     $result = mysql_query($sql);
  3.         while($fetched = mysql_fetch_array($result)) {
  4.     $ceiling = $fetched["year"];
  5.     }
  6.     $sql = "SELECT * FROM _current_floats WHERE active = 'yes' AND yearID <= $ceiling ORDER BY yearID DESC, name ASC";
As you can see in the first $sql query I'm ordering the query DESC and skipping the first two distinct results. This is because my client wants to features the first two years in the site's navigation. So we pick up the query at result 2 and assign the var $ceiling to the result.

I'm then creating another $sql query selecting all results in DESC order less than or equal to $ceiling.

What I need to do now is group these results by years, like this below:

2006

result, result, result, result, result, result, result, result, result

2005

result, result, result, result, result, result, result, result, result

2004

result, result, result

2003

result, result, result, result, result, result

etc, etc..

Here's the snippet that returns all the results without grouping them by year..

Expand|Select|Wrap|Line Numbers
  1.     $display = 3;
  2.     $cols = 0;
  3.     $sql = "SELECT DISTINCT year FROM _current_floats_config WHERE active = 'yes' ORDER BY year DESC LIMIT 2, 1";
  4.     $result = mysql_query($sql);
  5.         while($fetched = mysql_fetch_array($result)) {
  6.     $ceiling = $fetched["year"];
  7.     }
  8.     $sql = "SELECT * FROM _current_floats WHERE active = 'yes' AND yearID <= $ceiling ORDER BY yearID DESC, name ASC";
  9.     echo $sql."<br /><br />";;
  10.     $result = mysql_query($sql); 
  11.     if (mysql_num_rows($result) > 0 ) {
  12.  
  13. /*        while($fetched = mysql_fetch_array($result)) {
  14.         echo $fetched["yearID"]."<br />";
  15.         } // end WHILE statement
  16.     } else { echo "Ooops!"; } // end IF statement
  17.  
  18. */
  19.     echo "<table width='100%' border='0' cellspacing='10' cellpadding='0' style='padding:10px;'>";
  20.  
  21. //$num = 1;
  22. while($fetched = mysql_fetch_array($result)){
  23. $num = $fetched["ID"];
  24.     if($cols == 0){
  25.         echo "<tr>\n";
  26.     }
  27.     // put what you would like to display within each cell here
  28.  
  29. if (((!empty($fetched["rendering"])) AND (!empty($fetched["rendering2"]))) OR ((!empty($fetched["rendering"])) AND (!empty($fetched["parade_pic"]))) OR ((!empty($fetched["parade_pic"])) AND (!empty($fetched["parade_pic2"])))) {
  30.  
  31.     echo "<td valign=\"top\" align=\"center\" width=\"33%\"><h1 style=\"font-size:96%;\"><a title=\"$fetched[name]\" href=\"current.php?year=$fetched[yearID]&id=$fetched[ID]\">$fetched[name]</a></h1><br /><script type=\"text/javascript\">new fadeshow(fadeimages".$num.", 150, 150, 0, 5000, 0)</script></td>\n";
  32.     $cols++;
  33.     } elseif (!empty($fetched["rendering"])) {
  34.     echo "<td valign=\"top\" align=\"center\" width=\"33%\"><h1 style=\"font-size:96%;\"><a title=\"$fetched[name]\" href=\"current.php?year=$fetched[yearID]&id=$fetched[ID]&viewing=rendering\">$fetched[name]</a></h1><img class=\"reflect ropacity25 rheight15\" alt=\"$fetched[name]\" src=\"dynamic/images/show_image.php?filename=$fetched[rendering]&width=150&height=150\"></td>\n";
  35.     $cols++;
  36.     } elseif (!empty($fetched["parade_pic"])) {
  37.     echo "<td valign=\"top\" align=\"center\" width=\"33%\"><h1 style=\"font-size:96%;\"><a title=\"$fetched[name]\" href=\"current.php?year=$fetched[yearID]&id=$fetched[ID]&viewing=parade_pic\">$fetched[name]</a></h1><img class=\"reflect ropacity25 rheight15\" alt=\"$fetched[name]\" src=\"dynamic/images/show_image.php?filename=$fetched[parade_pic]&width=150&height=150\"></td>\n";
  38.     $cols++;
  39.     } else {
  40.     echo "<td valign=\"top\" align=\"center\" width=\"33%\"><h1 style=\"font-size:96%;\"><a title=\"$fetched[name]\" href=\"current.php?year=$fetched[yearID]&id=$fetched[ID]\">$fetched[name]</a></h1><img class=\"reflect ropacity25 rheight15\" alt=\"$fetched[name]\" src=\"dynamic/images/show_image.php?filename=none.jpg&width=150&height=150\"></td>\n";
  41.     $cols++;
  42.     }
  43.     if($cols == $display){
  44.         echo "</tr>\n\n";
  45.         $cols = 0;
  46.     }
  47.  
  48. } // end IF statement
  49.  
  50. // added the following so it would display the correct html
  51. if($cols != $display && $cols != 0){
  52.     $neededtds = $display - $cols;
  53.     for($i=0;$i<$neededtds;$i++){
  54.         echo "<td>&nbsp;</td>\n";
  55.     }
  56.      echo "</tr>\n</table>\n"; 
  57.     } else {
  58.     echo "</table>\n";
  59.  
  60. } // end WHILE statement
  61.  
  62.         //
  63.         // THERE ARE NO RESULTS FOR THE GIVEN YEAR
  64.         //
  65.  
  66.  
  67. } else { echo "<p style=\"font-size:2.4em;color:#FF0C12;margin-bottom:-25px;\">Oops!</p><p>We couldn't find any floats in our database beyond the past two years.</p><p>You can check back later or select a year under the <strong>Our Floats</strong> heading on the left to continue.</p>"; }
  68.  
  69. }
Can someone steer me in the right direction? Thanks!
Oct 11 '07 #1
Share this Question
Share on Google+
1 Reply


P: 2
Anyone, Bueller.. Bueller?
Oct 14 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.