call stored mySQL Procedure

[eriche]

Hello,

After many hours trying to figure out on my own how to call a procedure with parameters from an extended Xenforo PHP class - I have to ask you guys for help

Calling a procedure without parameters or with hardcoded parameters works fine!

$stmt = $db->prepare('CALL my_procedure_test(2)');
$stmt->execute();
$rows = $stmt->fetchAll();

But how to call the procedure with the bindParam command

$stmt = $db->prepare('CALL my_procedure_test(?)');
$stmt->bindParam(1, 5);
$stmt->execute();
$rows = $stmt->fetchAll();

I also tryed the syntax for the parameter like

:param1 instead of "?"

But it always fails

So please tell me whats the correct syntax to call the procedure with bindParam from Xenforo with the default db-Adapter.

thX Erich

 

[Jim Boy]

why dont you try

$stmt = $db->prepare('CALL my_procedure_test(?)');
$stmt->execute(array(5));
$rows = $stmt->fetchAll();

 

[Jim Boy]

Also I supect bind doesn't numbers, try :var instead of ?

eg

$stmt = $db->prepare('CALL my_procedure_test(:var)');
$stmt->bindParam("var", 5);

 

[eriche]

@Jim Boy sadly also your methodes are not working
I googled so much and none of the syntaxes works

I know the Zend Framework is used - but how should I call the procedure with the parameters?

For your first solution I get following error

ErrorException: Array to string conversion - library\...\UserRegistrationCT.php:62

For the 2nd Solution, which I tryed also before, but it always failed because of the ":" before the parameter.

Zend_Db_Statement_Mysqli_Exception: Mysqli prepare error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':param1)' at line 1 - library\Zend\Db\Statement\Mysqli.php:77

$stmt = $db->prepare('CALL my_procedure_test(:param1)');
$stmt->bindValue(":param1", 7);
$stmt->execute();
$rows = $stmt->fetchAll();

I am happy for every tested hint
With such a small problem I struggle around 2 days

Erich

 

[GeorgeS]

Hallo, eriche -


take a first look here: http://php.net/manual/en/mysqli.quickstart.prepared-statements.php

For each ? in the prepared statement you must provide a stmt->bind_param(cType, xValue) where cType gives the type of the parameter to pass, i.e. "i" for integer, and xValue gives the value to pass. More on this here: http://php.net/manual/en/mysqli-stmt.bind-param.php

After you passed all parameters, you can call $stmt->execute()