CODEWITHSUNDEEP
phpmysqloopsql-order-by
brewpixels
brewpixels

Reputation: 311

How do I ORDER BY last_visit and display the most recent visitor for a user?

The following public function displays visitors to a users profile. What I'm trying to do is order the results by the most recent visitor. I figure the best way to do this is add ORDER BY last_visit before the left JOIN. But this breaks the script and I get my 'no results' message. last_visits has a timestamp data type btw. 

public function getVisitors($count = 10, $page = 0){
    $result = $this->_db->query('
        SELECT
            `visited_users`.*,
            `users`.*,
            NOW() as now_t
        FROM
            `visited_users`
        ORDER BY last_visit  
        LEFT JOIN `users` ON `users`.`id` = `visited_users`.`user_id`
        WHERE
            `visited_users`.`visited_id` = ?
        LIMIT '.$count*$page.','.$count, array($this->_data->id));
    return $result->results();
}

Upvotes: 0

Views: 34

Answers (1)

cyk
cyk

Reputation: 581

Proper SQL syntax requires the ORDER BY to come after the WHERE clause. Try this.

public function getVisitors($count = 10, $page = 0){
    $result = $this->_db->query('
        SELECT
            `visited_users`.*,
            `users`.*,
            NOW() as now_t
        FROM
            `visited_users`
        LEFT JOIN `users` ON `users`.`id` = `visited_users`.`user_id`
        WHERE
            `visited_users`.`visited_id` = ?
        ORDER BY last_visit  
        LIMIT '.$count*$page.','.$count, array($this->_data->id));
    return $result->results();
}

Upvotes: 1

Related Questions