mysqli passing variables to stored procedures

[skygremlin]

 

I’m trying to create a simple web site for people to track issues on a project.  I want it to query a MySQL db and list all issues(for individual pages).  Ironically it’s for our company website creation.  I’m trying to use mysqli and stored procedures for this, and once I get a good handle on that convert the production site to mysqli and stored procedures.

 

One of the issue’s I’m having  (I have more questions I’ll most likely post) is I’m not able to pass the correct website page, as a variable, to the stored procedure.   What I mean is if I manually put the page name as a string in the call, the query seems to run and create a simple table.  If I create a variable with the same string I get an error.  Below is my code and the error.  At first look I’m assuming this might have something to do with how I have the stored procedure setup.  “VARCHAR(255)” and dropping everything after the “.”  But I don’t know that much about stored procedures. Everything I’ve done so far did not require passing in a parameter.

 

More info:

 - The page variable is being populated via a drop down menu, that is created from another query above that gets a list of all pages on the site (stored in another table).  Hope that’s not to confusing..   

 

thanx

 

Working:

$issues = mysqli_query($connection, "call page_issues(\"ser_ms.php\")");

                if (!$issues) {

                    printf("Error: %s\n", mysqli_error($connection));

                    exit();

               }

Table created below with the result set separated out..

 

******************************

 

Not working

$page = "ser_ms.php";

              //$issues = mysqli_query($connection, "call page_issues(\"ser_ms.php\")");

                $issues = mysqli_query($connection, "call page_issues($page)");

                if (!$issues) {

                    printf("Error: %s\n", mysqli_error($connection));

                    exit();

               }

Error I get back is:  Error: Unknown table 'ser_ms' in field list

 

Stored Procedure:

-- --------------------------------------------------------------------------------

-- Routine DDL

-- Note: comments before and after the routine body will not be stored by the server

-- --------------------------------------------------------------------------------

DELIMITER $$


CREATE PROCEDURE `website`.`page_issues` (IN `cur_page` VARCHAR(255))

BEGIN


select * from website_issues where page = cur_page;


END

 

[kicken]

 

$page = "ser_ms.php";
                $issues = mysqli_query($connection, "call page_issues($page)");

 

That generates an invalid query. You would need to quote the $page variable just like you did the raw string:

$issues = mysqli_query($connection, "call page_issues('$page')");

You should run $page through an escape process to make sure any bad characters get escaped.

 

Alternatively, a better method would be to use prepared statements and query parameters. I don't use mysqli so the below is just a best guess at the proper code:

$stmt = mysqli_prepare($connection, 'call page_issues(?)');
mysqli_stmt_bind_param($stmt, 's', $page);

mysqli_stmt_execute($stmt);
mysqli_stmt_store_result($stmt);
$result = mysqli_get_result($stmt);
var_dump(mysqli_fetch_all($result));

[skygremlin]

That seemed to work...  I thought I tried that syntax, but in my attempts to figure this out I must have overlooked something......    I changed the variable to a couple different pages and they all seemed to display the table correctly.

 

Thank you for the suggestion..  From what I've read I should be using the prepared statements, but seeing as this for internal people only 10 or so - running on a internal server (not for outside world) - I'm learning as I go - and this really should have been done yesterday.  ...  If I can get it working I'll be happy.  

 

I do have another issue though, I'll start a new thread.. I'm running 2 queries on this page, one at page load to populate the drop down menu, then this one..  I'm getting an out of sync error, which is what I think is because i'm not clearing the buffer..  But my mysqli_free_memory isn't working...  Probably another syntax issue or I have it in the wrong place....

 

thanx again for the quick reply and advice..