PHP对mysql简单调用,[原创]简单的PHP调用MYSQL存储过程的类

delimiter // create procedure listpage (IN num1 int,IN num2 int) begin set @sql = concat('select * from stuinfo limit ',num1,',',num2); //连接的sql语句 prepare sq from @sql; execute sq; deallocate prepare sq; end //

今天我把这个类改了一下。做了点测试和加了点注释放在这里：

/**

* Created by David Yeung 2007-8-31

* An easy way to connect mysql using php

*/

//Define connection constant

define ('MY_HOST','localhost');

define ('MY_USER','webuser');

define ('MY_PASS','********');

define ('MY_DB','test');

define ('MY_PORT','3307');

//For linux add this field

define ('MY_SOCKET','/tmp/mysql_sock');

//Php interface to mysqli

class DB_Mysqli{

private $mysqli;

private $row;

private $count;

private $affected_row_sql;

private $affected_row_sp;

//construct function

public function DB_Mysqli(){

//Check system is nt or linux

if (PHP_OS == 'WINNT'){

$this->mysqli = new mysqli(MY_HOST, MY_USER, MY_PASS, MY_DB, MY_PORT);

}else

{

$this->mysqli = new mysqli(MY_HOST, MY_USER, MY_PASS, MY_DB, MY_PORT,MY_SOCKET);

}

if (mysqli_connect_errno()){

die("Connect failed:".mysqli_connect_error());

}

$this->row = null;

$this->count = 0;

}

//Query sql text

public function call_sql($sql){

$this->mysqli->query($sql);

$this->affected_row_sql = $this->mysqli->affected_rows;

}

//Query store procedure text

public function call_sp($query){

$this->mysqli->multi_query($query);

do {

if ($result = $this->mysqli->use_result()){

while ($row = $result->fetch_row()){

$this->row[$this->count++] = $row;

}

$result->close();

}

} while($this->mysqli->next_result());

$this->affected_row_sp = $this->mysqli->affected_rows;

}

//Close connection and free memory

public function close_sp(){

$this->mysqli->close();

}

//Get query result

public function get_rows(){

return $this->row;

}

//Get rows count

public function get_count(){

return $this->count;

}

//Get affected rows

public function get_affected_rows($str){

switch ($str){

//Get affected rows from sql text

case 'sql':

return $this->affected_row_sql;

break;

//Get affected rows from store procedure text

case 'sp':

return $this->affected_row_sp;

break;

default:

return false;

break;

}

}

}

//New a instant of this DB class

$mysqli = new DB_Mysqli();

$query = 'call sp_test();';

$mysqli->call_sp($query);

$mysqli->close_sp();

print 'DROP PROCEDURE IF EXISTS P_AUTO_SCHEDULING;CREATE PROCEDURE P_AUTO_SCHEDULING(IN v_hosptialId VARCHAR(50))BEGINDECLARE i INT DEFAULT 0;DECLARE v_days INT DEFAULT 1;DECLAREv_uuid VARCHAR(32);DECLARE v_curdate date;DECLAREv_weekTyp

';

print_r($mysqli->get_rows());

print '

';

?>

测试结果：

mysql> delimiter ||

mysql> create procedure sp_test()

-> begin

-> select * from auto_t;

-> end||

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> call sp_test();

+----+---------+

| id | name |

+----+---------+

| 1 | 3 |

| 2 | 5 |

| 3 | sdflsjf |

+----+---------+

3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

PHP 代码:

Array

(

[0] => Array

(

[0] => 1

[1] => 3

)

[1] => Array

(

[0] => 2

[1] => 5

)

[2] => Array

(

[0] => 3

[1] => sdflsjf

)

)