[art-web] [backgrounds] add a resolution size filter to backgorund listings

[Thomas Wood <thos src gnome org>]

commit 7adf88ff4ed7cddb2b411abf6c5a5a18debb89fd
Author: Thomas Wood <thos gnome org>
Date:   Sat Nov 28 11:32:22 2009 +0000

    [backgrounds] add a resolution size filter to backgorund listings

 controllers/backgrounds.php |   47 ++++++++++++++++++++++++++++++++++--------
 models/art.php              |   38 ++++++++++++++++++++++++++++++++++
 models/backgrounds.php      |   37 +++++++++++++++++++++++++++++++++
 views/backgrounds.php       |   20 ++++++++++++++++-
 4 files changed, 131 insertions(+), 11 deletions(-)
---
diff --git a/controllers/backgrounds.php b/controllers/backgrounds.php
index 89fcfb0..2c5d8da 100644
--- a/controllers/backgrounds.php
+++ b/controllers/backgrounds.php
@@ -1,5 +1,5 @@
 <?php
-
+$t_start = microtime (true);
 /*
  * Copyright (C) 2008, 2009 Thomas Wood <thos gnome org>
  *
@@ -49,14 +49,21 @@ $limit = GET ('limit');
 if (!is_numeric ($limit))
   $limit = 12;
 
-$set_sort = GET ('sort');
-if ($set_sort)
+function GET_COOKIE ($name, $default)
 {
-  setcookie ('sort', $set_sort);
-  $sort = $set_sort;
+  $set = GET ($name);
+  if ($set)
+  {
+    setcookie ($name, $set);
+    $value = $set;
+  }
+  else
+    $value = (array_key_exists ($name, $_COOKIE)) ? $_COOKIE[$name] : $default;
+
+  return $value;
 }
-else
-  $sort = (array_key_exists ('sort', $_COOKIE)) ? $_COOKIE['sort'] : 'name';
+
+$sort = GET_COOKIE ('sort', 'name');
 
 if ($sort)
 {
@@ -72,6 +79,10 @@ if ($sort)
 else
   $sortby = 'name';
 
+$filter = GET_COOKIE ('filter', null);
+if ($filter == 'none')
+  $filter = null;
+
 $start = ($page - 1) * $limit;
 
 if ($category)
@@ -93,8 +104,18 @@ if ($category)
     }
     else
     {
-      $view_data = $bg->get_items ($category, $start, $limit, $sortby);
-      $total_backgrounds = $bg->get_total ($category);
+      if ($filter)
+      {
+        $filter_sql = 'resolution="' . mysql_real_escape_string ($filter) . '"';
+        $view_data = $bg->get_filtered_items ($category, $start, $limit,
+                                              $sortby, $filter_sql);
+        $total_backgrounds = $bg->get_filtered_total ($category, $filter_sql);
+      }
+      else
+      {
+        $view_data = $bg->get_items ($category, $start, $limit, $sortby);
+        $total_backgrounds = $bg->get_total ($category);
+      }
     }
   }
 else
@@ -109,7 +130,15 @@ if ($view_data)
   }
 }
 
+if ($category)
+  $resolution_filter = $bg->get_resolution_list ($category);
+else
+  $resolution_filter = array ();
+
 /* load view */
 require ("views/backgrounds.php");
 
+$t_load = microtime (true) - $t_start;
+print ("<!-- $t_load -->")
+
 ?>
diff --git a/models/art.php b/models/art.php
index 6fc3cc1..ecff2ef 100644
--- a/models/art.php
+++ b/models/art.php
@@ -84,6 +84,7 @@ abstract class ArtModel
 
     return $table;
   }
+
   function get_items ($category, $start, $length, $order)
   {
     /* check that start and length values are numeric */
@@ -121,6 +122,43 @@ abstract class ArtModel
     return $total[0];
   }
 
