mysqli这个库还是比较繁杂的,这其中又分mysqli ,mysqli_stmt,mysqli_result......一堆类,特别乱
这里奉上thinkphp5.1中使用mysqli扩展的查询用法,以飨诸君
$mysqli = new \mysqli("127.0.0.1","root","1234567","testdb");
$sql = "select * from user_auth_v where userid = ?";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param('s',$userid);
$stmt->execute();
$ret=$stmt->get_result();
var_dump($ret); //返回结果:object(mysqli_result)#92 (5) { ["current_field"]=> int(0) ["field_count"]=> int(14) ["lengths"]=> NULL ["num_rows"]=> int(7) ["type"]=> int(0) }
echo "结果集长度:".$ret->num_rows;//结果集长度:7
for ($i=1;$i<=$ret->num_rows;$i++)
{
echo "<br>";
var_dump(mysqli_fetch_assoc($ret));
}
由于mysqli不是thinkphp5.1的内置类型,所以引用时要添加斜线:
$mysqli = new \mysqli("127.0.0.1","root","1234567","testdb");
$ret循环返回结果形如:
array(14) { ["userid"]=> string(2) "cj" ["username"]=> string(6) "程劲" ["ugid"]=> int(3) ["roleid"]=> int(2) ["usergroupid"]=> int(6) ["description"]=> string(24) "查询商品入库信息" ["fullpath"]=> string(28) "logistic/Logistic/generalqry" ["authid"]=> int(4) ["ismenu"]=> int(0) ["link"]=> string(26) "generalqry/:tblname/:param" ["menulevel"]=> int(0) ["role"]=> int(2) ["saiwa"]=> int(2) ["authno"]=> int(201) }
array(14) { ["userid"]=> string(2) "cj" ["username"]=> string(6) "程劲" ["ugid"]=> int(3) ["roleid"]=> int(2) ["usergroupid"]=> int(6) ["description"]=> string(21) "查询入库单明细" ["fullpath"]=> string(25) "logistic/Logistic/qryrkmx" ["authid"]=> int(5) ["ismenu"]=> int(0) ["link"]=> string(14) "qryrkmx/:param" ["menulevel"]=> int(0) ["role"]=> int(2) ["saiwa"]=> int(2) ["authno"]=> int(20200) }
array(14) { ["userid"]=> string(2) "cj" ["username"]=> string(6) "程劲" ["ugid"]=> int(3) ["roleid"]=> int(2) ["usergroupid"]=> int(6) ["description"]=> string(27) "添加入库单单头信息" ["fullpath"]=> string(26) "logistic/Logistic/addrkdoc" ["authid"]=> int(6) ["ismenu"]=> int(0) ["link"]=> string(7) "/rk/add" ["menulevel"]=> int(0) ["role"]=> int(2) ["saiwa"]=> int(2) ["authno"]=> int(20102) }
array(14) { ["userid"]=> string(2) "cj" ["username"]=> string(6) "程劲" ["ugid"]=> int(3) ["roleid"]=> int(2) ["usergroupid"]=> int(6) ["description"]=> string(27) "更新入库单单头信息" ["fullpath"]=> string(26) "logistic/Logistic/updrkdoc" ["authid"]=> int(7) ["ismenu"]=> int(0) ["link"]=> string(10) "/rk/update" ["menulevel"]=> int(0) ["role"]=> int(2) ["saiwa"]=> int(2) ["authno"]=> int(20103) }
array(14) { ["userid"]=> string(2) "cj" ["username"]=> string(6) "程劲" ["ugid"]=> int(3) ["roleid"]=> int(2) ["usergroupid"]=> int(6) ["description"]=> string(18) "新增入库明细" ["fullpath"]=> string(25) "logistic/Logistic/addrkmx" ["authid"]=> int(8) ["ismenu"]=> int(0) ["link"]=> string(9) "/rkmx/add" ["menulevel"]=> int(0) ["role"]=> int(2) ["saiwa"]=> int(2) ["authno"]=> int(20201) }
array(14) { ["userid"]=> string(2) "cj" ["username"]=> string(6) "程劲" ["ugid"]=> int(3) ["roleid"]=> int(2) ["usergroupid"]=> int(6) ["description"]=> string(18) "更新入库明细" ["fullpath"]=> string(28) "logistic/Logistic/updaterkmx" ["authid"]=> int(9) ["ismenu"]=> int(0) ["link"]=> string(16) "/rkmx/update/:id" ["menulevel"]=> int(0) ["role"]=> int(2) ["saiwa"]=> int(2) ["authno"]=> int(20202) }
array(14) { ["userid"]=> string(2) "cj" ["username"]=> string(6) "程劲" ["ugid"]=> int(3) ["roleid"]=> int(2) ["usergroupid"]=> int(6) ["description"]=> string(18) "商品新增入库" ["fullpath"]=> string(28) "logistic/Logistic/loadlrkdoc" ["authid"]=> int(11) ["ismenu"]=> int(1) ["link"]=> string(10) "loadlrkdoc" ["menulevel"]=> int(0) ["role"]=> int(2) ["saiwa"]=> int(2) ["authno"]=> int(2) }
或者是:
$conn = new \mysqli('127.0.0.1','root','1234567','testdb');
$sql = "select authid from user_auth_v where userid = ?";
$stmt = mysqli_stmt_init($conn);
$sentence = mysqli_stmt_prepare($stmt,$sql);
$stmt->bind_param('s',$userid);
$stmt->execute();
$ret = $stmt->get_result();
echo "结果集长度:".$ret->num_rows;
for ($i=1;$i<=$ret->num_rows;$i++)
{
echo "<br>";
var_dump(mysqli_fetch_assoc($ret));
}
- 全mysqli_stmt模式
$conn = new mysqli('127.0.0.1','root','1234567','testdb');
$stmt = mysqli_stmt_init($conn);
$sql = "select * from user_auth_v where link = ? and userid = ?";
$bindret = mysqli_stmt_prepare($stmt,$sql);
$link = 'gd/list';
$userid = 'cpc';
if ($bindret)
{
mysqli_stmt_bind_param($stmt,'ss',$link,$userid);
mysqli_stmt_execute($stmt);
$rows = mysqli_stmt_get_result($stmt);
var_dump($rows->num_rows);
}
这里只返回结果(数组的长度)
- 以下返回关联数组
$conn = new mysqli('127.0.0.1','root','1234567','testdb');
$stmt = mysqli_stmt_init($conn);
$sql = "select * from user_auth_v where userid = ?";
$bindret = mysqli_stmt_prepare($stmt,$sql);
//$link = 'gd/list';
$userid = 'cpc';
if ($bindret)
{
mysqli_stmt_bind_param($stmt,'s',$userid);
mysqli_stmt_execute($stmt);
$rows = mysqli_stmt_get_result($stmt);
var_dump($rows->num_rows);
while ($row=$rows->fetch_array(MYSQLI_ASSOC))
{
var_dump($row);
echo "<br>";
}
}