New to CI would like more info on $this->db->query(), $this->db->get(), etc.

[eluser]betoranaldi[/eluser]
So I am new to CI and have gone through the entire user manual and skimmed through the wiki.

I am diving into accessing databases and creating queries. I am a little confused on how to use $this->db->query(), $this->db->get(), etc.

Well maybe not confused but would like to know their abilities and how to use them.

Are there any resources that explain the uses of $this->db->query(), $this->db->get(), etc.?

Also if anyone has any resources about joining tables and how to utilize that information.

Thank you in advance.

-Brian

[eluser]Michael Wales[/eluser]
There's really not much of a better resource than the User Guide, so I'll just toss some code examples at you and hopefully they stick:

If you do not want to use ActiveRecord (CodeIgniter's Query Building Class) then you are stuck with the query() method. This allows you to write your own SQL statements and make use of CI's Query Binding - the closest thing to a prepared statement you will get with CI. For the most part, this is rarely used - most of us use ActiveRecord.

Code:

$sql = 'SELECT * FROM users WHERE username = ? and password = ?';
$query = $this->db->query($sql, array('walesmd', md5('wizard')));
if ($result->num_rows() > 0) {
  return $query->row();
}
return FALSE;

ActiveRecord is the route most of us go - it allows you to stay in the PHP mindset, writing PHP code to generate your SQL queries.

Code:

// Do the same thing as the previous example.
$query = $this->db->get_where('users', array('username' => 'walesmd', 'password' => md5('wizard')));
if ($query->num_rows() > 0) {
  return $query->row();
}
return FALSE;

Code:

// Get a list of all of our users, sorted by registration date
$this->db->order_by('created_on', 'DESC');
$query = $this->db->get('users');
if ($query->num_rows() > 0) {
  return $query->result();
}
return FALSE;

Code:

// This will create a new user
$u = array(
  'username' => 'walesmd',
  'password' => md5('wizard'),
  'created_on' => date('Y-m-d', time()));
$query = $this->db->insert('users', $u);
if ($query->affected_rows() > 0) {
  return TRUE;
}
return FALSE;

Code:

// This will change a user's password
$u = array('password' => md5('blue hat'));
$query = $this->db->update('users', $u, array('username' => 'walesmd'));
if ($query->affected_rows() > 0) {
  return TRUE;
}
return FALSE;

Hopefully these, plus the User Guide, can point you in the right direction.

[eluser]betoranaldi[/eluser]
Micheal,

These are some great examples and are tremendously helpful. Thank you for taking the time to post them.

I just noticed the active record area in the user guide, I totally missed this page!

I went through all of the general topics (page by page) but only skimmed through the rest.)
http://ellislab.com/codeigniter/user-gui...record.htm

If you could just let me know if I am handling this scenario properly/or that I am on the right track...

There are two tables, one with users and another with jobs.

users (id, name, etc.) // saved in session data on login
jobs (id, user, details)

There will be a column in the jobs table that relates to a unique id of a user

Code:

$query = $this->db->get_where('jobs', array('user' => $this->session->userdata('userid')));

if ($query->num_rows() > 0) {
  return $query->row();
}
return FALSE;

This would display all "jobs" that are assigned to the user id in the session.

[eluser]Michael Wales[/eluser]
Close - the row() method of your $query object will only return the first row as an object. You'll want to use the result() method to return multiple rows (and loop through them with a foreach in your view).

[eluser]jinfusion[/eluser]
What is missing from the user guide in my estimation, Are return types. Does $this->db->insert() return a boolean based on success? does it return the id of the inserted record? Stuff like that would be super helpful.

[eluser]betoranaldi[/eluser]
You can get the inserted record id by using:

Code:

$this->db->insert_id();