+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Can someone please explain the logic behind this... (/showthread.php?tid=31816)
Can someone please explain the logic behind this... - El Forum - 07-02-2010
[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.
Can someone please explain the logic behind this... - El Forum - 07-02-2010
[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"?
Can someone please explain the logic behind this... - El Forum - 07-03-2010
[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.
Can someone please explain the logic behind this... - El Forum - 07-05-2010
[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'