Recuperar parametro salida de un sp con mysqli

[ferminako]

Hola a todos, quisiera saber si alguien podria ponerme el codigo en php de ejemplo de como ejecuta un sp de mysql usando mysqli y recupera un parametro de salida del mismo, he buscado y probado todo lo que encuentro y no consigo realizarlo.

He probado cosas como:

Código PHP:

$res=mysqli_query($cn,"call I_Familia(@res,'".$this->quitar($nombre)."');"); 
   
$res2=mysqli_query($cn2,"Select @res"); 
  
 
 $row=mysqli_fetch_assoc($res2);
  $variable=$row['@res'];
  echo $variable;
 

pero no funcionan....

Muchas gracias.

[onRush]

A sample connection to a MySQL database using the library database described
earlier might look like the following code snippet. Keep in mind that this example
uses the object-oriented interface for mysqli.

Código PHP:

$mysqli = new mysqli(’localhost’, ’dbuser’, ’dbpass’, ’library’);
 
if  (mysqli_connect_errno()) {
echo    ’Connect failed: ’ . mysqli_connect_error();
exit;
}
 
// All other database calls go here
 
$mysqli->close();
 

The same connection using a procedural approach instead of OOP might look
like this. Note that there are not many differences, the main one being the use
of the database resource in the subsequent mysqli_* function calls, such as in
mysqli_close() here.

Código PHP:

$dbh = mysqli_connect(’localhost’, ’dbuser’, ’dbpass’, ’library’);
 
if  (!$dbh) {
echo    ’Connect failed: ’ . mysqli_connect_error();
exit;
}
 
// All other database calls go here
 
mysqli_close($dbh);
 
 
// Filter input from $_GET
$author = ’’;
if  (ctype_alpha($_GET[’author’]))
{
$author = $_GET[’author’];
}
 
// Escape the value of $author with mysqli->real_escape_string()
$sql = ’SELECT author.*, book.* FROM author
LEFT JOIN book ON author.id = book.author_id
WHERE author.last_name = ’ . $mysqli->real_escape_string($author);
 
// Execute the statement and echo the results
 
if  (!$mysqli->real_query($sql)) {
echo    ’Error in query: ’ . $mysqli->error;
exit;
}
 
if  ($result = $mysqli->store_result()) {
while   ($row = $result->fetch_assoc())
{
echo    "{$row[’title’]}, {$row[’last_name’]}\n";
}
$result->close();
}
 
For the procedural style, the code would look like this:
 
// Filter input from $_GET
$author = ’’;
if  (ctype_alpha($_GET[’author’]))
{
$author = $_GET[’author’];
}
 
 
 
 
 
 
 
 
Database Programming    ” 165
 
 
// Escape the value of $author with mysqli->real_escape_string()
$sql = ’SELECT author.*, book.* FROM author
LEFT JOIN book ON author.id = book.author_id
WHERE author.last_name = ’ . mysqli_real_escape_string($dbh, $author);
 
// Execute the statement and echo the results
if  (!mysqli_real_query($dbh, $sql)) {
echo    ’Error in query: ’ . mysqli_error();
exit;
}
 
if  ($result = mysqli_store_result($dbh)) {
while   ($row = mysqli_fetch_assoc($result))
{
echo    "{$row[’title’]}, {$row[’last_name’]}\n";
}
mysqli_free_result($result);
}
 
 
Prepared Statements and Bound Parameters With mysqli
 
// Filter input from $_GET
$author = ’’;
if  (ctype_alpha($_GET[’author’]))
{
$author = $_GET[’author’];
}
 
// Set a named placeholder in the SQL statement for author
$sql = ’SELECT book.title FROM author
LEFT JOIN book ON author.id = book.author_id
WHERE author.last_name = ?’;
if  ($stmt = $mysqli->prepare($sql)) {
 
$stmt->bind_param(’s’, $author);
$stmt->execute();
$stmt->bind_result($title);
 
while   ($stmt->fetch()) {
echo    "{$title}, {$author}\n";
}
 
$stmt->close();
}
 
 
 
Again, the same code using the procedural approach looks like this:
 
// Filter input from $_GET
$author = ’’;
if  (ctype_alpha($_GET[’author’]))
{
$author = $_GET[’author’];
}
 
// Set a named placeholder in the SQL statement for author
$sql = ’SELECT book.title FROM author
LEFT JOIN book ON author.id = book.author_id
WHERE author.last_name = ?’;
 
if  ($stmt = mysqli_prepare($dbh, $sql)) {
 
mysqli_stmt_bind_param($stmt, ’s’, $author);
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($dbh, $title);
 
while   (mysqli_stmt_fetch()) {
echo    "{$title}, {$author}\n";
}
 
mysqli_stmt_close($stmt);
}
 
 
Transactions With mysqli
 
 
$mysqli->autocommit(FALSE);
 
$mysqli->query("INSERT INTO book (isbn, title, author_id, publisher_id)
VALUES (’0395974682’, ’The Lord of the Rings’, 1, 3)");
$mysqli->query("INSERT INTO book (title) VALUES (’Animal Farm’, 3, 2)");
 
if  (!$mysqli->commit()) {
$mysqli->rollback();
}
 
The procedural version of the code is very similar:
 
mysqli_autocommit($dbh, FALSE);
 
mysqli_query($dbh, "INSERT INTO book (isbn, title, author_id, publisher_id)
VALUES (’0395974682’, ’The Lord of the Rings’, 1, 3)");
mysqli_query($dbh, "INSERT INTO book (title) VALUES (’Animal Farm’, 3, 2)");
 
if  (!mysqli_commit($dbh)) {
mysqli_rollback($dbh);
}
 

[ferminako]

Nose si decir gracias, porque no entiendo nada jajaja, podrias ser algo más concreto???? ahi veo sql preparadas pero no stored procedures... no entiendo

[ferminako]

encontre la solucion, espero que a alguien le sirva:

$ivalue=1;
$res = $cn->multi_query( "call I_Familia(@res,'".$this->quitar($nombre)."');SELECT @res" );
if( $res ) {
$results = 0;
do {
if ($result = $cn->store_result()) {
printf( "<b>Result #%u</b>:<br/>", ++$results );
while( $row = $result->fetch_row() ) {
foreach( $row as $cell ) echo $cell, "&nbsp;";
}
$result->close();
if( $cn->more_results() ) echo "<br/>";
}
} while( $cn->next_result() );
}
$cn->close();