mySQLi准备的语句无法get_result() [英] mySQLi prepared statement unable to get_result()
问题描述
我对mySQLi完全感到困惑.尽管多年来我一直在使用过程性mysql调用,但我希望习惯于为其提供的db security/mySQL注入保护编写准备好的语句.我正在尝试编写一个简单的select语句(是的,我知道为此进行程序调用可提高性能).运行时,我得到所有回声,直到点击$result = $stmt->get_result();
组件.在我看来,这一切都相当简单,但是在阅读了数小时的mySQLi手册后,我无所适从.任何想法为什么会失败?
I am totally confused by mySQLi. Although I have been using procedural mysql calls for years, I want to get used to making prepared statements for the db security/mySQL injection protection it offers. I am trying to write a simple select statement (yes I know making a procedural call for this offers performance enhancement). When run, I get all the echoes until I hit the $result = $stmt->get_result();
component. It all seems fairly straightforward to me, but I am at a loss after hours of reading manuals on mySQLi. Any ideas why this would be failing?
*注:这是一个测试环境,虽然没有进行字符的清理/转义,但是我只是将有效内容传递给变量$ username和$ email.而且,我从头到尾都在寻找解决问题的方法.
*note: this is a test environment and while no sanitizing/escaping of characters is taking place, I am only passing valid content into the variables $username and $email. And also, I have looked all over SO to find the solution to my issue.
function checkUsernameEmailAvailability($username, $email) {
//Instantiate mysqli connection
@$mysqli = new mysqli(C_HOST,C_USER,C_PASS,C_BASE) or die("Failed to connect to MySQL database...");
if (!$mysqli)
{
echo 'Error: Could not connect to database. Please try again later...';
exit;
} else {
echo 'mysqli created';
}
/* Create a prepared statement */
if($stmt = $mysqli -> prepare("SELECT username,email FROM tb_users WHERE username=? OR email=?")) {
echo '<br />MYSQLi: ';
/* Bind parameters s - string, b - boolean, i - int, etc */
$stmt -> bind_param("ss", $username, $email);
echo '<br />paramsBound...';
/* Execute it */
$stmt -> execute();
echo '<br />Executed';
$result = $stmt->get_result();
echo '<br />Result acquired';
/* now you can fetch the results into an array - NICE */
$myrow = $result->fetch_assoc();
echo '<br />Fetched';
/* Close statement */
/$stmt -> close();
echo '<br />Done mysqli';
}
}
此外,每次调用函数时都必须实例化mysqli吗?我假设它们不是像过程mysql中那样持久的数据库连接.是的,我知道这是一个范围问题,并且不,我无法理解此类变量的作用域.当我在函数外部声明它时,进入函数时不可用.
Also, do I have to instantiate a mysqli every time I call a function? I'm assuming they're not persistent db connects like in procedural mysql. Yes, I am aware this is a scope issue, and no I have not been able to understand the scoping of this class variable. When I declared it outside of the function, it was not available when I came into the function.
更新 如果我将第12行更改为:
UPDATE if I change line 12 from:
if($stmt = $mysqli -> prepare("SELECT username,email FROM tb_users WHERE username=? OR email=?")) {
收件人:
$stmt = $mysqli->stmt_init();
if($stmt = $mysqli -> prepare("SELECT username,email FROM tb_users WHERE username=? OR email=?")) {
if(!stmt) echo 'Statement prepared'; else echo 'Statement NOT prepared';
我得到未准备好的声明.现在我更加困惑了...
I get Statement NOT prepared. Now I'm even more confused....
更新: 我联系了我的托管服务提供商,显然支持mySQLi,并且存在mysqlnd驱动程序.也许有一种方法可以简单地对此进行测试?尽管他们通常在过去给了我很多知识渊博的答案.
UPDATE: I contacted my hosting provider and apparently mySQLi is supported, and the mysqlnd driver is present. Perhaps there is a way to simply test this? Although they usually have given me pretty knowledgeable answers in the past.
更新...再次: 我自己检查了服务器功能,发现虽然存在mysqli和PDO,但没有mysqlnd.因此,我了解了为什么get_result()无法工作(我认为需要mysqlnd),但我仍然不明白为什么预处理语句本身将无法工作.
UPDATE...AGAIN: I checked my server capabilities myself and discovered, while mysqli and PDO are present, mysqlnd is not. Thusly, I understand why get_result() will not work (mysqlnd required I think), I still don't understand why the prepared statement itself will not work.
推荐答案
通过一些检查,即使我的主机上安装了mysqli,显然也不存在mysqlnd驱动程序.因此,不能使用get_result()(PHP手册将get_result定义为mysqlnd依赖),而是需要进行额外的编码以按我希望的方式处理我的结果.
Through some checking, even though mysqli is installed on my host, apparently the mysqlnd driver is not present. Therefore, get_result() can not be used(php manual defines get_result as mysqlnd dependent), and instead extra coding would need to be done to handle my result the way I would like.
因此,我决定尝试学习PDO的工作原理,几分钟之内,瞧!
Therefore, I decided to try and learn how PDO works, and within minutes, voila!!!
将上面的代码替换为:
function checkUsernameEmailAvailability($username, $email) {
echo 'pdo about to be created';
$dsn = 'mysql:dbname='.C_BASE.';host='.C_HOST;
$user = C_USER;
$password = C_PASS;
try {
$dbh = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
echo 'Connection failed: ' . $e->getMessage();
}
$params = array(':username' => $username, ':email' => $email);
try{
$sth = $dbh->prepare('SELECT username,email FROM tb_users WHERE username = :username OR email = :email ');
} catch(PDOException $e) {
echo 'Prepare failed: ' . $e->getMessage();
}
try{
$sth->execute($params);
} catch(PDOException $e) {
echo 'Execute failed: ' . $e->getMessage();
}
$result = $sth->fetch(PDO::FETCH_ASSOC);
print_r($result);
}
尽我所能进行错误检查,并且一开始就完全没有问题!
As much error checking as I could think of, and no problems at all first crack at it!
最终密码
Final Code
function checkUsernameEmailAvailability($username, $email) {
$dsn = 'mysql:dbname='.C_BASE.';host='.C_HOST;
$user = C_USER;
$password = C_PASS;
new PDO($dsn, $user, $password);
$params = array(':username' => $username, ':email' => $email);
$sth = $dbh->prepare('SELECT username,email FROM tb_users WHERE username = :username OR email = :email ');
$sth->execute($params);
$result = $sth->fetch(PDO::FETCH_ASSOC);
print_r($result);
}
这篇关于mySQLi准备的语句无法get_result()的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!