Can someone please explain the logic behind this...

[eluser]daparky[/eluser]
I've done a query but to me it doesn't make sense as to why it returns correct results when the array says it's not possible...

Here's the query:

Code:

function get_results($table2 = '')
    {
        $this->db->select('table1.*, GROUP_CONCAT(DISTINCT table2_id) AS table2_id');
        $this->db->join('table2', 'table2_id = table1_table2_id', 'left');
        

        if($table2 != '')
        {
            $this->db->where('table2_id', $table2);
        }
        
        $this->db->group_by('table1_id');
        

        return $table1;
    }

returns an array like this:

Code:

[0] => stdClass Object
        (
            [table1_id] => 20
            [table1_name] => dsfdsf
            [table2_id] => 28,239,20
        )

The part i don't get is that when i select something from a dropdown that has the an id matching any in table2_id it returns the correct result. isn't table2_id a string? I'm not exploding those ID's into seperate keys.

[eluser]pickupman[/eluser]
I am not sure what your question is. It almost seems you are thinking out loud asking a rhetorical question. You are getting the string from the GROUP_CONCAT. What are you referencing by "select something from a dropdown"?

[eluser]daparky[/eluser]
Hi Phil.

The var $table2 is from a dropdown, if I select anything that has the same id as table2_id it returns the result correctly despite table2_id has comma separated ids. I just wondered how that worked because usually I would explode the ids then match, but it seems to be matching without exploding.

[eluser]mddd[/eluser]
You are not comparing the string table2_id.
Your WHERE statement takes effect BEFORE the GROUP_CONCAT is done. That means that WHERE table2_id = {x} refers to the field table2_id in your table. NOT to the pseudo-column table2_id that contains the GROUP_CONCAT result.

Write it like this and it will be clearer:

Code:

$this->db->select('table1.*, GROUP_CONCAT(DISTINCT table2_id) AS found_table_ids');
$this->db->join('table2', 'table2_id = table1_table2_id', 'left');

if($table2 != '')
{
   $this->db->where('table2_id', $table2);
}

If you wanted to match the GROUP_CONCAT you would have to write

Code:

HAVING found_table_ids = '28,239,20'