Help with ordering by popularity (PHP/MySQL)

Discussion in 'PHP' started by arcturus1, Dec 21, 2007.

  1. #1
    I've been trying to figure this out all day, the script itself is a modified version scuttle (open source social bookmarking). I'm having an issue with sorting by popularity. It appears to work ok in most instances, but sometimes the sort order will display more popular bookmarks further down the page. See this page (http://edutagger.com/tags.php/australia?sort=pop_desc) for an example (the most popular bookmark is at the top, but the second most popular bookmark is at the bottom). Sorry I can't post URLs yet.

    I believe this is the code that's responsible, but I can't really see anything wrong with it. I can provide more info (more code, database tables, etc) if needed, but maybe there's something obvious here I'm not getting.

    Any help would be greatly appreciated.

            // Set up the SQL query.
            $query_1 = 'SELECT DISTINCT ';
            if (SQL_LAYER == 'mysql4') {
                $query_1 .= 'SQL_CALC_FOUND_ROWS ';
            }
            $query_1 .= 'B.*, U.'. $userservice->getFieldName('username');
    		
    		$query_2 = ' FROM '. $userservice->getTableName() .' AS U, '. $GLOBALS['tableprefix'] .'bookmarks AS B';
    
            $query_3 = ' WHERE B.uId = U.'. $userservice->getFieldName('primary') . $privacy;
            if (is_null($watched)) {
                if (!is_null($user)) {
                    $query_3 .= ' AND B.uId = '. $user;
                }
            } else {
                $arrWatch = $userservice->getWatchList($user);
                if (count($arrWatch) > 0) {
                    foreach($arrWatch as $row) {
                        $query_3_1 .= 'B.uId = '. intval($row) .' OR ';
                    }
                    $query_3_1 = substr($query_3_1, 0, -3);
                } else {
                    $query_3_1 = 'B.uId = -1';
                }
                $query_3 .= ' AND ('. $query_3_1 .') AND B.bStatus IN (0, 1)';
            }
    
            switch($sortOrder) {
                case 'date_desc':
                    $query_5 = ' ORDER BY B.bDatetime DESC ';
                    break;
                case 'date_asc':
                    $query_5 = ' ORDER BY B.bDatetime ASC ';
                    break;
                case 'title_desc':
                    $query_5 = ' ORDER BY B.bTitle DESC ';
                    break;
                case 'title_asc':
                    $query_5 = ' ORDER BY B.bTitle ASC ';
                    break;
                case 'url_desc':
                    $query_5 = ' ORDER BY B.bAddress DESC ';
                    break;
                case 'url_asc':
                    $query_5 = ' ORDER BY B.bAddress ASC ';
                    break;
                case 'pop_desc':
                    $query_5 = ' ORDER BY count DESC, B.bDatetime DESC ';
                    $query_1 .= ', count(B.bHash) as count ';
                    break;
                default:
                    $query_5 = ' ORDER BY B.bDatetime DESC ';
            }
    Code (markup):

     
    arcturus1, Dec 21, 2007 IP