MySQL Query using ?deprecated? syntax

MySQL Query using ?deprecated? syntax - 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: MySQL Query using ?deprecated? syntax (/showthread.php?tid=21842)

MySQL Query using ?deprecated? syntax - El Forum - 08-22-2009

[eluser]AIM-andrew[/eluser]
I'm having trouble adapting existing code to new requirements.
Furthermore, I do not know which version of codeigniter I'm supporting.

Here is the existing code:

Code:
public function addToUserId($email, $new_to_user_id) {

     $this->db->select('username');

     $this->db->from('fa_user');

     $this->db->from('user');

     $this->db->where('fa_user_id = id');

     $this->db->where('user_id', $new_to_user_id);

     $results = $this->db->get()->result();

     $email_to = $email->getTo();

     $email_to[] = $results[0]->username;     

}

This method is called from the model layer to add $new_to_user_id to the To: array of addresses of an email.

Our email system has changed and this is the troublesome code:

Code:

public function addToUserId($email, $new_to_user_id) {

     // SFPD uses [email protected] format

     // $this->db->select('username');

     $this->db->select('CONCAT (firstname, \'.\', lastname) AS full_name');

     $this->db->from('fa_user');

     $this->db->from('user');

     $this->db->where('fa_user_id = id');

     $this->db->where('user_id', $new_to_user_id);

     $results = $this->db->get()->result();



     $email_to = $email->getTo();

     // $email_to[] = $results[0]->username;

     $email_to[] = $results[0]->full_name;

}

My predecessor's SELECT statement is bizarre in how they have used multiple lines. I have attempted to follow their syntax. All I need from the database is instead of username I want the corresponding firstname and lastname of the $new_to_user_id.

I'd posted previously, but the talk of Active Record was whizzing over my head.

Any advise is greatly appreciated.
Thanx in advance.

MySQL Query using ?deprecated? syntax - El Forum - 08-22-2009

[eluser]steelaz[/eluser]
Is there a reason why you don't want to join firstname and lastname strings after you get them from database (below)?

Code:
public function addToUserId($email, $new_to_user_id) 

{

     $this->db->select('firstname', 'lastname');

     $this->db->from('fa_user');

     $this->db->from('user');

     $this->db->where('fa_user_id = id');

     $this->db->where('user_id', $new_to_user_id);

     $results = $this->db->get()->result();

     $email_to = $email->getTo();

     $email_to[] = $results[0]->firstname .'.'. $results[0]->lastname;

}

Also, if you're expecting just one result, instead of using result(), you can use row():

Code:
public function addToUserId($email, $new_to_user_id) 

{

     $this->db->select('firstname', 'lastname');

     $this->db->from('fa_user');

     $this->db->from('user');

     $this->db->where('fa_user_id = id');

     $this->db->where('user_id', $new_to_user_id);

     $result = $this->db->get()->row();

     $email_to = $email->getTo();

     $email_to[] = $result->firstname .'.'. $result->lastname;

}

MySQL Query using ?deprecated? syntax - El Forum - 08-22-2009

[eluser]kgill[/eluser]
This

Code:
$this->db->select('CONCAT (firstname, \'.\', lastname) AS full_name');

 $this->db->from('fa_user');

 $this->db->from('user');

 $this->db->where('fa_user_id = id');

 $this->db->where('user_id', $new_to_user_id);

is the same as this:

Code:
select CONCAT(firstname, '.', lastname) AS full_name

  from fa_user, user

  where fa_user_id = id 

  and user_id = $new_to_user_id

// OR this

  select CONCAT(firstname, '.', lastname) AS full_name

  from fa_user left join user on fa_user_id = id

  where user_id = $new_to_user_id

It's not really a depreciated syntax it's just an alternate way of writing things, where it gets useful is when you're dealing with lots of table joins, instead of nesting all the join clauses you just list the how the tables are linked in the where clause. By the way if AR is confusing you, you can use db->query and type the SQL statement directly if you need to get things fixed quickly.