PHP Stored Procedure Issues

Use PHP to call emp_id this variable, how to write?

Reply to discussion (solution)

 
  Query ("Call Testit ($emp _id)");p Rint_r ($result);? >

You still can't get the value of emp_id.

Of course not! Because the emp_id is an outgoing variable
To be used after the user variable is read

mysql_query ("Call Testit (@a)"), $rs = mysql_query ("select @a");p Rint_r (Mysql_fetch_assoc ($rs));

Data is empty, no data for hairs

mysql_query ("Call Testit (@a)");
$rs = mysql_query ("select @a");
Print_r (MYSQL_FETCH_ASSOC ($rs));
Use this code.

First you have to confirm that your call Testit () can run at the command line ...

Can run, no error

Call Testit (123)

Don't think about it, it's impossible.
When the Php_mysql extension was created, MySQL was still in the version 4 era. and MySQL 4 does not yet support stored procedures
Natural Php_mysql extensions are not designed to consider how to return multiple results of a stored procedure
This is one of the main reasons why the php_mysql extension is going to be phased out.

After entering the MySQL 5 era, it is too difficult to patch on the php_mysql extension, so there is a php_mysqli extension

Here are some tests to observe

$create =<<< sqlcreate PROCEDURE testit (out emp_id int) begin set emp_id=199; Select emp_id as Emps;end; SQL; $drop = ' Drop PROCEDURE IF EXISTS testit ';

mysql_connect (' localhost ', ' root ', '); mysql_select_db (' Test '); mysql_query ($drop); mysql_query ($create); $rs = MySQL _query (' Call Testit (@b) ') or Die (Mysql_error ());p Rint_r (Mysql_fetch_assoc ($rs)), $rs = mysql_query (' select @b ') or Die ( Mysql_error ());p Rint_r (Mysql_fetch_assoc ($rs));

Array (    [emps] = 199) Commands out of sync; can ' t run this command now

 $dsn = ' mysql:host=localhost;dbname=test '; $options = Array (pdo::mysql_attr_init_ COMMAND = "Set names GBK", Pdo::attr_errmode = pdo::errmode_exception, Pdo::attr_default_fetch_mode = Pdo::f  Etch_assoc,); try {$dbh = new PDO ($dsn, ' root ', ', $options);  $DBH->query ($drop);  $DBH->query ($create);  $stmt = $dbh->prepare (' Call Testit (@a) ');//, Array (Pdo::attr_cursor, pdo::cursor_fwdonly));  $pp = 0; $stmt->bindparam (1, $pp, PDO::P aram_int|  PDO::P aram_input_output, 12);  $stmt->execute ();    do {$rows = $stmt->fetchall (PDO::FETCH_ASSOC);    if ($rows) {print_r ($rows);  }} while ($stmt->nextrowset ()); Print_r ($dbh->query (' select @a ')->fetchall ());} catch (Pdoexception $e) {print "error!:". $e->getmessage (). Php_eol;} Print_r ($dbh->query (' select @a ')->fetchall ()); 

Array ([    0] = = Array        (            [Emps] = 199        )) error!: sqlstate[hy000]: General Errorarray (    [0] = = Array        (            [@a] = 199        ))

$db = new mysqli (' localhost ', ' root ', ' ', ' test '), $db->query ($drop); $db->query ($create); $rs = $db->query (' Call Testit (@a) ');p Rint_r ($rs->fetch_assoc ()), $db->next_result (); $rs = $db->query (' select @a ') or Die ($db- >error);p Rint_r ($rs->fetch_assoc ());

Array (    [emps] = 199) Array (    [@a] = 199)

About the creation of stored procedures and the use of out parameters, for example in the MySQL manual

An example of a simple stored procedure that uses an out parameter. For example, when the program is defined, use the MySQL client delimiter command to change the statement delimiter from; This is allowed in the program body; The delimiter is passed to the server instead of being interpreted by MySQL itself. Mysql> delimiter//mysql> CREATE PROCEDURE simpleproc (out param1 INT),    BEGIN   , SELECT COUNT ( *) into param1 from T;    //query OK, END, 0 rows Affected (0.00 sec) mysql> delimiter; mysql> call Simpleproc (@a); Query OK, 0 rows Affected (0.00 sec) mysql> SELECT @a;+------+| @a   |+------+| 3    |+------+1 row in Set (0.00 sec)

Enlightened, thank you moderator

