+- 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: activerecord and mysql differences? (/showthread.php?tid=27871)
activerecord and mysql differences? - El Forum - 02-23-2010
[eluser]andychurchill[/eluser]
$this->db->select('shows.url, shows.title as showTitle, shows.Description as showDescription, episodes.thumbnailImage');
$this->db->from('shows');
$this->db->join('episodes', 'episodes.show_id = shows.id');
$this->db->where('shows.genre_id', '(select genre_id from shows where url = \''.$show.'\')', false);
$this->db->where('shows.url !=', $show);
$query = $this->db->get();
echo $this->db->last_query();
using the above generates this:
SELECT `shows`.`url`, `shows`.`title` as showTitle, `shows`.`Description` as showDescription, `episodes`.`thumbnailImage` FROM (`shows`) JOIN `episodes` ON `episodes`.`show_id` = `shows`.`id` WHERE shows.genre_id =(select genre_id from shows where url = 'some-other-programme') AND `shows`.`url` != 'some-other-programme'
running this in MySQL Query Browser returns one row as expected, but CI seems to be returning 0 results? It's the same database, etc, nothing looks wrong, but it's just not working as I'd expect it to.
Any ideas?
activerecord and mysql differences? - El Forum - 02-23-2010
[eluser]Phil Sturgeon[/eluser]
Quote:1.) Output the final query and see if it looks right. Do this after your model call or within the model after $this->db->get().
// Clear any existing output (optional)
ob_clean();
echo $this->db->last_query();
// Stop PHP from doing anything else (optional)
exit();
2.) If it looks fine, try running it in phpMyAdmin or another GUI.
3.) If it fails, you get an error message from MySQL server telling you what’s wrong. It will make it pretty obvious what change needs to be made to get it working.
4.) If it runs fine, then CodeIgniter is not running the query correctly. This could:
a.) CodeIgniter is logged into MySQL with a different user who may not have the same permissions.
b.) A condition is not being met in your model file or controller that is stopping the query from being run. Output values along the way.
Debugging ActiveRecord queries in CodeIgniter.