CODEWITHSUNDEEP
phpmysqlcodeigniter
Taylor
Taylor

Reputation: 3141

Codeigniter: DB order by effects two database querys

I have two queries but the only the first one, I want ordered by the time. The second one, I don't.

My code:

 $query = $this->db->get('dayone_entries');
    $this->db->order_by("time", "desc"); //ordering it

    if ($query->num_rows() > 0) {


        foreach ($query->result() as $row) {
            $data[] = $row;
        }

        $query_user = $this->db->get_where('users', array('uid' => $data->uid));

        foreach ($query_user->result() as $row_user) {
            $data[] = $row_user;
        }

        return $data;

With the code above, it orders by time on both of my queries, $query and $query_user I only want ordered on the $query. Any help?

Upvotes: 1

Views: 86

Answers (2)

Tpojka
Tpojka

Reputation: 7111

Use chaining method to exact variable:

$query = $this->db->order_by("time", "desc")->get('dayone_entries');

so it won't affect next query.

Upvotes: 0

Tim Mickey
Tim Mickey

Reputation: 361

A couple things. In Codeigniter, you put all $this->db->fn() before the $this->db->get(). CI stores all your calls and builds the query when you run $this->db->get. For example:

$this->db->order_by("time", "desc");
$rows = $this->db->get('dayone_entries')->result();

$row_users = $this->db->get_where('users', array('uid' => $data->uid))->result();

However, it looks like you really want to use a join. (I'm not 100% sure from your code, but try something like this)

$this->db->join('users', 'users.uid = dayone_entries.uid');
$this->db->order_by('time', 'desc');
$data = $this->db->get('dayone_entries')->result();

I'm guessing you are using MySQL. The above CI should generate the following query:

SELECT * FROM dayone_entries
JOIN users ON users.uid = dayone_entries.uid
ORDER BY time DESC;

You can check what query CI runs by running 

$this->db->last_query();

( right after your $this->db->get(); )

Upvotes: 1

Related Questions