PHP rest API with call procedure

Question

I've often created an API like this:

public function getUserInfo($id) {
    $stmt = $this->conn->prepare("SELECT id, email FROM users WHERE id = ? ");
    $result = array();

    $stmt->bind_param("s", $id);
    $stmt->execute();
    $stmt->bind_result($id, $email);
    $stmt->fetch();

    $result['id'] = $id;
    $result['email'] = $email;

    return $result;
}

However, I need to develop the API using procedures. and My Code is not working.

public function getUserInfo($id) {
    $stmt = $this->conn->prepare("call getUser( ? )");
    $result = array();

    $stmt->bind_param("s", $id);
    $stmt->execute();
    $stmt->bind_result($id, $email); // error here
    $stmt->fetch();

    $result['id'] = $id;
    $result['email'] = $email;

    return $result;
}

What I have to do for get id and email in this case?

Why are you creating stored procedures for simple `SELECT` calls? This is often very wasteful and extremely counter-productive. Just make the call. Wrap it in a function like you've done. That's the best plan. — tadman, Aug 04 '17 at 07:10
I summarized the situation briefly. The actual procedure is more complex. — Centell, Aug 04 '17 at 07:13
Can you describe what exactly *not working* means in your case. Is there an error message, or, maybe, WSOD at least? — BlitZ, Aug 04 '17 at 07:13
This is also a problem when using simple queries such as "SELECT * FROM users". I do not know how to read parameters that are not specified in the select query.. — Centell, Aug 04 '17 at 07:14
You really should reserve stored procedures for situations where other queries are cluttered up by complicated calculations and there's no other way to simplify them, plus the logic these functions contain is unlikely to change in the future. A classic example here is GIS functions to compute distance on the 3D surface of the Earth instead of cartesian distance. You're just doing a `CALL` here so I think you're over-engineering this. — tadman, Aug 04 '17 at 07:15
I got this error code. Notice: Undefined variable: id in /opt/lampp/htdocs/AppAPI/includes/DBOperation.php on line 143 — Centell, Aug 04 '17 at 07:17

score 1 · Accepted Answer · answered Aug 04 '17 at 07:16

1

Don't call fetch() if you want the column names as well, call fetch_row():

$result = $stmt->fetch_row();

You can delete any unnecessary columns from that structure, or add in any other fields you want.

answered Aug 04 '17 at 07:16

tadman

194,930
21
217
240

Thank you for your answer! Your answer is very helpul to me. I tried fetch in various ways and succeeded. I finally sove it like $conn->store_result()->fetch_assoc(); – Centell Aug 07 '17 at 06:39
Remember that some of those steps can fail, so be sure to [enable exceptions](https://stackoverflow.com/questions/18457821/how-to-make-mysqli-throw-exceptions-using-mysqli-report-strict) so you don't miss errors. – tadman Aug 08 '17 at 15:21
Thank you for your advice! – Centell Aug 10 '17 at 01:46

PHP rest API with call procedure

1 Answers1