我正在尝试将旧的Mysqli查询转换为mysqli准备语句，但我似乎无法弄清楚如何将其作为mysqli准备语句工作 - 请参阅下面的代码。我的原始查询工作正常，请参见下文。
<?php
// DB Settings
define('DB_SERVER', 'localhost');
define('DB_USER', 'xxxx');
define('DB_PASSWORD', 'xxxx');
define('DB_NAME', 'xxxx');
$mysqli = new mysqli(DB_SERVER, DB_USER, DB_PASSWORD, DB_NAME);
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s'n", mysqli_connect_error());
exit();
}
$name = "Smith"; //Smith is Master 6 times and once as Junior Stewart Total 7 rows in my database
$search_query = '%'.$name.'%';
//Original Query - this works fine
$sql = "SELECT Master, `Junior Steward` AS `Junior_Steward` FROM past_officers WHERE `Master` LIKE
'$search_query' OR `Junior Steward` LIKE '$search_query' ";
$stmt = mysqli_stmt_init($mysqli);
if (mysqli_stmt_prepare($stmt, $sql)) {
mysqli_stmt_bind_param($stmt, "s", $search_query);
mysqli_stmt_bind_result($stmt, $Master, $Junior_Steward);
mysqli_stmt_execute($stmt);
mysqli_stmt_store_result($stmt);
$count = mysqli_stmt_num_rows($stmt);
printf("Number of rows: %d.'n", mysqli_stmt_num_rows($stmt));//Prints number of rows
if ($count == 0) {
echo $output = "<p>There was no search results</p>"; //asign to variable $output message "There was no search results"
}
while (mysqli_stmt_fetch($stmt)) {
echo "<p>Master: $Master Junior Steward: $Junior_Steward</p>";
}
}
/* Prepared Statements Binds two variables to a prepared statement as parameters*/
//This doesn't work
$sql = "SELECT Master, `Junior Steward` AS `Junior_Steward` FROM past_officers WHERE `Master` LIKE ? OR `Junior
Steward` LIKE ?";
$stmt = mysqli_stmt_init($mysqli);
if (mysqli_stmt_prepare($stmt, $sql)) {
mysqli_stmt_bind_param($stmt, "s", $search_query);
mysqli_stmt_bind_result($stmt, $Master, $Junior_Steward);
mysqli_stmt_execute($stmt);
mysqli_stmt_store_result($stmt);
$count = mysqli_stmt_num_rows($stmt);
printf("Number of rows: %d.'n", mysqli_stmt_num_rows($stmt));//Prints number of rows
if ($count == 0) {
echo $output = "<p>There was no search results</p>"; //asign to variable $output message "There was no search results"
}
while (mysqli_stmt_fetch($stmt)) {
echo "<p>Master: $Master Junior Steward: $Junior_Steward</p>";
}
}
/* Prepared Statements Bind one variable to a prepared statement as parameters*/
//This works but only for one parameter
$sql = "SELECT Master FROM past_officers WHERE `Master` LIKE ? ";
$stmt = mysqli_stmt_init($mysqli);
if (mysqli_stmt_prepare($stmt, $sql)) {
mysqli_stmt_bind_param($stmt, "s", $search_query);
mysqli_stmt_bind_result($stmt, $Master);
mysqli_stmt_execute($stmt);
mysqli_stmt_store_result($stmt);
$count = mysqli_stmt_num_rows($stmt);
printf("Number of rows: %d.'n", mysqli_stmt_num_rows($stmt));//Prints number of rows
if ($count == 0) {
echo $output = "<p>There was no search results</p>"; //asign to variable $output message "There was no search results"
}
while (mysqli_stmt_fetch($stmt)) {
echo "<p>Master: $Master </p>";
}
}
$stmt->close();
$mysqli->close()
?>
你同时使用了 mysqli 类和 mysqli 中的函数。您实际上并没有使用其中之一。在其中一个之间进行选择，或者使用该类或使用 mysqli_connect（） 等函数;下面是如何使用类 mysqli 执行预准备语句的示例。
$stmt = new mysqli(DB_SERVER, DB_USER, DB_PASSWORD, DB_NAME);
$username = "Jordy";
$password = "MyLittleSecret";
if($query = $stmt->prepare("SELECT * FROM users WHERE username = ? AND password = ?")) {
$query->bind_param("ss", $username, $password);
$query->execute();
}
http://php.net/manual/en/mysqli.quickstart.prepared-statements.php