Background:
I am working on an Online Browser Based Game and it is going on pretty good. I just completed a new version and the game is working fine, maybe some minor errors but nothing to worry about. But now I felt my code was getting a bit complicated and difficult to understand so clearly (I am working with 10 frames at the same time), so I though of re-estructuring and optimizing the code a bit.
As I heard before of Stored Procedures that MySQL had I had though of replacing my older statements made on the script with those Stored Procedures. So I went on searching on the Internet about how to use those Procedures. I surfed through Uninformative and Complicated sites, and those who had the information just showed examples and syntax with MySQL directly (no reference on how to make it via PHP).
After 3~4 hours of searching and trying I finally figured out on how to CREATE and DROP Procedures. And a basic CALL to procedures.
I created a simple test-script with a CALL to a Procedure who would do this: "SELECT * FROM usertable", and then the script would output some information. But it happened to me that I could not get a result from my query, like when you do $result = mysql_query($query); .
Well, I'm getting annoyed with this, I found no tutorial that mentioned on how to work with MySQL Stored Procedures and PHP. My post here is the last chance for me to still change the code to use Stored Procedures.

Questions:
1. Can anyone here help me out on how to work with Stored Procedures via PHP. Call the Procedures and get the data (for example of a SELECT).
2. And on how to make statements with variables. Create procedures with variables on MySQL and Call procedures with variables with PHP.

Additional:
So basically it is more a question on how to work with MySQL and PHP when using Stored Procedures.

Additional Information:
Running on Windows XP.
MySQL Version 5.0.33
PHP 5.2.1
Apache 2... (shouldn't be needed this)
The game is still on development, so my working Computer is the same as where the Apache and MySQL server is (localhost)

You're not going to like this answer, but it sounds like MySQL/PHP may not be up to the job. They only got around to adding procs in v5, so maybe PHP doesn't support this yet? I'd look into ASP instead if you're running this on Windows.

The problem
You're getting the error because the stored procedures can only do things rather than select out rows. You can double check this by calling the stored procedure from within MySQL and seeing if it raises an error. I'm sure in future versions of MySQL this will be allowed.

An easy way out
My advice would be to simply use select statements to get the data out of the database and use stored procedures to do things in the database.

If you must use stored procs to pull the data
just get the stored proc to insert the data into an output table which will act as a temporary storage before the data is extracted using a normal select i.e. make the following calls from PHP:-

create table output_table(
user_id int,
field_1 varchar(255) ,
field_2 varchar(255)
)

create procedure my_proc(
IN in_user_id int,
IN in_my_other_params varchar(20)
)
begin
-- delete existing output data for user
delete from output_table
where user_id = in_user_id;

-- do some processing

-- insert the rows required into the output table
insert output_table ( user_id, field_1, field_2 )
select in_user_id, your_data_1, your_data_2
from some_other_table
end;

-- in php call the proc
$result=mysql_query( "call my_proc()" );

-- then in php get data to output
$result=mysql_query( "select field_1, field_2
from output_table
where user_id = $user_id" );

while( list( $str1, $str2 ) = mysql_fetch_row( $result ) ) {
echo "$str1 $str2";
}

This code hasn't been checked (and I have just returned from the pub) but it is close to what you're after. You'll be limited to a fixed number of columns each time but you should be able to design your code around this. I use this method when I'm using MySQL and it works fine.

Mike

Learning Newbie, this may also be the cause for the lack of information on about PHP on that, guess I have to wait then. I'm not having problems with writing Database queries on my code, I just though of tuning it up a bit on that.

Mike Bike Kite, I'm understanding, so the procedure puts the information into a temporary database for me to get it. But...isn't this method slower than the simple method?
The simple one would send information, select data, and get data.
But it seems to me that here we are: Sending a small information (call), having the Database to copy some values, Sending information (mysql_query), selecting data and get data.

Schimassek...

But...isn't this method slower than the simple method?
The simple one would send information, select data, and get data.
But it seems to me that here we are: Sending a small information (call), having the Database to copy some values, Sending information (mysql_query), selecting data and get data.

Allways do things in the simplest way you can - this works for coding, life etc etc. So, if all you need to do is insert data and select the data, then just insert the data and select it.

If you need to have some more complex coding using the data in the database then I'd use stored procedures to do that stuff and then use select statements to pull the data out.

It might also be worth looking at functions as well which return a single value but can be called within a select statement i.e.
select my_func( $user_id, $some_other_data )
PHP works fine calling MySQL stored procedures.

But...isn't this method slower than the simple method?
The simple one would send information, select data, and get data.
But it seems to me that here we are: Sending a small information (call), having the Database to copy some values, Sending information (mysql_query), selecting data and get data.

Performance - It doesn't really matter so much about speed for most applications as databases are unlikely to be the restricting factor. If at a later date you find that things just aren't running fast enough then start to look at how things can be improved.

Simplicity - I'd do things in the simplest way you can - this works for SQL, PHP, life etc etc. If all you need to do is insert data and then later select the data - then just do exactly that.

Complexity - When things get a bit more complex then use stored procs to put all the logic for doing something in one place. Then use select statements to pull the data out.

And yes, PHP works fine calling MySQL stored procedures. It might also be worth you looking at functions as well.

Mike

Well, this helped me a lot, thanks.
For now I think it is better to leave the mysql_querys on the code untill they enchance PHP to work better with these procedures and functions. I still have lots of other work to do ^_^' like re-estructuring the code for better reading and inserting of code.

Schimassek...