mysqli到准备好的语句 [英] mysqli to prepared statement

问题描述

正在尝试将mysqli转换为prepare语句.他们中的大多数都取得了很大的进步，但这是不寻常的.希望有人能帮上忙.

Am trying to convert mysqli to prepare statement. Have being making alot of progress with most of them,but is unusual. I hope some can help with it.

这是我的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();

我虽然我只运行了一次却出错了，但我知道我最想念的是东西.

I Although i run it once and i get error, i know am most be missing some thing.

推荐答案

将这些未准备好的语句更改为准备好的语句的尝试是错误的.几个问题是:

Your attempt to change those non-prepared statements to prepared statements is wrong. Few issues are:

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

• There's no need to create three separate statement objects for SELECT, UPDATE and INSERT, only one statement object is enough. Having said that, always close the prepared statement using using it again for your query.
• if(!$stmt->bind_param(..., if(!$stmt->execute() etc. $stmt is not a statement object, you never even created or used this variable anywhere. And that's why you're getting this Fatal error: Call to a member function bind_param() on a non-object ... error.
• Looking at your non-prepared code above, there's no need to use ->bind_result() or ->fetch() method, simply perform INSERT or UPDATE operation and redirect the user to a different page.

您准备好的代码应该是这样的:(底层逻辑严格类似于您未准备好的代码)

Your prepared code should be like this: (The underlying logic strictly resembles your non-prepared code)

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);
}

这篇关于mysqli到准备好的语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！