mysqli to prepared statement

问题:

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.

here is my mysqli code

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

These is my attempt to try and fix it with prepared statement

           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.

回答1:

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

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