php - Error after calling mysql stored procedures -

php - Error after calling mysql stored procedures -

sorry english language , sorry if message composed wrong - first question.

i had lost 2 evenings when tried resolve 1 interesting problem mysql disconnecting, after procedure call. it's going more interesting, when i'll say, problem select queries in procedures.

so, example. have 2 classes , procedure:

1) dbconn - connection , handle queries.

class dbconn { private $mysqlc = null; public function __construct() { $this->createconnection(); } public function __destruct() { //$this->mysqlc->close(); } private function createconnection() { $mc = new mysqli("localhost", "root", "root", "root"); if ($mc->connect_error) die('bye. '.$mc->connect_errno."-".$mc->connect_error); else $mc->set_charset("utf8"); $this->mysqlc = $mc; } private function closeconnection() { $this->mysqlc->close(); } private function _error() { die('bye. '.$this->mysqlc->connect_errno."-".$this->mysqlc->connect_error); } public function setdata($call, $types = null, $params = null) { //$this->createconnection(); $stmt = $this->mysqlc->stmt_init(); if ($stmt->prepare($call) === false) $this->_error(); if ($params && call_user_func_array(array($stmt, "bind_param"), array_merge(array($types), $params)) === false) $this->_error(); if ($stmt->execute() === false) $this->_error(); $insid = $stmt->insert_id; $affrows = $stmt->affected_rows; $stmt->close(); //$this->closeconnection(); homecoming array($insid, $affrows); } public function getdata($call, $types = null, $params = null) { //$this->createconnection(); //#look here begin print 'status = '.$this->mysqlc->ping(); //#look here end //print $call; $stmt = $this->mysqlc->stmt_init(); if ($stmt->prepare($call) === false) $this->_error(); if ($params && call_user_func_array(array($stmt, "bind_param"), array_merge(array($types), $params)) === false) $this->_error(); if ($stmt->execute() === false) $this->_error(); if ($stmt->store_result() === false) $this->_error(); $meta = $stmt->result_metadata(); while ($field = $meta->fetch_field()) $var[] = &$row[$field->name]; call_user_func_array(array($stmt, 'bind_result'), $var); $arr = null; while ($stmt->fetch()) { foreach($row $key => $val) $c[$key] = $val; $arr[] = $c; } $stmt->close(); //$this->closeconnection(); homecoming $arr; } }

2) baseactions - creates dbconn object , sends text commands it.

class baseactions { private $conn = null; public function __construct() { $this->conn = new dbconn(); } private function checkpassword($email = '', $pass = '') { //#look here begin $arr = $this->conn->getdata("call login_actions(-1, '$email', '', '$pass', '');"); $arr = $this->conn->getdata("call login_actions(-1, '$email', '', '$pass', '');"); //#look here end homecoming ($arr[0]['istrue']==1 ? true : false); } private function updatesession($email) { if (!session_regenerate_id()) homecoming false; $session = session_id(); $this->conn->setdata( "call login_session(2, ?, ?)", "ss", array(&$email, &$session) ); homecoming true; } public function loginuser($email = '', $pass = '') { if (!$this->updatesession($email)) homecoming false; if (!$this->checkpassword($email, $pass)) homecoming false; homecoming true; } }

3) stored procedure

create definer=`root`@`localhost` procedure `login_actions`( _action int, _vcemail varchar(50), _vcnickname varchar(20), _vcpassword varchar(255), _vcpasssalt varchar(10) ) begin case _action when -1 select md5(concat(md5(_vcpassword), vcpasssalt)) = vcpassword 'istrue' login vcemail=_vcemail; when 0 select iid, vcemail, vcnickname login; when 1 insert login(vcemail, vcnickname, dtdateadd, vcpassword, vcpasssalt) values(_vcemail, _vcnickname, utc_timestamp(), md5(concat(md5(_vcpassword), _vcpasssalt)), _vcpasssalt); end case; end

well... i've marked 2 blocks in these code '//#look here' - please, find before.

if implement next code...

$base = new baseactions(); $base->loginuser("mail@mail.com", "mypassword");

...page homecoming you

status = 1 status = bye. 0-

but if alter "call login_actions(-1, '$email', '', '$pass', '');" on query case procedure these parameters "select md5(concat(md5($pass), vcpasssalt)) = vcpassword 'istrue' login vcemail=$email;", you'll ok result.

status = 1 status = 1

i can't understand - why mysql connection close everytime after procedure select? there no problems insert in procerude. please, help - i'm tearing hairs.

upd: error occurs in "if ($stmt->prepare($call) === false) $this->_error();" of getdata method. first implement of ok, rest bad.

i found solution. is't pretty, works , helps solve problem calling of many procedures in 1 connection.

i need add together line in getdata method.

$stmt->close(); while(mysqli_more_results($this->mysqlc)) //<<<<---- line mysqli_next_result($this->mysqlc); //<<<<---- line homecoming $arr;

so, final class is:

class dbconn { private $mysqlc = null; public function __construct() { $mc = new mysqli("localhost", "user", "password", "database"); if ($mc->connect_error) $this->error("bye. ", $mc->connect_errno, $mc->connect_error); else $mc->set_charset("utf8"); $this->mysqlc = $mc; } public function __destruct() { $this->mysqlc->close(); } private function isconnected() { homecoming $this->mysqlc->ping(); } private function error($msg = '', $errno = 0, $error = '') { die("bye. {$msg} ". ($errno != 0 ? "errno: {$errno} - {$error}" : "errno: {$this->mysqlc->errno} - {$this->mysqlc->error}")); } public function setdata($call, $types = null, $params = null) { $stmt = $this->mysqlc->stmt_init(); if ($stmt->prepare($call) === false) { $this->error("", $stmt->errno, $stmt->error); } if ($params) { $result = call_user_func_array(array($stmt, "bind_param"), array_merge(array($types), $params)); if ($result === false) { $this->error("", $stmt->errno, $stmt->error); } } if ($stmt->execute() === false) { $this->error("", $stmt->errno, $stmt->error); } $insid = $stmt->insert_id; $affrows = $stmt->affected_rows; $stmt->close(); homecoming array($insid, $affrows); } public function getdata($call, $types = null, $params = null) { $stmt = $this->mysqlc->stmt_init(); if ($stmt->prepare($call) === false) { $this->error("", $stmt->errno, $stmt->error); } if ($params) { $result = call_user_func_array(array($stmt, "bind_param"), array_merge(array($types), $params)); if ($result === false) { $this->error("", $stmt->errno, $stmt->error); } } if ($stmt->execute() === false) { $this->error("", $stmt->errno, $stmt->error); } $result = $stmt->store_result(); if ( $result === false && !empty($stmt->error) ) { // failing!!! , throw away result $this->error("", $stmt->errno, $stmt->error); } $meta = $stmt->result_metadata(); while ($field = $meta->fetch_field()) { $var[] = &$row[$field->name]; } call_user_func_array(array($stmt, 'bind_result'), $var); $arr = null; while ($stmt->fetch()) { foreach($row $key => $val) $c[$key] = $val; $arr[] = $c; } $stmt->close(); while(mysqli_more_results($this->mysqlc)) //<<<<---- line mysqli_next_result($this->mysqlc); //<<<<---- line homecoming $arr; } }

thank all, geeks!

php mysql call procedure disconnected