title: PHP MySQLi prepared statements don't appear to bind or pass variables - but works with PDO prepare
body: <p>I'm having some trouble with an application that's recently stopped working and I can't figure out what the issue might be. </p>
  
  <p>Reading from the database is working well, but when I try to insert, or update an existing record it doesn't work. Occasionally the record field value is set to null instead of the variable value. </p>
  
  <p>This application is currently running on a shared host and it was working fine for several months until just a week ago. I have the UAT instance sitting on another host with another provider and it seems to be working without issue. </p>
  
  <p>During my investigation, I noticed that if I updated or inserted via the standard PHP MYSQLi procedural statement that the record was being correctly updated/inserted, but when I try and bind it via the prepared statement that it doesn't work. </p>
  
  <p>No errors are being thrown by PHP/MySQL.</p>
  
  <p>I've tried simplifying things by creating a simple test script that updates one record with a value.  </p>
  
  <p><strong>This works:</strong></p>
  
  <pre><code>&lt;?php
      // Connection file
      require 'connection.php';
  
      // Create connection
      $conn = new mysqli($servername, $username, $password, $dbname);
  
      // Check connection
      if ($conn-&gt;connect_error) {
          die("Connection failed: " . $conn-&gt;connect_error);
      }
  
      // set parameters and execute
      $firstname = htmlspecialchars($_GET["firstname"]);
  
      $sql = "UPDATE users SET first_name='$firstname' WHERE uid=1";
  
      if ($conn-&gt;query($sql) === TRUE) {
          echo "Updated record successfully";
      } else {
          echo "Error updating record: " . $conn-&gt;error;
      }
  
      $conn-&gt;close();
      ?&gt;
  </code></pre>
  
  <p><strong>While this does not:</strong> </p>
  
  <pre><code>&lt;?php
      // Connection file
      require 'connection.php';
  
      // Create connection
      $conn = new mysqli($servername, $username, $password, $dbname);
  
      // Check connection
      if ($conn-&gt;connect_error) {
          die("Connection failed: " . $conn-&gt;connect_error);
      }
  
      // set parameters and execute
      $firstname = htmlspecialchars($_GET["firstname"]);
  
      // prepare and bind
      $stmt = $conn-&gt;prepare("UPDATE users SET first_name=? WHERE uid =1");
      $stmt-&gt;bind_param("s", $firstname);
  
  
      $stmt-&gt;execute();
  
      echo "Updated record successfully";
  
      $stmt-&gt;close();
      $conn-&gt;close();
      ?&gt;
  </code></pre>
  
  <p>I'm running PHP v7.0.30, Apache v2.4.33 and MySQL v10.2.15 (MariaDB)</p>
  
  <p>Any ideas. This is driving me crazy...</p>
  
  <p>I've just tried returning the number of affected rows for the second example.</p>
  
  <pre><code>printf("Affected rows (UPDATE): %d\n", $stmt -&gt;affected_rows);
  </code></pre>
  
  <p>If the first_name in the database table has a value, and I run the script outputting the number of rows, it returns <em>'Affected rows 1'</em>. When I check the database I can see that the value is now empty. </p>
  
  <p>What could cause this? Some sort of encoding issue perhaps?</p>
  
  <p><strong>UPDATE 1</strong></p>
  
  <p>Just added var_dump($stmt, $firstname); to second example after bind.</p>
  
  <pre><code>object(mysqli_stmt)#3 (10) { ["affected_rows"]=&gt; int(-1) ["insert_id"]=&gt; int(0) ["num_rows"]=&gt; int(0) ["param_count"]=&gt; int(1) ["field_count"]=&gt; int(0) ["errno"]=&gt; int(0) ["error"]=&gt; string(0) "" ["error_list"]=&gt; array(0) { } ["sqlstate"]=&gt; string(5) "00000" ["id"]=&gt; int(0) } string(3) "Bob" 
  </code></pre>
  
  <p>Affected rows: 1</p>
  
  <p>and</p>
  
  <pre><code>object(mysqli_stmt)#3 (10) { ["affected_rows"]=&gt; int(-1) ["insert_id"]=&gt; int(0) ["num_rows"]=&gt; int(0) ["param_count"]=&gt; int(1) ["field_count"]=&gt; int(0) ["errno"]=&gt; int(0) ["error"]=&gt; string(0) "" ["error_list"]=&gt; array(0) { } ["sqlstate"]=&gt; string(5) "00000" ["id"]=&gt; int(0) } string(4) "Jack" 
  </code></pre>
  
  <p>Affected rows: 0`</p>
  
  <p><strong>UPDATE 2</strong></p>
  
  <p>I'm starting to think this is a server issue.
  I've just tested updating a record using PDO prepare and it's working.</p>
  
  <pre><code>&lt;?php
      // Connection file
      require 'connection.php';
  
      // set parameters
      $firstname = $_GET["firstname"];
      $id = 1;
  
      try {
          $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
          // set the PDO error mode to exception
          $conn-&gt;setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  
          // prepare sql and bind parameters
          $stmt = $conn-&gt;prepare("UPDATE users SET first_name=? WHERE uid=?");
          $stmt-&gt;execute([$firstname,$id]);
  
          }
      catch(PDOException $e)
          {
          echo "Error: " . $e-&gt;getMessage();
          }
      $conn = null;
  
  ?&gt;
  </code></pre>
  
  <p>When I try and execute a prepared statement to SELECT, I get a '503 Service Unavailable'. Works fine locally.</p>
  
  <pre><code>&lt;?php
      // Connection file
      require 'connection.php';
  
      // Create connection
      $conn = new mysqli($servername, $username, $password, $dbname);
  
      // Check connection
      if ($conn-&gt;connect_error) {
          die("Connection failed: " . $conn-&gt;connect_error);
      }
  
      // set parameters and execute
      $id = 1;
  
      //create a prepared statement
      $query = "SELECT first_name FROM users WHERE uid=?";
      $stmt  = $conn-&gt;prepare($query);
  
      $stmt -&gt;bind_param('i', $id);
  
      //execute query
      $stmt -&gt;execute();
  
      //bind result variables
      $stmt -&gt;bind_result($first_name);
  
      //fetch records
      while($stmt -&gt;fetch()) {
          print $first_name;
      }   
  
      //close connection
      $stmt -&gt;close();
  
      mysqli_close($conn);
  ?&gt;
  </code></pre>
  
  <p><strong>FINAL UPDATE - ISSUE RESOLVED</strong> </p>
  
  <p>My hosting provider was able to resolve the issue. It seems that the nd_mysqli PHP extension was disabled. With it enabled the above examples work and my application is now functional.</p>