PHP - Stored Procedure Problem
Results 1 to 6 of 6

Thread: PHP - Stored Procedure Problem

  1. #1
    Senior Member
    Join Date
    Apr 2004
    Posts
    228

    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);
    }

    }
    Don\'t post if you\'ve got nothing constructive to say. Flooding is annoying

  2. #2
    Senior Member
    Join Date
    Apr 2004
    Posts
    228
    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
    //
    Don\'t post if you\'ve got nothing constructive to say. Flooding is annoying

  3. #3
    Senior Member
    Join Date
    Oct 2003
    Location
    MA
    Posts
    1,053
    Er thats why I dont use store procedures, its a lot easier to write PHP to do all the queries and calculations

  4. #4
    Senior Member
    Join Date
    Apr 2004
    Posts
    228
    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.
    Don\'t post if you\'ve got nothing constructive to say. Flooding is annoying

  5. #5
    Just Another Geek
    Join Date
    Jul 2002
    Location
    Rotterdam, Netherlands
    Posts
    3,403
    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
    Last edited by SirDice; February 22nd, 2008 at 08:15 AM.
    Oliver's Law:
    Experience is something you don't get until just after you need it.

  6. #6
    Senior Member
    Join Date
    Apr 2004
    Posts
    228
    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 :'(
    Don\'t post if you\'ve got nothing constructive to say. Flooding is annoying

Similar Threads

  1. Replies: 0
    Last Post: August 30th, 2007, 04:33 AM
  2. Php MS Exchange Problem
    By Fidato in forum Microsoft Security Discussions
    Replies: 1
    Last Post: August 16th, 2004, 04:11 PM
  3. Shoveling a Shell using PHP Insecurities
    By Irongeek in forum The Security Tutorials Forum
    Replies: 12
    Last Post: August 13th, 2004, 08:25 PM
  4. Hrm.. So PHP Is Your Wanted Fetish eh..
    By er0k in forum Other Tutorials Forum
    Replies: 1
    Last Post: July 4th, 2003, 05:21 PM
  5. basic php with a database
    By johnnymier in forum Other Tutorials Forum
    Replies: 0
    Last Post: June 7th, 2003, 12:56 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 Security News

     Patches

       Security Trends

         How-To

           Buying Guides