Simple database code not working ( Problem ongoing )

[eluser]markanderson993[/eluser]
Hello, I am trying to extract information out of a database but I am not getting any results back. I believe there is something wrong with my code, if anybody can figure out what is wrong I would greatly appreciate the help!

The point of the code is to match the user's id to the pm_recipient id in a table then for each match it finds, run it through a foreach function to get the row's information and insert it into an array to be returned.

Code:

function get_messages($id)
    {
        $this->db->where('pm_recipient',$id);
        $get_pm_recipient_rows = $this->db->get('pm_recipient');
        if ($get_pm_recipient_rows->num_rows() != 0)
        {
        
            $pm_rows = array();
            
            foreach ($get_pm_recipient_rows->result() as $row)
            {
                $this->db->where('pm_id',$row->pm_id);
                $this->db->limit(1);
                $get_pm_thread_row = $this->db->get('pm_thread');
                $pm_thread_row = $get_pm_thread_row->row();
                
                $pm_rows[$row->pm_id] = $get_pm_thread_row->row();
            }
            
            return $pm_rows;
            
        } else {
            return 0;
        }
    }

[eluser]mdowns[/eluser]
Try this:

Code:

$get_pm_recipient_rows = $this->db->get_where('pm_recipient',array('pm_im'=>$id));
return $get_pm_recipient_rows->result_array();

[eluser]mdowns[/eluser]
Oops...totally misunderstood.

If I were you I would just write the query out instead of doing loops

EDIT: If you want to keep the loops, try using get_where.

[eluser]markanderson993[/eluser]
Ahh so simple! Only three lines of code Wow thanks so much for pointing me in the right direction

[eluser]markanderson993[/eluser]
Well, i'm having considerable trouble now that I'm putting my new code to the test.

In order to be more clear on what the problem I will first go into a little detail explaining what is actually going on.

First there are 2 tables, the first named pm_recipient (consists of 2 fields: pm_id, pm_recipient) and the other named pm_thread (indexed by pm_id). Ideally what should happen is when a pm_recipient (the same thing as the user's id) exists, the database searches the pm_recipient table for all the matches between their user_id and the pm_recipient number in the pm_recipient table. For each match, the corrisponding row in the pm_thread table is extracted and added to an array. So the end product is an array containing rows of information from the pm_thread table.

The above code attempted to do that but it did not achieve the desired result.

Does anybody know if what I am attempting to do is possible?

Any help would be greatly appreciated! Thank you!

- mark

[eluser]mdowns[/eluser]
Could you not use the following query? :

SELECT T.* FROM pm_thread AS T
JOIN pm_recipient AS R
WHERE T.pm_id = R.pm_id AND R.pm_recipient = @userid

[eluser]markanderson993[/eluser]
Thanks for your input mdowns, could you please explain your logic, i'm having a hard time following.

Thanks!

[eluser]mdowns[/eluser]
If you join the pm_thread and pm_recipient table on pm_id, you get all the threads for each recipient:

SELECT T.* FROM pm_thread AS T JOIN pm_recipient AS R WHERE T.pm_id = R.pm_id

Then you filter out the recipients based on your given userid:

AND R.pm_recipient = @userid