mysqli to prepared语句

Question

我正试图将mysqli转换成准备语句。已经取得了很大的进步，但这是不寻常的。我希望有人能帮上忙。

这里是我的mysqli代码

            $UpdateQuery = "UPDATE user SET avatar ='$NewImageName' WHERE user_name = '$temp'";
            $InsertQuery = "INSERT INTO user (avatar) VALUES ('$NewImageName')";

           $result = mysqli_query($con, "SELECT * FROM user WHERE user_name = '$temp'");
            if( mysqli_num_rows($result) > 0) {
                if(!empty($_FILES['ImageFile']['name'])){
                    mysqli_query($con, $UpdateQuery)or die(mysqli_error($con));
                    header("location:edit-profile.php?user_name=$temp");
                }
            } 
            else {
                mysqli_query($con, $InsertQuery)or die(mysqli_error($con));
                header("location:edit-profile.php?user_name=$temp");
            }  

--这是我尝试用准备好的语句修复它的尝试

           if(!($stmtUpdate = $con->prepare("UPDATE user SET avatar = ? WHERE user_name = ?"))) {
        echo "Prepare failed: (" . $con->errno . ")" . $con->error;
    }
        if(!($stmtInsert = $con->prepare("INSERT INTO user ( avatar ) VALUES ( ? )"))) {
        echo "Prepare failed: (" . $con->errno . ")" . $con->error;
    } 
        if(!($stmtSelect = $con->prepare("SELECT * FROM user WHERE user_name = ? "))) {
        echo "Prepare failed: (" . $con->errno . ")" . $con->error;
    }        
        if(!$stmt->bind_param('sss', $temp, $NewImageName, $temp)) {
      echo "Binding paramaters failed:(" . $stmt->errno . ")" . $stmt->error;
    }      
        if(!$stmt->execute()){
             echo "Execute failed: (" . $stmt->errno .")" . $stmt->error;
    }

    $stmt->store_result();  
    if($stmt->num_rows == 0) {
           if(!empty($_FILES['ImageFile']['name'])){
                    $con->prepare($stmtUpdate)or die(mysqli_error($con));
                    header("location:edit-profile.php?user_name=$temp");
             exit;
                }
            } else {
        $stmt->bind_result($avatar, $avatar, $temp);
        $stmt->fetch();
          header("location:edit-profile.php?user_name=$temp");
        }

   $stmt->close();

我虽然运行过它一次，也有错误，但我知道我最缺少的是一些东西。

Answer 1

您试图将那些未准备好的语句更改为准备好的语句是错误的。很少有问题是：

• 不需要为SELECT、UPDATE和INSERT创建三个单独的语句对象，只要一个语句对象就足够了。尽管如此，请始终在查询中使用它来关闭准备好的语句。
• if(!$stmt->bind_param(...，if(!$stmt->execute()等。$stmt不是一个语句对象，您甚至从来没有在任何地方创建或使用过这个变量。这就是为什么您要得到这个致命的错误:调用非对象上的成员函数bind_param() .错误。
• 查看上面未准备好的代码，不需要使用->bind_result()或->fetch()方法，只需执行INSERT或UPDATE操作，并将用户重定向到不同的页面。

准备好的代码应该如下所示：(底层逻辑与未准备好的代码非常相似)

if(!($stmt = $con->prepare("SELECT * FROM user WHERE user_name = ?"))){
    die("Prepare failed: (" . $con->errno . ") " . $con->error);
} 
if(!$stmt->bind_param('s', $temp)){
    die("Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error);
}
if($stmt->execute()){
    $stmt->store_result();
    $num_rows = $stmt->num_rows;
    $stmt->close();

    if($num_rows){
        if(!empty($_FILES['ImageFile']['name'])){
            if(!($stmt = $con->prepare("UPDATE user SET avatar = ? WHERE user_name = ?"))){
                die("Prepare failed: (" . $con->errno . ") " . $con->error);
            } 
            if(!$stmt->bind_param('ss', $NewImageName, $temp)){
                die("Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error);
            }
            if($stmt->execute()){
                $stmt->close();
                header("location:edit-profile.php?user_name=" . $temp);
                exit();
            }else{
                die("Execute failed: (" . $stmt->errno . ") " . $stmt->error);
            }
        }
    }else{
        if(!($stmt = $con->prepare("INSERT INTO user (avatar) VALUES (?)"))){
            die("Prepare failed: (" . $con->errno . ") " . $con->error);
        } 
        if(!$stmt->bind_param('s', $NewImageName)){
            die("Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error);
        } 
        if($stmt->execute()){
            $stmt->close();
            header("location:edit-profile.php?user_name=" . $temp);
            exit();
        }else{
            die("Execute failed: (" . $stmt->errno . ") " . $stmt->error);
        }
    }
}else{
    die("Execute failed: (" . $stmt->errno . ") " . $stmt->error);
}