Welcome Guest, Not a member yet? Register   Sign In
Help to create best SQL query with query builder

Good morning all,
I use query builder to generate my queries and I find it really great. However I am stuck.

I am currently working on a chat and I would like to return the photo of the user who is in my profile table. However depending on whether the user is the sender or the one receiving the message, the request is not the same.

I wanted to know if it was possible to do an IF with the query builder or if there was another solution to solve my problem.

Actually my function works and return the recipient picture with $this->db->join('profile', 'profile.profile_id = message.message_id_recipient'); but if I have a contact send me a message and I don't reply yet I don't have the sender picture in this query. I can fire other query on each people of course but I would like to know if better way exist.

Thanks for your help.


    //Function to get all messages
    public function get_page_messages($userID, $page, $nbsResultPerPage, $search)
        $this->db->select('message.message_id, message.message_id_sender, message.message_id_recipient, message.message_message, message.message_create_time, message.message_read_time, profile.profile_first_name, profile.profile_last_name, profile.profile_email, profile.profile_picture');
        $where = "(`message.message_id_sender` = '$userID' OR `message.message_id_recipient` = '$userID')";
        $this->db->join('profile', 'profile.profile_id = message.message_id_recipient');

        $this->db->order_by("message_create_time", "DESC");

        $query = $this->db->get();

        return $query->result();

Theme © iAndrew 2016 - Forum software by © MyBB