How to use values from one stored procedure into another?

For eg I have a SP which give a result when i, CALL A1(); i fetch the result and store in $a. But when I call another SP CALL A2('$a'); It is not executing. Showing Array() when I run the program


When you compose the SQL command "CALL A2('$a');" in PHP, the parser first expands the $a variable within your double-quoted string literal. However, as documented under Converting to string:

Arrays are always converted to the string "Array"

Therefore PHP interprets the string that is to be sent to MySQL as "CALL AS('Array');", which is obviously the root of your problem. Note also that allowing PHP to expand variables into SQL commands in this fashion is dangerous: see @deceze's blog article The Great Escapism (Or: What You Need To Know To Work With Text Within Text) to understand this better; and then How can I prevent SQL injection in PHP? to understand how variables should (generally) be passed from PHP to SQL.

However, in this case, the fix is not so straightforward. Since MySQL does not have an array datatype, even if you did properly serialise the array then it would only recognise the serial form (which will not be easy to use in your A2 procedure).

It should also be noted that stored procedures don't really "return" anything. Whilst they can output zero or more resultsets, resultsets can't be used as an input to another stored procedure. The normal workaround is to store the resultset (e.g. in a temporary table) and then later access that as required.

That said, it is very rare indeed that such an arrangement is necessary. Usually people attempt such things because they're trying to use SQL as a procedural language, whereas it is a very powerful declarative language. If you explain what it is that your procedures are collectively trying to do, I very much suspect that someone will be able to write a single SQL command to do the whole thing.

Tags: PHP / Mysql / Wordpress

Similar questions

What is the procedure of creating a new Wordpress website when I have already created one
I have wordpress installed on my localhost server running with XAMPP. I have already created one website and now I am have to create another one. What is the procedure of setting up a new website. Do I create a new database on phpmyadmin, as well as a new user? When I installed wordpress for the first time I added a website name and some user crede...
Proper procedure for wp_insert_post?
I just found the magnificent wp_insert_post function and have a few basic question about it. *For reference I used this on a multisite install for a number of blogs running theme_x. I simply wanted to add a page to each blog (without having to do it through WP Admin). This seems to have mostly-worked though I'm not sure I'm using it for its intende...
Inserting comments via a procedure
I've been playing arround with procedures and tried putting the comment insert into a procedure. I've got some trouble and was wondering if you guys could take a look. } So here I only changed the variables on the top because it didn't work out for me with the array that was previously used. I send these variables to: And this is my Storedprocedure...
Directory authentication initially succeeded, but no valid profile was found ("get entries" procedure)
I'm using the wpDirAuth plugin and some users cannot log in: Directory authentication initially succeeded, but no valid profile was found ("get entries" procedure). [([email protected])] The Google results i found were not very helpful. The 3 year old comment from the dev indicates that the LDAP profile is incomplete, but the sys...
Right procedure when you develop a WP Website for a client
i don't know if this procedure could be incorrect while you develop a WP website for a client, but i worked like this for years: Is this procedure "correct"?
Correct procedure for fixing broken WP sites after WP version update
What is the correct procedure for fixing a WP site that has broken due to a recent update to the latest WP version. Assuming there are no backups both locally or via the web host. Is it a case of switching off plugins and switching themes to see if it is restored?

Also ask

We use cookies to deliver the best possible experience on our website. By continuing to use this site, accepting or closing this box, you consent to our use of cookies. To learn more, visit our privacy policy.