我在存储过程中进行搜索查询,但问题是每次搜索单词时都会显示错误。
我的存储过程
DELIMITER//
CREATE PROCEDURE viewMedicine(product_name varchar(40), description text)
READS SQL DATA
DETERMINISTIC
begin
PREPARE STMT FROM "SELECT * FROM products WHERE product_name like ? OR description like ? ORDER BY ASC";
SET @s=product_name;
SET @ss=description;
EXECUTE STMT USING @s, @ss;
end//
DELIMITER;
php代码:
$search="";
if(isset($_POST['search_txt'])){
$search=$_POST['search_txt'];
if($search!="")
$sql="Call searchMedicine('$search%')";
else
$sql="SELECT id, product_name, description, price, quantity, date FROM products ORDER BY product_name ASC";
}
else
$sql="SELECT id, product_name, description, price, quantity, date FROM products ORDER BY product_name ASC";
$result = mysqli_query($conn, $sql);
while($test = mysqli_fetch_array($result))
{
$id = $test['id'];
?>
您看到了什么错误?存储过程名为viewMedicine,有两个参数:product_name和description。您的sql代码正在调用searchMedicine,并且只传递一个参数$search。
您对mysql_fetch_array的调用很可能不正确。您需要传递由mysqli_query返回的mysql_result对象。如果没有看到更多你的代码,我就无法确定。请看我的评论。
编辑:感谢您提供代码。您对mysqli_query的使用是正确的,但存在语法或参数错误。从SPROC的定义来看,我认为这是一个论点错误。
尝试进行以下更改:
$search="";
if (isset($_POST['search_txt'])){
$search=$_POST['search_txt'];
if($search!="")
$sql="Call viewMedicine('$search%', '')";
else
$sql="SELECT id, product_name, description, price, quantity, date FROM products ORDER BY product_name ASC";
}
else {
$sql="SELECT id, product_name, description, price, quantity, date FROM products ORDER BY product_name ASC";
}
$result = mysqli_query($conn, $sql);
if(!$result) {
echo("Error description: " . mysqli_error($conn));
}
else {
while($test = mysqli_fetch_array($result))
{
$id = $test['id'];
?>
如果您不需要在查询中搜索描述,请将其从SPROC:中删除
PREPARE STMT FROM "SELECT * FROM products WHERE product_name like ?";
SET @s=product_name;
EXECUTE STMT USING @s;
然后,您代码中的呼叫将变为:
...
if($search!="")
$sql="Call viewMedicine('$search%')";
else
...
编辑2:
如果你想搜索描述,你需要把它放到脚本中。我想,您可能希望以与搜索产品名称相同的方式进行搜索,即通过$_POST数组,因此我将为该假设编写代码。当然,请注意,您需要对向该脚本发送POST数据的网络表单进行必要的更改。
$search="";
if (isset($_POST['search_txt']) &&
isset($_POST['description_txt'])){
$search=$_POST['search_txt'];
$description=$_POST['description_txt'];
if($search != "" && $description != "")
$sql="Call viewMedicine('$search%', '%$description%')";
else
$sql="SELECT id, product_name, description, price, quantity, date FROM products ORDER BY product_name ASC";
}
else {
$sql="SELECT id, product_name, description, price, quantity, date FROM products ORDER BY product_name ASC";
}
$result = mysqli_query($conn, $sql);
if(!$result) {
echo("Error description: " . mysqli_error($conn));
}
else {
while($test = mysqli_fetch_array($result))
{
$id = $test['id'];
?>