Calling a MySQL Stored Procedure via PHP

[CanMike]

Help,

I've upgraded to MySQL 5 & PHP 5 so that I can use stored procedures.

I've successfully complied and tested (from the mysql command line) a stored procedure add_stuff.sql, which insert a value into a table (t1).

Here is the simple SP in the MySQL DB.

delimiter ~

create procedure add_stuff( IN inC1 text)

BEGIN

  INSERT INTO t1 (c1) VALUES (inC1);

END ~

delimiter ;

From the MySQL command line I can issue the command: mysql> [FONT="Courier New"]call add_stuff('mike');[/FONT] and it works great.

So I wrote some PHP to test the call:

<?php # add_stuff.php

$conn = @mysqli_connect('localhost', 'miketest', 'mike', 'test1');

if (mysqli_connect_errno()) 
{
   printf("Connect failed: %s\n", mysqli_connect_error());
   exit();
}

$c1 = 'mike';


$result = @mysqli_query ($conn, 'call add_stuff($c1)');


if (mysqli_affected_rows($conn) == 1)
{
  printf("Success");
}
else
{
 printf("Errormessage Details: %s\n", mysqli_error($conn));
}

mysqli_close($conn);
?>

As you can see, I have not enclosed the variable $c1 in either single or double quotes in this version.
When I run this code I get this error: Errormessage Details: Unknown column '$c1' in 'field list'

So then I changed the call to 'call add_stuff('.$c1.') and I get the error: Errormessage Details: Unknown column 'mike' in 'field list'

I've been working with Oracle stored procedures for years, so I quite comfortable with the structure, but I'm lost when it comes to CALLING the SP from PHP.

What is the correct syntax used to include arguments in a call to a SP? What am I doing wrong? How do I pass an argument within a call statement?

Thanks

[tomhath]

You still didn't quote it. Try:

...
$result = @mysqli_query ($conn, "call add_stuff('$c1')" );
...

[CanMike]

Yup, that was it. I works now.

Thanks for the answer.

One more question. Do you know of any good books that I can purchase to learn more about using PHP with MySQL stored procedure/views?

I tryed the website for PHP and MySQL, but couldn't find a good tutorial about using the two together.

[Roger_Ramjet]

Not a lot of anything out there for mysql and php ver 5 yet. I spent 5 hours googling around the other night and found very little of use beyond what you'll find here.

As a general rule I find Sitepoint and Wrox to be the most up to date publishers who produce high quality books so go see what they've got on their websites.

[tomhath]

There's not much special about using stored procedures or views with php. You execute a sql query and (maybe) get back a result set. I don't know if there's much more to it.

What would happen if the proc returns more than one result set? It's probably buried in the php documentation somewhere, but I've never had a reason to dig it out...