Advertisement
linccce

SQL error

Jul 16th, 2014
271
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PHP 3.99 KB | None | 0 0
  1. ERROR:
  2.  
  3. 1690 - BIGINT UNSIGNED value is out of range in '(`medziokle_press`.`c`.`isgood` - `medziokle_press`.`c`.`ispoor`)' SQL=SELECT c.userid, '' as avatar, '' as profileLink , CASE WHEN c.userid = 0 THEN c.email ELSE u.email END AS email , CASE WHEN c.userid = 0 THEN c.name ELSE u.name END AS name , CASE WHEN c.userid = 0 THEN c.username ELSE u.username END AS username , COUNT(c.userid) AS commentsCount , SUM(c.isgood) AS isgood, SUM(c.ispoor) AS ispoor, SUM(c.isgood - c.ispoor) AS votes FROM v9465_jcomments AS c LEFT JOIN v9465_users AS u ON u.id = c.userid WHERE c.published = 1 AND c.deleted = 0 GROUP BY c.userid, email, name, username, avatar, profileLink ORDER BY commentsCount DESC LIMIT 0, 25
  4.  
  5. code which is making this query:
  6.  
  7. <?php
  8. // no direct access
  9. defined('_JEXEC') or die;
  10.  
  11. class modJCommentsTopPostersHelper
  12. {
  13.     static function getList( &$params )
  14.     {
  15.         $db = JFactory::getDBO();
  16.         $interval = $params->get('interval', '');
  17.  
  18.         $where = array();
  19.  
  20.         if (!empty($interval)) {
  21.             $date = JFactory::getDate();
  22.             $timestamp = $date->toUnix();
  23.  
  24.             switch($interval) {
  25.                 case '1-day':
  26.                     $timestamp = strtotime('-1 day', $timestamp);
  27.                     break;
  28.  
  29.                 case '1-week':
  30.                     $timestamp = strtotime('-1 week', $timestamp);
  31.                     break;
  32.  
  33.                 case '2-week':
  34.                     $timestamp = strtotime('-2 week', $timestamp);
  35.                     break;
  36.  
  37.                 case '1-month':
  38.                     $timestamp = strtotime('-1 month', $timestamp);
  39.                     break;
  40.  
  41.                 case '3-month':
  42.                     $timestamp = strtotime('-3 month', $timestamp);
  43.                     break;
  44.  
  45.                 case '6-month':
  46.                     $timestamp = strtotime('-6 month', $timestamp);
  47.                     break;
  48.  
  49.                 case '1-year':
  50.                     $timestamp = strtotime('-1 year', $timestamp);
  51.                     break;
  52.                 default:
  53.                     $timestamp = NULL;
  54.                     break;
  55.             }
  56.  
  57.             if ($timestamp !== NULL) {
  58.                 if (version_compare(JVERSION,'1.6.0','ge')) {
  59.                     $dateFrom = JFactory::getDate($timestamp)->toSql();
  60.                     $dateTo = $date->toSql();
  61.                 } else {
  62.                     $dateFrom = JFactory::getDate($timestamp)->toMySql();
  63.                     $dateTo = $date->toMySQL();
  64.                 }
  65.  
  66.                 $where[] = 'c.date BETWEEN ' . $db->Quote($dateFrom) . ' AND ' . $db->Quote($dateTo);
  67.             }
  68.         }
  69.  
  70.  
  71.         switch($params->get('ordering', ''))
  72.         {
  73.                 case 'votes':
  74.                     $orderBy = 'votes DESC';
  75.                     break;
  76.  
  77.             case 'comments':
  78.             default:
  79.                     $orderBy = 'commentsCount DESC';
  80.                 break;
  81.         }
  82.  
  83.         $where[] = 'c.published = 1';
  84.         $where[] = 'c.deleted = 0';
  85.  
  86.         $query = "SELECT c.userid, '' as avatar, '' as profileLink"
  87.             . " , CASE WHEN c.userid = 0 THEN c.email ELSE u.email END AS email"
  88.             . " , CASE WHEN c.userid = 0 THEN c.name ELSE u.name END AS name"
  89.             . " , CASE WHEN c.userid = 0 THEN c.username ELSE u.username END AS username"
  90.             . " , COUNT(c.userid) AS commentsCount"
  91.             . " , SUM(c.isgood) AS isgood, SUM(c.ispoor) AS ispoor, SUM(c.isgood - c.ispoor) AS votes"
  92.             . " FROM #__jcomments AS c"
  93.             . " LEFT JOIN #__users AS u ON u.id = c.userid"
  94.             . (count($where) ? ' WHERE  ' . implode(' AND ', $where) : '')
  95.             . " GROUP BY c.userid, email, name, username, avatar, profileLink"
  96.             . " ORDER BY " . $orderBy
  97.             ;
  98.  
  99.         $db->setQuery($query, 0, $params->get('count'));
  100.         $list = $db->loadObjectList();
  101.  
  102.         $show_avatar = $params->get('show_avatar', 0);
  103.  
  104.         if ($show_avatar) {
  105.             JPluginHelper::importPlugin('jcomments');
  106.  
  107.             if (version_compare(JVERSION, '3.0', 'ge')) {
  108.                 $dispatcher = JEventDispatcher::getInstance();
  109.             } else {
  110.                 $dispatcher = JDispatcher::getInstance();
  111.             }
  112.  
  113.             $dispatcher->trigger('onPrepareAvatars', array(&$list));
  114.         }
  115.  
  116.         foreach($list as &$item) {
  117.             $item->displayAuthorName = JComments::getCommentAuthorName($item);
  118.  
  119.             if ($show_avatar && empty($item->avatar)) {
  120.                 $gravatar = md5(strtolower($item->email));
  121.                 $item->avatar = '<img src="http://www.gravatar.com/avatar.php?gravatar_id='. $gravatar .'&amp;default=' . urlencode(JCommentsFactory::getLink('noavatar')) . '" alt="'.htmlspecialchars(JComments::getCommentAuthorName($item)).'" />';
  122.             }
  123.         }
  124.  
  125.         return $list;
  126.     }
  127. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement