Why can't I call a MySQL stored function in a prepared query via PHP?

Question

I have the following query in a prepared statement:

SELECT 
  cid, cname, cemail, count_client_locations(cid) AS loc_cnt
FROM 
  clients 
WHERE isactive = 1 ORDER BY cname ASC

I have a stored function on the server count_client_locations. The query part of the function looks like this:

RETURN (SELECT COUNT(lid) FROM locations WHERE linked_client = cid);

When I run the SQL in MySQL Workbench, it returns the desired result:

cid | cname       | cemail            | loc_cnt
------------------------------------------------
2   | Acme Inc    | fred@example.com  | 3
1   | Example Ind | alice@example.com | 5
3   | Foobar Inc  | joe@example.com   | 0
1   | Barfoo Ltd  | hello@example.com | 1

When I run that via PHP mysqli prepared statement, it fails to prepare it (Fatal error: Call to a member function execute() on a non-object). If i take out the call to the function, it works fine.

EDIT PHP code:

$sql = $conn->prepare("SELECT cid, cname, cemail, count_client_locations(cid) AS loc_cnt FROM clients WHERE isactive = 1 ORDER BY cname ASC");
$sql->execute();

So why does this query work in MySQL workbench, and not in the PHP code, and is there anything I can do to fix it?

@Drew - I am not sure if I follow what you mean. I got what wrong? The query? — dan, Nov 28 '15 at 07:32
http://stackoverflow.com/questions/12769982/reference-what-does-this-error-mean-in-php/12769983#12769983' — Drew, Nov 28 '15 at 07:35
try count(cid)..... showing more of your editphp code might helps. — Mark Ng, Nov 28 '15 at 07:55
It looks as though your `prepare()` is failing. What does your webserver's error.log say? Also, the contents of `$conn->error` might be enlightening. — Darwin von Corax, Nov 28 '15 at 07:59
var_dump( get_class( $conn ) ); just before prepare statement if it is ok, var_dump( $sql ) after prepare statement and let me know what you are getting . — Prafulla Kumar Sahu, Nov 28 '15 at 08:48

score 0 · Answer 1 · answered Nov 28 '15 at 10:56

0

You're getting an error message Fatal error: Call to a member function execute() on a non-object.

Just to be clear, this is a PHP error message, not a MySQL error message. The error is not related to your query string or to your database in any way at all.

Your code looks like this: $conn->prepare(...).

The error is occurring because PHP doesn't recognise the $conn variable. You might think $conn is the MySQL connection object, but PHP is looking at $conn when you call it and seeing null (or possibly false or some other non object value). This is why it is giving this error.

You haven't shown us enough code for me to explain why it might be doing this; possibly you're in a function and haven't passed $conn into it, but there are other possible reasons as well. As I can't be certain, I'll leave you to work it out from there.

answered Nov 28 '15 at 10:56

Spudley

166,037
39
233
307

Thanks for the answer Spudley. I did mention that if I take out the call to the stored function in the query itself, the query works as it should (less the `count_client_locations(cid) AS loc_cnt` in the selected columns. I did also mention that the query fails to prepare. My actual question was why the original query works on the MySQL Workbench, but not in the PHP code (as I thought if it works in one it works in the other). However I just fixed my problem. – dan Nov 28 '15 at 11:17
1

Yeah; the `prepare` was failing (at a MySQL level) and returning `false`, and because you then called `prepare()` on it rather than checking the return value for an error state, you were effectively executing `false->prepare()` in PHP, which led to the PHP error you actually saw. – Matt Gibson Nov 28 '15 at 11:50

dan · Accepted Answer · 2015-11-28T12:00:50.687

0

So, answering my own question as to why the query was working in MySQL Workbench and not in the PHP code is because I wasn't specifying which database for the stored function call:

SELECT 
  cid, cname, cemail, DBName.count_client_locations(cid) AS loc_cnt
FROM 
  clients 
WHERE isactive = 1 ORDER BY cname ASC

edited Nov 28 '15 at 12:00

answered Nov 28 '15 at 11:24

dan

856
9
20

1

You can probably also specify a default database for the connection to use, at which point you won't need to specify it for every statement (it's an optional parameter when creating a new mysqli connection.) – Matt Gibson Nov 28 '15 at 11:48

Why can't I call a MySQL stored function in a prepared query via PHP?

2 Answers2