CodeIgniter Forums
model queries preference - 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: model queries preference (/showthread.php?tid=52664)

model queries preference - El Forum - 06-20-2012

[eluser]brian88[/eluser]
I wanted to do the below code because thats what codeigniter prefers. But when I try to access it in the controller I get an error in my sql because of the quotes. So im forced to do it the 2nd way(down below)
Code:
// get all posts
function getPosts($table, $id = 'id', $limit = '100') {
  $q = $this->db->query("
   select *
   from ?
   order by ? desc
   limit ?
  ",array($table, $id, $limit));
  
  if($q->num_rows() > 0){
   return $q->result();
  }
} // end function

i get an error with this code because of the quotes. is there a way around the quotes?
Code:
$data['posts'] = $this->main_mod->getPosts('posts', 'id', '50');

// error says...
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''posts' order by 'id' desc limit '100'' at line 2
select * from 'posts' order by 'id' desc limit '100'

2nd way
Code:
// get all posts
function getPosts($table, $id = 'id', $limit = '100') {
  $q = $this->db->query("
   select *
   from {$table}
   order by {$id} desc
   limit {$limit}
  ");
  
  if($q->num_rows() > 0){
   return $q->result();
  }
} // end function

Does it really matter between what one I use here? I figure the 1st code example is more secure since its code igniters way.

model queries preference - El Forum - 06-20-2012

[eluser]Brad K Morse[/eluser]
Try this in the model

Code:
function getPosts($table, $id = 'id', $limit = 100) {

  $q = $this->db->select('*')->from($table)->order_by($id, 'desc')->limit($limit);
  
  if($q->num_rows() > 0)
    return $q->result();
  
  return false;
}

calling in controller:

Code:
$data['posts'] = $this->main_mod->getPosts('posts', 'id', 50);


model queries preference - El Forum - 06-21-2012

[eluser]CodeIgniteMe[/eluser]
Code:
// get all posts
function getPosts($table, $id = 'id', $limit = '100') {
  $q = $this->db->query("
   select *
   from ?
   order by ? desc
   limit ?
  ",array($table, $id, $limit));
  
  if($q->num_rows() > 0){
   return $q->result();
  }
} // end function

this method is only for query values, which is why it is automatically escaped as stated on the user guide
Queries

Brad's method is the recommended one for table and field names