CODEWITHSUNDEEP
mysqlpdofatal-error
Maciek
Maciek

Reputation: 1982

PDO Mysql create function and use in select

I want to create a MySQL function in my PDO setup.

$connection->exec('
DROP FUNCTION IF EXISTS return_id
CREATE FUNCTION return_id(id int(11)) RETURN int(11)
BEGIN
return id;
END');

$fetch=$connection->query('select *,return_id(id) as new_id from category')->fetchall();
print_r($fetch);

I am getting the error:

FATAL ERROR: UNCAUGHT ERROR: CALL TO A MEMBER FUNCTION FETCHALL() ON BOOLEAN

Upvotes: 0

Views: 83

Answers (1)

Nick
Nick

Reputation: 147166

There are a few problems here. Firstly, you're not checking the return status of your $connection->exec call which will be false because you have syntax errors, a missing ; at the end of the first line i.e. it should be

DROP FUNCTION IF EXISTS return_id;

However, PDO::exec will not execute multiple statements, so you need to put the function definition into a separate exec anyway.

Next: RETURN should be RETURNS

Finally, you're not checking the result of the call to $connection->query which is also returning false (the boolean which does not have a member function fetchAll) because it failed due to the previous errors.

To make it work you need to change your code to this. However you should as best practice also check the results of the calls to $connection->exec and $connection->query.

$connection->exec('DROP function IF EXISTS return_id');
$connection->exec('CREATE FUNCTION return_id(id int(11)) RETURNS int(11) BEGIN return id; END');
$fetch=$connection->query('select *,return_id(id) as new_id from category')->fetchall();
print_r($fetch);

Upvotes: 1

Related Questions