Mysql bind_param()错误

Question

<?php
require_once'config.php';
if(isset($_POST['subscribe'])){
  $email=$_POST['email'];

  //check the email if it is already subscribed or not
  if(strlen($email)>3){             
    $stmt=$mysql->prepare("SELECT * FROM subscribers WHERE email=?");
    $stmt->bind_param('s', $email);
    $stmt->execute();
    if($stmt->field_count > 0){ // if not then add
      unset($mysql); unset($stmt);
      $mysql=new mysqli(HOST,USER,PASS,DB);     
      $stmt=$mysql->prepare('INSERT INTO subscribers(email) VALUES (?)');
      $stmt->bind_param('s', $email);
      $stmt->execute();
      if($stmt->affected_rows>0){
        echo "subscribed";
      }
    } else { //else is there
      echo "Already there";
    }
  } else echo "empty string";
}
?>

如果我删除行unset($mysql); unset($stmt);，则会导致一个致命错误：Bind_param Non-Object Error。但是，如果我取消设置，然后重新定义对象，则可以正常工作。有谁能解释一下原因吗？

Answer 1

警告:这是未经测试的。然而，我认为演示如何以一种更干净的方式编写这篇文章是很重要的。特别是，将你的意图分解成特定的函数，每个函数做一件事并返回结果。这将有助于防止您的代码变得混乱，并使其更容易测试每个块自己。

require_once'config.php';

//It is always good to declare stuff like this in a central place
//That way they're easy to change, and you change it everywhere you have that express intent
$SQL_FIND_SUBSCRIPTION = "SELECT * FROM subscribers WHERE email=?";
$SQL_NEW_SUBSCRIPTION = "INSERT INTO subscribers(email) VALUES (?)";

//Functions make testing easy!
static function attemptSubscription($postValues, &$returnMsg) {
  if (   isset($postValues['subscribe']) 
      && isset($postValues['email']) //be sure to validate both!

  {
    if (isValidEmail($postValues['email'])) {
      subscribe($email);//this syntax may be off.

    } else {
      $returnMsg = "A valid email address must be provided.";
      return false;
    }
  } else {
    $returnMsg = "No subscription was attempted.";
    return false;
  }
}


//Returns true if the passed parameter is a valid email
static function isValidEmail($email) {
  //left as an exercise for the reader. 
}

//Assumes a valid email address is being passed
static function subscribe($email, &$returnMsg) {
  global $mysql; //sadly, this is the cleanest way possible without using a class.
  $stmt=$mysql->prepare($SQL_FIND_SUBSCRIPTION);
  $stmt->bind_param('s', $email);
  $stmt->execute();
  $stmt->store_result(); // This lets you reuse stmt

  if($stmt->field_count > 0){ 
    //Subscription already exists
    $returnMsg = "This subscription already exists.";
    return true;
  } else { 
    //Subscription must be added
    return addNewSubscription($email, $returnMsg);
  }
}

static function addNewSubscription($email, &$returnMsg) {
  global $mysql; // :(
  $stmt=$mysql->prepare($SQL_NEW_SUBSCRIPTION);
  $stmt->bind_param('s', $email);
  $stmt->execute();
  $stmt->store_result();

  if($stmt->affected_rows>0){
    $returnMsg = "New subscription successful!";
    return true;
  } else {
    $returnMsg = "New subscription failed.";//you can add more info here if you want
    return false;
  }

  $stmt->close();
}

//now actually execute
$resultMsg = "";
if (attemptSubscription($_POST, $resultMsg)) {
  echo "Success! ".$resultMsg;
} else {
  echo "Oh no! Failure! ".$resultMsg;
}



// ?> Note that eliding this will help remove nasty hidden characters from the rendered html

有关更多信息，请查看store_result。

使用这种配置，您可以验证各个命令是否正常工作，而不需要首先执行这些命令所嵌套的结构。特别是，验证第二个sql查询非常重要:非绑定错误通常是因为找不到列或表。然而，也就是说，要认识到prepare()语句所做的是向服务器发送一个查询，以检查其语法；该查询永远不会重新发送，而是会一直“加载”在服务器中，直到您(用另一条语句)告诉它删除它。当您随后绑定参数时，该参数将被发送到服务器并放入可用插槽(?)中。然后，您可以高效地多次执行此查询，这得益于此模型。

请注意，如果服务器上的查询没有新参数的未绑定插槽，则绑定它的尝试将失败。如果服务器上的查询试图引用不可用的变量(例如列名)，那么在此步骤中也会失败。

从您提供的详细信息中还不清楚确切的问题在哪里，但是如果您以一种更干净的方式编写代码，调试这些问题将变得更加容易:是您的第二条sql语句不好吗？是不是因为您没有从第一条语句中正确释放服务器资源？