Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource

mohsini

hi all

I keep getting this error

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result
resource in

my code is below

function getALLRows(){
$qry=mysql_query("CALL getSubs");

	return ($row=mysql_fetch_assoc($qry));
}

laserlight

That warning is due to one or more of these:

Failed to connect to database server.
Failed to select database.
Failed to execute corresponding SQL statement, e.g., due to a syntax error.

mohsini

Hi thanks for the quick reply,

I checked by callign a dummy test function, it works

The database is selected hence i can call the dummy function

I executed the stored procedure in mysql, it worked and gave me 10 rows.....but when i do it from php it doesnt....... so where is the problem

laserlight

Do some debugging then:

function getALLRows() {
    $query = mysql_query("CALL getSubs")
        or die(mysql_error());

$all_rows = array();
while ($row = mysql_fetch_assoc($qry))
{
    $all_rows[] = $row;
}
return $all_rows;
}

Incidentally, why are you using the MySQL extension instead of the MySQLi extension or the PDO extension?

mohsini

Hi there,
thanx a lot. for your assistance, im actually a new php programmer this was my next question..what the diffeernce between mysql and mysqli........what should i use...

ok now for this problem, i will write your code and test again......let me see..i will post back if i get a problem

mohsini

Hi laser,
I just wrote your code and this is the error,

PROCEDURE myDB.getSubs can't return a result set in the given context

laserlight

what the diffeernce between mysql and mysqli........what should i use...

The MySQLi extension, like the PDO extension, provides an object oriented interface with support for prepared statements and other features available in MySQL 4.1 and above. As such, I suggest that you use the MySQLi extension or PDO extension unless you have no choice except to use the MySQL extension.

ok now for this problem, i will write your code and test again......let me see..i will post back if i get a problem

Yep. The mysql_error() will give a more detailed error message that may hint what is the problem.

EDIT:

I just wrote your code and this is the error,

PROCEDURE myDB.getSubs can't return a result set in the given context

hmm... but when you tried it in the mysql command line it worked.

I have never had a reason to use stored procedures, so I am not sure how to diagnose the problem. One possibility is that the MySQL extension does not allow you to call stored procedures. Another possibility is that if your stored procedure has more than one SQL statement, this causes problems since mysql_query() is designed to only execute one SQL statement at a time. If this is the case, then multi_query() from the MySQLi extension might be a fix. Another possible fix would then be to use the PDO extension, possibly with the PDOStatement::nextRowset().

mohsini

Hi laser,
I think ur a php GURU, i just started learning php last week. U r talking in a very hi language which is goign above my head, but i think what u r saying is the solution to me. So I want to know what is mysqli, and what is PDO.

Secondly, i think i came very close to the problem of my stored procedure. U see my stored procedure is havingselect statement and it gives 10 rows. Now i wa reading mysql manual and it says that u cannot use select query inside stored procedure/funtion. thats the reason my stored procedure is notbeing executed and its throwing the error.

plz correct me if im wrong

djheru

From the php manual:

ou can only return variables by reference, not the result of a statement.

I do this:

function getALLRows(){
$qry=mysql_query("CALL getSubs");
$row=mysql_fetch_assoc($qry)
return ($row);
}

bradgrafelman

Are you testing the command from the MySQL command line using the same user that PHP is connecting to the MySQL database with? Calling stored procedure is a privilege that the user may not have - make sure you're testing with the same user account.
As laserlight pointed out, you really should look into using more advance libraries to talk with the MySQL server, such as the [man]MySQLi[/man] library.

You may be interested in this comment posted to the MySQL manual:

Graham Jordan on November 16 2006 1:17pm wrote:
HERE IT IS:
For all of you getting "can't return a result set in the given context" errors when using PHP to execute stored procedures,
the mysql_connect flag is:

mysql_connect( host, databaseuser,password,TRUE, 131074)

Worked with mysql 5.0.20 and PHP 5.1.4
Also, stored procedures seem to close the connection when they've finished running in PHP.
Can be fixed using mysql_ping( db_resource_id ) to reinstate lost connections

Long story short: don't use the mysql_*() functions - they're deprecated and don't support newer functionality, such as what you're trying to do. Look into installing/using at least the [man]MySQLi[/man] functions.