SQL: Need someone else to look at this, please - Printable Version +- 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: SQL: Need someone else to look at this, please (/showthread.php?tid=16198) |
SQL: Need someone else to look at this, please - El Forum - 02-27-2009 [eluser]coding_hero[/eluser] This is probably simple, but I'm not getting anywhere with it. I think I've been looking at it too long. I have two tables, user_access and collection. The table user_access contains the following values: user_id | collection_id ======================== 4 | 3 7 | 3 3 | 15 4 | 15 7 | 15 collection contains the following values: id | name =========================== 1 | Some entry 3 | This is Sample entry 3 15 | Yet another entry What I'm trying to do is find all 'collections' that are NOT associated with a UID. So, for example, UID 4 has access to 3 and 15, but not 1. Here's the SQL I'm using: Code: SELECT DISTINCT collection.id, collection.name And it works fine in phpMyAdmin. However, when I use it in code, it returns 3 results. Here's the contents of the model function: Code: public function get_collections_unavailable_to_user ($user_id) SQL: Need someone else to look at this, please - El Forum - 02-27-2009 [eluser]pistolPete[/eluser] A general advice: Use the profiler to see which queries were actually generated. Code: $this->output->enable_profiler(TRUE); ActiveRecord does currently not support the use of round brackets. (see here, here, here, etc.) I suggest using the plain SQL statement instead: Code: $this->db->query('SELECT DISTINCT collection.id, collection.name ...'); SQL: Need someone else to look at this, please - El Forum - 02-27-2009 [eluser]coding_hero[/eluser] [quote author="pistolPete" date="1235774552"]A general advice: Use the profiler to see which queries were actually generated. Code: $this->output->enable_profiler(TRUE); Aha! Thanks, I was looking for something like that. I eventually started stepping into the ActiveRecords class to see what SQL it was generating mere minutes before you posted. Quote:I suggest using the plain SQL statement instead: Yah, that's the conclusion I came to also. Glad to know it wasn't me doing something stupid. I can blame the class for this one Thanks for the help! |