我是想向存储过程里传入一个商品ID号,从而在商品表里获得该商品所有信息。MYSQL的存储过程我是这样写的:
CREATE PROCEDURE shop_getById(in id int)
BEGIN
select shop.* from shop where shop.id = id;
END;在MYSQL里call getById(10);
能获得10号商品的各个字段数据。我通过PHP来取就出问题了。取不出值。都是空的。<?php
include_once 'admin/conn.php'; //数据库连接$res = mysql_query("set @id=10", $con);
$res = mysql_query("call shop_getById(@id)", $con);
$row = mysql_fetch_row($res);
echo "商品信息:" . $row[0] . "," . $row[1] . "," . $row[2];
?>请问如何取,谢谢。
CREATE PROCEDURE shop_getById(in id int)
BEGIN
select shop.* from shop where shop.id = id;
END;在MYSQL里call getById(10);
能获得10号商品的各个字段数据。我通过PHP来取就出问题了。取不出值。都是空的。<?php
include_once 'admin/conn.php'; //数据库连接$res = mysql_query("set @id=10", $con);
$res = mysql_query("call shop_getById(@id)", $con);
$row = mysql_fetch_row($res);
echo "商品信息:" . $row[0] . "," . $row[1] . "," . $row[2];
?>请问如何取,谢谢。
$res = mysql_query("call shop_getById(@id)", $con);
whlie($row = mysql_fetch_assoc($res)){
$arr[]=$row;
}print_r($arr);
$res = mysql_query("set @id=10", $con);
$res = mysql_query("call shop_getById(@id)", $con);
while($row = mysql_fetch_assoc($res)){
$arr[]=$row;
}print_r($arr);
echo $arr[0][1];
<?php
include_once 'admin/conn.php';$res = mysql_query("set @id=10", $con);
$res = mysql_query("call shop_getById(@id)", $con);
while ($row = mysql_fetch_assoc($res)) {
$arr[]=$row;
}
echo $arr[0][1];
?>
$res = mysql_query("set @id=10", $con);
$res = mysql_query("call shop_getById(@id)", $con);
while($row = mysql_fetch_assoc($res)){
$arr[]=$row;
}print_r($arr);
echo $arr[0][1];
mysql_connect($host,$user,$password,true,131072)
PHP 5.0 以上的 mysqli 系列函数可以支持操作 MySQL 的存储过程。
以下是一些简单的存储过程和用 PHP 调用的示例。 一、返回单个数据: 1: <?php 2: header("Content-Type:text/html;charset=utf-8"); 3: 4: $host = "localhost"; 5: $user = "root"; 6: $password = "mypassword"; 7: $db = "test_store_proc"; 8: $dblink = mysqli_connect($host, $user, $password, $db) or die("can't connect to mysql"); 9: 10: $dblink->query('SET NAMES UTF8');11: if ($result = $dblink->query("CALL sp_test0(@num, @x, 123)"))12: {13: $rs = $dblink->query("select @num");14: $row = $rs->fetch_array();15: echo $row['@num'], '<br>';16: 17: $rs = $dblink->query("select @x");18: $row = $rs->fetch_array();19: echo $row['@x'];20: 21: mysqli_free_result($rs);22: }23: else24: echo 'error...';25: mysqli_close($dblink);26: 27: /*28: -- Procedure "sp_test0" DDL29: CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_test0`(OUT num INT, OUT x VARCHAR(16), IN n INT)30: BEGIN31: DECLARE nouse int;32: DECLARE tmp int;33: 34: SELECT nId INTO nouse FROM open_news WHERE nID=39;35: SELECT count(*) INTO tmp FROM open_news;36: SET num = tmp;37: 38: SET x = 'XXX';39: END;40: */41: ?>42: 二、返回结果集: 1: <?php 2: header("Content-Type:text/html;charset=utf-8"); 3: 4: $host = "localhost"; 5: $user = "root"; 6: $password = "mypassword"; 7: $db = "test_store_proc"; 8: $dblink = mysqli_connect($host, $user, $password, $db) or die("can't connect to mysql"); 9: 10: $dblink->query('SET NAMES UTF8');11: if ($result = $dblink->query("call sp_test1()"))12: {13: while( $row = $result->fetch_array())14: {15: echo ($row['nId']. "--" . $row['sTopic2'] . "<br>");16: }17: mysqli_free_result($result);18: }19: else20: echo 'error...';21: mysqli_close($dblink);22: 23: /*24: -- Procedure "sp_test1" DDL25: CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_test1`()26: BEGIN27: SELECT * FROM open_news WHERE nId<40;28: END;29: */30: ?>31: 三、返回多个结果集: 1: <?php 2: header("Content-Type:text/html;charset=utf-8"); 3: 4: $host = "localhost"; 5: $user = "root"; 6: $password = "mypassword"; 7: $db = "test_store_proc"; 8: 9: $dblink = new mysqli($host, $user, $password, $db);10: if (mysqli_connect_errno())11: {12: print('Can not connect to MySQL server');13: exit;14: }15: else16: print('?????? MySQL ????????<br>');17: 18: $dblink->query('SET NAMES UTF8');19: $rows = array();20: if($dblink->real_query("CALL sp_test2()"))21: {22: do23: {24: if($result = $dblink->store_result())25: {26: while ($row = $result->fetch_assoc())27: {28: array_push($rows, $row);29: }30: $result->close();31: }32: }33: while($dblink->next_result());34: }35: else36: echo 'error...';37: 38: $dblink->close();39: 40: print_r($rows);41: /*42: -- Procedure "sp_test2" DDL43: CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_test2`()44: BEGIN45: SELECT nId,sTopic2 FROM open_news LIMIT 0, 5;46: SELECT count(nId) AS counter FROM open_news;47: END;48: */49: ?>50:
关键就是两点
-->1 define(‘CLIENT_MULTI_RESULTS‘, 131072);
2
3 $link = mysql_connect(“127.0.0.1“, “root“, “”,1,CLIENT_MULTI_RESULTS) or die(“Could not connect: “.mysql_error());下面就可以正常使用了,以下是例子程序。
1 <?php
2 define(‘CLIENT_MULTI_RESULTS‘, 131072);
3
4 $link = mysql_connect(“127.0.0.1“, “root“, “”,1,CLIENT_MULTI_RESULTS) or die(“Could not connect: “.mysql_error());
5 mysql_select_db(“vs“) or die(“Could not select database“);
6 ?>
7
8 <?php
9 $result = mysql_query(“call get_news_from_class_id(2)“) or die(“Query failed:“ .mysql_error());
10 while($row = mysql_fetch_array($result, MYSQL_ASSOC))
11 {
12 $line = ‘<tr><td><a target = _blank href=\”.$row["url"].‘\‘>‘.$row["title"].‘(‘.$row["page_time"].‘)‘.‘</a></td></tr>‘;
14 echo $line;
15 printf(“\n“);
16
17 }
18 mysql_free_result($result);
19 ?>
20
21 <?php
22 mysql_close($link);
23 ?>
php调用存储过程返回结果集,解决can’t return a result set in the given context错误的方法
需要php调用存储过程,返回一个结果集,发现很困难,找了半天,终于在老外的论坛上找到解决方案,这里本地化一下。关键就是两点1)define(‘CLIENT_MULTI_RESULTS’, 131072);2)$link = mysql_connect(“127.0.0.1″, “root”, “”,1,CLIENT_MULTI_RESULTS) or die(“Could not connect: “.mysql_error());
下面就可以正常使用了,以下是例子程序。 <?php
define(‘CLIENT_MULTI_RESULTS’, 131072); $link = mysql_connect(“127.0.0.1″, “root”, “”,1,CLIENT_MULTI_RESULTS) or die(“Could not connect: “.mysql_error());
mysql_select_db(“vs”) or die(“Could not select database”);
?> <?php
$result = mysql_query(“call get_news_from_class_id(2)”) or die(“Query failed:” .mysql_error());
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
$line = ‘<tr><td><a target = _blank href=\”.$row["url"].’\'>’.$row["title"].’(‘.$row["page_time"].’)’.’</a></td></t
r>’;
echo $line;
printf(“\n”); }
mysql_free_result($result);
?> <?php
mysql_close($link);
?>