[geeklog-cvs] geeklog: directory works

[geeklog-cvs at lists.geeklog.net]

changeset 7631:36a990997aa1
url:  http://project.geeklog.net/cgi-bin/hgwebdir.cgi/geeklog/rev/36a990997aa1
user: stan <yankees26an at gmail.com>
date: Mon Jul 13 13:45:55 2009 -0400
description:
directory works

diffstat:

 public_html/directory.php |  29 +++++++++++++++++++++++------
 1 files changed, 23 insertions(+), 6 deletions(-)

diffs (83 lines):

diff -r 3a0a7fddcaa3 -r 36a990997aa1 public_html/directory.php
--- a/public_html/directory.php	Sun Jul 12 18:14:00 2009 -0400
+++ b/public_html/directory.php	Mon Jul 13 13:45:55 2009 -0400
@@ -165,7 +165,7 @@
 */
 function DIR_navBar ($topic, $year, $month = 0)
 {
-    global $_CONF, $_TABLES, $LANG05, $LANG_DIR;
+    global $_CONF, $_TABLES, $LANG05, $LANG_DIR,$_DB_dbms;
 
     $retval = '';
 
@@ -189,7 +189,8 @@
         }
     }
 
-    $result = DB_query ("SELECT MIN(YEAR(date)) AS year FROM {$_TABLES['stories']}");
+    if($_DB_dbms=='pgsql'){$result = DB_query ("SELECT EXTRACT(Year from date) AS year FROM {$_TABLES['stories']}");}
+    else{$result = DB_query ("SELECT MIN(YEAR(date)) AS year FROM {$_TABLES['stories']}"); }
     $A = DB_fetchArray ($result);
     if ($prevyear < $A['year']) {
         $prevyear = 0;
@@ -266,11 +267,20 @@
     $lastday = DIR_lastDayOfMonth ($month, $year);
     $end   = sprintf ('%04d-%02d-%02d 23:59:59', $year, $month, $lastday);
 
-    $sql = "SELECT sid,title,UNIX_TIMESTAMP(date) AS day,DATE_FORMAT(date, '%e') AS mday FROM {$_TABLES['stories']} WHERE (date >= '$start') AND (date <= '$end') AND (draft_flag = 0) AND (date <= NOW())";
-    if ($topic != 'all') {
-        $sql .= " AND (tid = '$topic')";
+    $sql = array();
+    $sql['mysql'] = "SELECT sid,title,UNIX_TIMESTAMP(date) AS day,DATE_FORMAT(date, '%e') AS mday FROM {$_TABLES['stories']} WHERE (date >= '$start') AND (date <= '$end') AND (draft_flag = 0) AND (date <= NOW())";
+    $sql['mssql'] = "SELECT sid,title,UNIX_TIMESTAMP(date) AS day,DATE_FORMAT(date, '%e') AS mday FROM {$_TABLES['stories']} WHERE (date >= '$start') AND (date <= '$end') AND (draft_flag = 0) AND (date <= NOW())";
+    $sql['pgsql'] = "SELECT sid,title,UNIX_TIMESTAMP(date) AS day,EXTRACT(day from date) AS mday FROM {$_TABLES['stories']} WHERE (date >= '$start') AND (date <= '$end') AND (draft_flag = 0) AND (date <= NOW())";
+        if ($topic != 'all') {
+        $sql['mysql'] .= " AND (tid = '$topic')";
+        $sql['mssql'] .= " AND (tid = '$topic')";
+        $sql['pgsql'] .= " AND (tid = '$topic')";
     }
-    $sql .= COM_getTopicSql ('AND') . COM_getPermSql ('AND')
+    $sql['mysql'] .= COM_getTopicSql ('AND') . COM_getPermSql ('AND')
+         . COM_getLangSQL ('sid', 'AND') . " ORDER BY date ASC";
+    $sql['mssql'] .= COM_getTopicSql ('AND') . COM_getPermSql ('AND')
+         . COM_getLangSQL ('sid', 'AND') . " ORDER BY date ASC";    
+    $sql['pgsql'] .= COM_getTopicSql ('AND') . COM_getPermSql ('AND')
          . COM_getLangSQL ('sid', 'AND') . " ORDER BY date ASC";
 
     $result = DB_query ($sql);
@@ -345,18 +355,23 @@
     $monthsql = array();
     $monthsql['mysql'] = "SELECT DISTINCT MONTH(date) AS month,COUNT(*) AS count FROM {$_TABLES['stories']} WHERE (date >= '$start') AND (date <= '$end') AND (draft_flag = 0) AND (date <= NOW())";
     $monthsql['mssql'] = "SELECT MONTH(date) AS month,COUNT(sid) AS count FROM {$_TABLES['stories']} WHERE (date >= '$start') AND (date <= '$end') AND (draft_flag = 0) AND (date <= NOW())";
+    $monthsql['pgsql'] = "SELECT EXTRACT(Month from date) AS month,COUNT(sid) AS count FROM {$_TABLES['stories']} WHERE (date >= '$start') AND (date <= '$end') AND (draft_flag = 0) AND (date <= NOW())";
     if ($topic != 'all') {
         $monthsql['mysql'] .= " AND (tid = '$topic')";
         $monthsql['mssql'] .= " AND (tid = '$topic')";
+        $monthsql['pgsql'] .= " AND (tid = '$topic')";
     }
     $monthsql['mysql'] .= COM_getTopicSql ('AND') . COM_getPermSql ('AND')
               . COM_getLangSQL ('sid', 'AND');
     $monthsql['mssql'] .= COM_getTopicSql ('AND') . COM_getPermSql ('AND')
               . COM_getLangSQL ('sid', 'AND');
+    $monthsql['pgsql'] .= COM_getTopicSql ('AND') . COM_getPermSql ('AND')
+              . COM_getLangSQL ('sid', 'AND');
 
     $msql = array();
     $msql['mysql'] = $monthsql['mysql'] . " GROUP BY MONTH(date) ORDER BY date ASC";
     $msql['mssql'] = $monthsql['mssql'] . " GROUP BY MONTH(date) ORDER BY month(date) ASC";
+    $msql['pgsql'] = $monthsql['pgsql'] . " GROUP BY month,date ORDER BY DATE ASC";
 
     $mresult = DB_query ($msql);
     $nummonths = DB_numRows ($mresult);
@@ -433,9 +448,11 @@
     $yearsql = array();
     $yearsql['mysql'] = "SELECT DISTINCT YEAR(date) AS year,date FROM {$_TABLES['stories']} WHERE (draft_flag = 0) AND (date <= NOW())" . COM_getTopicSql ('AND') . COM_getPermSql ('AND')  . COM_getLangSQL ('sid', 'AND');
     $yearsql['mssql'] = "SELECT YEAR(date) AS year FROM {$_TABLES['stories']} WHERE (draft_flag = 0) AND (date <= NOW())" . COM_getTopicSql ('AND') . COM_getPermSql ('AND')  . COM_getLangSQL ('sid', 'AND');
+    $yearsql['pgsql'] = "SELECT EXTRACT( YEAR from date) AS year FROM {$_TABLES['stories']} WHERE (draft_flag = 0) AND (date <= NOW())" . COM_getTopicSql ('AND') . COM_getPermSql ('AND')  . COM_getLangSQL ('sid', 'AND');
     $ysql = array();
     $ysql['mysql'] = $yearsql['mysql'] . " GROUP BY YEAR(date) ORDER BY date DESC";
     $ysql['mssql'] = $yearsql['mssql'] . " GROUP BY YEAR(date) ORDER BY YEAR(date) DESC";
+    $ysql['pgsql'] = $yearsql['pgsql'] . " GROUP BY year,date ORDER BY year DESC";
 
     $yresult = DB_query ($ysql);
     $numyears = DB_numRows ($yresult);