PHP - Stored Procedure Problem

Hi Guys

After 6 month of "information management" I had enough and am back to doing some development.

although, I seem to have hit the wall.
When I run a code below, the query for stored procedure returns the amount of results, when it actualy should return a new name for the image to be used further.

The Stored Procedure runs fine if called on MySQL Server


$House_ID='100';

//Check that there were no errors with any of the uploaded image
//Checks each image seperately
foreach ($_FILES["image"] ["error"] as $image => $error)
{
//If there are no errors
if ($error == UPLOAD_ERR_OK) {

//Read a name of the new image
$Old_Name=$_FILES['image']['name'][$image];
//Get an extension of the uploaded image
$Image_Extension=end(explode('.',$Old_Name));

//Call a stored procedure that inserts a new image data
//into the database
//The procedure returns an ID of the new image as text
$insert_image=mysqli_query(
$db_connect,
"CALL Insert_Image
('$Image_Extension',
$House_ID);");

$image_data=mysqli_fetch_array($insert_image);

//Set a new Image Name to a name returned by Stored Procedure
$Image_Name=$image_data[$New_Image];

mysqli_close($db_connect);
}

}

Here's the Procedure I'm calling from PHP

CREATE PROCEDURE Insert_Image
($File_Extension VARCHAR(6),
$House_ID BIGINT)

BEGIN

DECLARE $Image_ID BIGINT;
DECLARE $Image_Folder VARCHAR(50);
DECLARE $Image_Path VARCHAR(50);
DECLARE $New_Image_ID BIGINT;
DECLARE $New_Image VARCHAR(20);


SELECT max(Image_ID) FROM images INTO $Image_ID;

SET $New_Image_ID=$Image_ID+1;

SET $Image_Folder = ’houses/’;

SET $Image_Path=CONCAT($Image_Folder, $New_Image_ID,’.’, $File_Extension);

INSERT INTO images(Image_Path, House_ID)
VALUES ($Image_Path, $House_ID);

SET $New_Image=CONCAT($New_Image_ID,’.’, $File_Extension);
SELECT $New_Image;

END
//

Er thats why I dont use store procedures, its a lot easier to write PHP to do all the queries and calculations

Quote:

---

Originally Posted by oofki

Er thats why I dont use store procedures, its a lot easier to write PHP to do all the queries and calculations

---

Makes sense, yet there are a few reasons why I prefered SP this time.Nit the least of them are the bandwidth and the speed of execution.

I'm not sure about these quotes

Code:

---

$insert_image=mysqli_query(
$db_connect,
"CALL Insert_Image
('$Image_Extension',
$House_ID);");

---

Try creating the query string in full before using it in mysqli_query. I have a sneaky suspicion you're sending $House_ID literally and not it's value.

Another way may be to use mysqli_prepare($db_connect,"CALL Insert_Image (?,?);"); and then mysqli_stmt_bind_param and mysqli_stmt_execute.

http://nl.php.net/manual/en/function...mt-execute.php

Quote:

---

Originally Posted by SirDice

I'm not sure about these quotes

Code:

---

$insert_image=mysqli_query(
$db_connect,
"CALL Insert_Image
('$Image_Extension',
$House_ID);");

---

Try creating the query string in full before using it in mysqli_query. I have a sneaky suspicion you're sending $House_ID literally and not it's value.

Another way may be to use mysqli_prepare($db_connect,"CALL Insert_Image (?,?);"); and then mysqli_stmt_bind_param and mysqli_stmt_execute.

http://nl.php.net/manual/en/function...mt-execute.php

---

When I call a procedure, it does insert an image data into the table.

The only problem I encountered was that PHP just doesn't read the return value, which I am going to need to rename the image when storing it in the file directory :'(