listing records using stored procedures

Question

I'm having a little practice regarding mysql stored procedures. Here is the php file which calls it:

<?php
include('conn.php');
$cor=$_POST['course'];
$sql="CALL list('$cor')";
$result=mysql_query($sql);



?>

<table>
<tr>
<th>Name</th>
<th>Course</th>
</tr>

<?php 
while($row=mysql_fetch_assoc($result)){
?>
<tr>
<td><?php echo $row['Name']; ?></td>
<td><?php echo $row['Course']; ?></td>
</tr>

<?php } ?>

</table>

And here's the stored procedure, which works well when I execute it in heidi sql:

CREATE PROCEDURE `list`(IN `COR` VARCHAR(50))
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
SELECT * 

FROM tb1 
WHERE Course=COR;

END

Please tell me how do I debug this one, I'm having this kind of error:

mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in

I suspect my query is at fault. please help.

Answer 1

Let MySQL tell you what the problem was

$result=mysql_query($sql);
if ( !$result ) {
  echo 'an error occurred: ', htmlspecialchars(mysql_error()), 
    '<br />The query was: ', htmlspecialchars($sql);
  die;
}

see http://docs.php.net/mysql_error

Answer 2

It seems ext/mysql makes using stored procedures a cumbersome (some say impossible) thing to do. Using ext/mysqli is recommended (I'm personally using mysqli, and indeed it works fine with stored procedures)

See this topic on MySQL forums: http://forums.mysql.com/read.php?52,59458,185092#msg-185092