Strange problem with mysql procedures and PHP database connection

Question

I have two completely separate queries that were transferred over to procedures. When they were ad-hoc queries, they ran great but now that I have stored them, things have gotten strange.

PHP calls a class method which returns the values of one of the procs. The second method is then called and fails. When I run the run the proc that PHP fails to give me, directly from the command line, it gives me the results just fine. This is certainly strange.

Can someone tell me if I'm missing something? I'd be happy to post some code but in the interest of time, I figured I would ask and see if this is a common problem first.

---

This is the function that connects;

function connect()
{
    $conn = mysql_connect($host, $user, $pass);
    if(!$conn){
        echo 'Error: cannot connect';
    }else{
        $selectDB();
    }
}

---

This is the proc that is failing:

BEGIN
SELECT name FROM source ORDER BY name ASC;
END

This is the first proc that returns the results.

BEGIN
SELECT cpw, cpw_id FROM cpw ORDER BY cpw ASC;
END

Answer 1

Without first seeing code, it's difficult to say. I can assure you that this isn't normal behavior though. From your comment on the OP, it would seem that your user-credentials are failing after the first query. I would check to be sure that nothing is overwriting them, or modifying them in anyway between requests.

At this point, it would be helpful to see the piece of code that makes the connection, and calls for the procs.

Answer 2

Seeing the code will allow for more specific help, but have you checked already for variable name conflicts in the procs that could be overwriting your connection credentials?

Answer 3

In case the function connect() you've posted is real code (and not an abbreviated version for SO) you have a "problem" with PHP's variable scope (and lifetime). $host, $user and $pass are unknown within the function. I.e. effectively mysql_connect(null, null, null);is executed and the php-mysql extension tries to establish the default connection accrding to the ini parameters mysql.default_host, mysql.default_user and mysql.default_password.

If you set the error reporting to E_ALL ( e.g. via error_reporting(E_ALL) prior to the function call) you should get some "undefined variable" messages.