+  function get_filtered_items ($category, $start, $length, $order, $filter)
+  {
+    /* check that start and length values are numeric */
+    if (!is_numeric ($start) || !is_numeric ($length))
+      return;
+
+    $sql = sprintf ($this->get_filtered_items_sql, $category, $filter, $order,
+                    $start, $length);
+
+    if ($sql === '')
+      return;
+
+    $bg_select_result = mysql_query ($sql);
+    if (!$bg_select_result)
+      printf ("Database error: %s", mysql_error());
+
+    $table = Array ();
+    while ($row = mysql_fetch_assoc ($bg_select_result))
+    {
+      $table[] = $row;
+    }
+
+    return $table;
+  }
+
+  function get_filtered_total ($category, $filter)
+  {
+    $sql = sprintf ($this->get_filtered_total_sql, $category, $filter);
+    $r = mysql_query ($sql);
+    if (!$r)
+      printf ("Database error: %s", mysql_error());
+
+    $total = mysql_fetch_row ($r);
+
+    return $total[0];
+  }
+
 }
 
 ?>
diff --git a/models/backgrounds.php b/models/backgrounds.php
index b601c6d..9e1edd2 100644
--- a/models/backgrounds.php
+++ b/models/backgrounds.php
@@ -32,6 +32,23 @@ class BackgroundsModel extends ArtModel
             AND background.backgroundID > 1000
             ORDER BY %s LIMIT %s,%s";
 
+  var $get_filtered_items_sql = "SELECT * FROM background
+            INNER JOIN user ON background.userID = user.userID
+            RIGHT JOIN background_resolution
+            ON background_resolution.backgroundID = background.backgroundID
+            WHERE status='active' AND category = '%s'
+            AND background.backgroundID > 1000
+            AND (%s)
+            ORDER BY %s LIMIT %s,%s";
+
+  var $get_filtered_total_sql = "SELECT COUNT(name) FROM background
+            INNER JOIN user ON background.userID = user.userID
+            RIGHT JOIN background_resolution
+            ON background_resolution.backgroundID = background.backgroundID
+            WHERE status='active' AND category = '%s'
+            AND background.backgroundID > 1000
+            AND (%s)";
+
   var $get_total_sql = "SELECT COUNT(name) FROM background
             WHERE category = '%s' AND status='active'
             AND background.backgroundID > 1000";
@@ -63,6 +80,26 @@ class BackgroundsModel extends ArtModel
 
     return $res;
   }
+
+  function get_resolution_list ($category)
+  {
+    $sql = "SELECT resolution FROM background_resolution
+            INNER JOIN background
+            ON background.backgroundID = background_resolution.backgroundID
+            WHERE background.category = '$category'
+            AND background.status = 'active'
+            AND background.backgroundID > 1000
+            GROUP BY resolution
+            ORDER BY resolution";
+    $r = mysql_query ($sql);
+    $res = array ();
+    $res['none'] = 'All';
+
+    while ($rr = mysql_fetch_row ($r))
+      $res[$rr[0]] = $rr[0];
+
+    return $res;
+  }
 }
 
 ?>
diff --git a/views/backgrounds.php b/views/backgrounds.php
index 0595e9a..b86143d 100644
--- a/views/backgrounds.php
+++ b/views/backgrounds.php
@@ -26,7 +26,7 @@ $t->add_css ("/css/art.css");
 $t->print_header();
 
 /* if no data is available, print out a category list */
-if (!$view_data)
+if (!$view_data && !$category)
 {
   ?>
   <h2>Categories</h2>
@@ -80,10 +80,20 @@ function selected ($a, $b)
     <option value="name"<?php selected ($sort, 'name')?>>Name</option>
     <option value="popularity"<?php selected ($sort, 'popularity')?>>Popularity</option>
   </select>
+  </label>
+
+  <label>Size:
+  <select name="filter" onchange="this.form.submit()" style="font-size: small">
+  <?php foreach ($resolution_filter as $value => $name): ?>
+    <option value=<?php echo $value; selected ($filter, $value) ?>>
+    <?php echo $name ?></option>
+  <?php endforeach ?>
+  </select>
+  </label>
+
   <noscript>
     <input type="submit" value="Go" style="font-size: small;">
   </noscript>
-  </label>
 </form>
 
 <script type='text/javascript'>
@@ -95,6 +105,12 @@ String.prototype.rot13 = rot13 = function(s)
 }
 </script>
 
+<?php if (!$view_data): ?>
+<div align="center">
+<i>No results, try adjusting the size filter.</i>
+</div>
+<?php endif ?>
+
 <?php foreach ($view_data as $row): ?>
 <div class="list-item">
     <b><?php echo $row['name']?></b>