• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
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();

Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  

  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.