• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
MySQL Query using ?deprecated? syntax

#1
[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 firstname.lastname@domain.com 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.

#2
[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;
}

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


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2019 MyBB Group.