Calling MYSQL stored procedures with OUT parameters

[divyakrishnan]

Hi..
I want to call mysql Stored procedure with OUT parameter.
Its worked in phpamyadmin
But in php it returning empty result.
I have used the following php script.
Can anyone help me?

<?php
include("dbcon.php");
$countryname="india";

  $rs1 = mysql_query( 'CALL countCountry($countryname,@totalcount)' );
      $rs = mysql_query( 'SELECT @totalcount as totalcount' );
      $row = mysql_fetch_array($rs);

      var_dump($rs);

      while($row = mysql_fetch_array($rs))
      {

      echo $row['totalcount'];
      }
 ?>

[pritaeas]

Have you tried just:

$rs = mysql_query( 'SELECT @totalcount' ) or die(mysql_error());

Also note that you are fetching twice. Once is enough.

[divyakrishnan]

Sorry ,its for debugging purpose I fetched twice.

I changed my script as follows

<?php
include("dbcon.php");
$countryname="india";

  $rs1 = mysql_query( 'CALL countCountry($countryname,@totalcount)' );
      //$rs = mysql_query( 'SELECT @totalcount as totalcount' );
      $rs = mysql_query( 'SELECT @totalcount' ) or die(mysql_error());
      while($row = mysql_fetch_array($rs))
      {

      echo $row['totalcount'];
      }
 ?>

Getting a notice like

Notice: Undefined index: totalcount in C:\xampp\htdocs\php\sp\example.php on line 9

[pritaeas]

Try:

$row = mysql_fetch_array($rs);
print_r($row);

[divyakrishnan]

Getting NULL values like

Array ( [0] => [@totalcount] => )

[pritaeas]

Have you tried with MySQLi or PDO?

[Harryjames]

Of course PDO is faster than MySQLi but i am not sure if we can use the same syntax for multiple database drivers!

[pritaeas]

@Harryjames: It's not about speed. It's just that the MySQL extension has a dubious history with stored procedures. Syntax is not an issue either, as he's just using MySQL anyway.

[Taywin]

Hmm.. Just a note... Are you trying to pass PHP variable into the call command -- 'CALL countCountry($countryname,@totalcount)'?

[pritaeas]

@Taywin: Good catch!

$rs1 = mysql_query( "CALL countCountry('$countryname', @totalcount)" );

[divyakrishnan]

Thank you very much
I have changed my script as

    <?php
    include("dbcon.php");
    $countryname="india";
    $rs1 = mysql_query( "CALL countCountry('$countryname', @totalcount)" );
    //$rs = mysql_query( 'SELECT @totalcount as totalcount' );
    $rs = mysql_query( 'SELECT @totalcount as totalcount' ) or die(mysql_error());
    while($row = mysql_fetch_array($rs))
    {
    echo $row['totalcount'];
    }
    ?>

Now its working.