I have a MySQL function that looks like this:
delimiter #
create function activate_user
(
p_activation_hash char(32)
)
returns int
deterministic
begin
declare v_ret int unsigned default 0;
if (char_length(p_activation_hash) != 32) then
return 0;
end if;
insert ignore into users (email, password_hash, first_name, last_name, company_name)
select email, password_hash, first_name, last_name, company_name
from user_activation
where activation_hash = p_activation_hash
and expiry_date > now();
return last_insert_id();
end #
delimiter ;
And if I call it like this using CodeIgniter:
$query = $this->db->query("select activate_user(?);", array($this->input->get('key')));
var_dump($query->result_object());
var_dump($query->result_array());
I can get either an array or object of results that looks like this:
array
0 =>
object(stdClass)[16]
public 'activate_user('5c9835e8dee7e1b43691708f503cb7f5')' => string '0' (length=1)
array
0 =>
array
'activate_user('5c9835e8dee7e1b43691708f503cb7f5')' => string '0' (length=1)
Obviously due to the key it isn't very useable and I don't really want to do a foreach it seems like there should be a better way?
I thought one of the point big advantages of mysql functions was to return things like integers, but reading these intergers seems messy in php, am I missing something?
For your information this is the most concise I got it:
$query = $this->db->query("select activate_user(?) as user_result;", array($this->input->get('key')));
$result = current($query->result_array());
echo "function result: " . $result['user_result'];