Jump to content

mysqli passing variables to stored procedures

Go to solution Solved by skygremlin,

Recommended Posts


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..   





$issues = mysqli_query($connection, "call page_issues(\"ser_ms.php\")");
if (!$issues) {
printf("Error: %s\n", mysqli_error($connection));

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

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
-- --------------------------------------------------------------------------------

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

select * from website_issues where page = cur_page;



Link to comment
Share on other sites


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

$result = mysqli_get_result($stmt);
Link to comment
Share on other sites

  • Solution

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..

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.