model queries preference

[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.

[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);

[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