How php calls the mysql stored procedure

Source: Internet
Author: User
Mysql php

Why is it wrong after I call a stored procedure and perform other queries?
I want php to call the mysql stored procedure to query all the data in the table and get the result normally.
When other queries are performed, no matter what statements are used, an error is returned.

This is the stored procedure:
# Querying data in the shares table
# Call proc_show_shares ('sendtime'); // sort object
Delimiter //
Drop procedure if exists proc_show_shares //
Create procedure proc_show_shares (in sorttype varchar (15 ))
Set @ SQL = concat ("select * from shares order by", sorttype, "desc ");
PREPARE cmd from @ SQL;
END //

The following code runs normally:
$ Query = "call proc_show_shares ('sendtime ')";
$ Results = mysql_query ($ query); // call the stored procedure
While ($ result_row = mysql_fetch_row ($ results ))
// {Code segment}

$ Values [] = $ result_row;
{Code segment} is commented out. an error occurs when the comment is canceled.
The code segment is:
$ Result = mysql_query ("select name from users where id = 10001") or die (' ');
The preceding SQL statement runs normally independently (of course there are results), and when it is connected, the output is 'depend on.

I can output values, but I can add mysql_query ("select name from users where id = 10001") or die ('depend on '); that doesn't work. However, this statement is correct.

The database remains in the connection status during all the processes.

Reply to discussion (solution)

This is related to the difference between the data structure returned by the stored procedure and the normal query.
The normal query returns a single resource, while the execution stored procedure returns a resource array (inaccurate, but meaning)
Just as you cannot use the same method to process simple variables and array variables, different methods should be used to process different result sets.
However, the php database operation function does not do this, so you encounter problems (not just you)
The solution is simple: Read the empty result set first, and then execute other queries.

This is also true: Since you have used the stored procedure, you should continue to include other queries in the stored procedure.
It shouldn't be handled by him separately.

Mysql_query ("select name from users where id = 10001") is called in the stored procedure at the beginning, but cannot be read. Therefore, the stored procedure is abandoned, directly using SQL statements does not work. It seems that the call to the stored procedure used for the first time has a great impact on the subsequent query. The previous stored procedure can be called as long as it is changed to an SQL statement.

In mysql, can triggers, stored procedures, and SQL statements be called consecutively (without recursion ).

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.