Converting MYSQL to Codeigniter

Question

I am trying to convert a MYSQL query to codeigniter and going no wheres real fast. I am trying to convert this query

$conn->prepare("SELECT `id`,`song`,`artist`,`album`,`track`,`mix_name`,`date` FROM `podcasts` where mix_number = (SELECT MAX(mix_number) FROM podcasts) order by track asc");

This is in my model:

//$where = '(SELECT MAX(mix_number)from podcasts)';
$this->db->select('id,song,artist,album,track,mix_name,date, link');
//$this->db->where('mix_number', '(SELECT MAX(mix_number)from podcasts)');
$this->db->order_by('track', 'asc');
$query = $this->db->get('podcasts');
return $query->result();

My problem area is in the where statement. When I comment out the where statement I get the data. Obviously not in the manner I want it.

I am doing it this way becuase my next query(s) will be

("SELECT `id`,`song`,`artist`,`album`,`track`,`mix_name`,`date` FROM `podcasts` where mix_number = **(SELECT MAX(mix_number) FROM podcasts) - 1** order by track asc")

And on down to (SELECT MAX(mix_number) FROM podcasts) - 3

Any thoughts on the proper way of writing the where statement? Thank you for yout time.

Quite similar question http://stackoverflow.com/questions/15212189/codeigniter-active-record-select-with-2-or-more-tables/15212366#comment21457973_15212366 - you can't do subqueries in CI directly with active record — jtheman, Mar 05 '13 at 21:39
Are you vcalling the where statement(as written) a subquery? — Brad, Mar 05 '13 at 21:50
Yes, its a query inside a query, thus called a subquery. CI active record have no native syntax for subqueries. But if youre fine with Wesleys solution below Im happy. — jtheman, Mar 05 '13 at 21:59
Why would you EVER use the unreadable CI active record notation? SQL is a standard and well known abstraction language for dataset manipulation. Why would you abstract an abstraction? Your code will be much more readable if you use the $this->db->query() method and write normal SQL. — Patrick Savalle, Mar 05 '13 at 23:17
@PatrickSavalle Bad example in this post, but it's used mainly for building dynamic queries (`if ($condition) $this->db->where($x) if ($otherCondition) $this->db->order_by($y)`..etc. It's better than mashing SQL strings together yourself. Another reason is to standardize inconsistencies with different db vendors (although most people use MySQL). yadda yadda. — Wesley Murch, Mar 06 '13 at 06:12

score 5 · Accepted Answer · edited Mar 12 '19 at 06:59

Set the third argument of where() to false to prevent CI from altering the string you pass in to the second argument, then you can do the subquery:

return $this->db
    ->select('id,song,artist,album,track,mix_name,date, link')
    ->where('mix_number', '(SELECT MAX(mix_number) from podcasts)', false)
    ->order_by('track', 'asc')
    ->get('podcasts')
    ->result();

https://www.codeigniter.com/userguide2/database/active_record.html

$this->db->where() accepts an optional third parameter. If you set it to FALSE, CodeIgniter will not try to protect your field or table names with backticks.

For me this produces the following query:

SELECT `id`, `song`, `artist`, `album`, `track`, `mix_name`, `date`, `link`
FROM (`podcasts`)
WHERE mix_number = (SELECT MAX(mix_number) from podcasts) ORDER BY `track` asc

Bingo, that got me a lot closer. Now I just need to flesh out a little html. Thank you Wesley and everyone else — Brad, Mar 05 '13 at 21:53

score 2 · Answer 2 · answered Mar 05 '13 at 21:40

2

If you are not too particular about using CodeIgniter's Active Record syntax, you can simply use your query as is:

$sql = "SELECT `id`,`song`,`artist`,`album`,`track`,`mix_name`,`date` FROM `podcasts` where mix_number = (SELECT MAX(mix_number) FROM podcasts) order by track asc";

$this->db->query($sql);

and then use $query->result() to get your results.

answered Mar 05 '13 at 21:40

Marc Audet

46,011
11
63
83

I tried that, it wont let me use $query->result()(error) Even without it, it wont return the data – Brad Mar 05 '13 at 21:49
I appreciate the feedback, will experiment with it for my own learning. Wesley Murch has the proper approach. All the best! – Marc Audet Mar 06 '13 at 01:52

Converting MYSQL to Codeigniter

2 Answers2