当我更改数据库时，Prepared语句失败

Question

the issue

So I bumped into something curious this morning when I was updating my database. I executed a collation change in my database, changing it from latin1 to uft8. However, my queries failed suddenly on my table. After some debugging, (rebuilding the table even with its original setup, but to no such avail) and receiving 500 internal errors, i realized it had to do with the prepared statement, so i tore it out, and replaced it with a regular mysqli_query, and it surprisingly worked. So now I am wondering, was my prepared statement wrong the whole time, or did it fail because of a change in the database.

the setup

This is the current table set up. I changed it back to latin (and its innoDB) yet it didnt gave me the results back i wanted when i changed everything back to the original settings (which is how it is now)

the code

the original code was this and it worked fine until the change

    require_once '../db/dbControl.php';

    $id = mysqli_real_escape_string($con,$_GET["id"]);  
    $sql = "SELECT  *
            FROM project
            WHERE   project.ProjectId = ? ";    
  $stmt1 = mysqli_prepare($con, $sql);
  mysqli_stmt_bind_param($stmt1,'i',$id);
  mysqli_stmt_execute($stmt1);
  mysqli_stmt_bind_result($stmt1,$ProjectId,$ProjectTitel,$ProjectOmschrijving, $ProjectOmschrijving,$ProjectDatum,$ProjectClient,$ProjectUrl);
  while (mysqli_stmt_fetch($stmt1)){ 

  the code itself of the page

  }

So right now I am just using a regular mysqli_query in order to make it work

require_once '../db/dbControl.php';

id = mysqli_real_escape_string($con,$_GET["id"]);   
$sql = "SELECT  *
        FROM project
        WHERE   project.ProjectId = '". $id ."'";   
$result = mysqli_query($con,$sql);
while($rows=mysqli_fetch_array($result)){
    $ProjectId = $rows['ProjectId'];
    $ProjectTitel = $rows['ProjectTitel'];
    $ProjectExpertise = $rows['ProjectExpertise'];
    $ProjectOmschrijving = $rows['ProjectOmschrijving'];
    $ProjectDatum = $rows['ProjectDatum'];
    $ProjectClient = $rows['ProjectClient'];
    $ProjectUrl = $rows['ProjectUrl'];

    the code itself of the page

  }

I am a little bit confused (maybe i overlooked something here because to focussed on a little bit of code) but it only happens on the project table. I checked it against code that involves readouts, and they work all fine with prepped statements.

Hope anyone can spot what I couldn't

Answer 1

I wont be able to tell you what happened but here are two thought's.

The prepared statement execution consists of two stages: prepare and execute. At the prepare stage a statement template is sent to the database server. The server performs a syntax check and initializes server internal resources for later use.

A prepared statement can be executed repeatedly. Upon every execution the current value of the bound variable is evaluated and sent to the server. The statement is not parsed again. The statement template is not transferred to the server again.

Maybe this is what happened, it could be that the prepared statements never reseted after you changed to utf8.

Every prepared statement occupies server resources. Statements should be closed explicitly immediately after use. If not done explicitly, the statement will be closed when the statement handle is freed by PHP.

Using a prepared statement is not always the most efficient way of executing a statement. A prepared statement executed only once causes more client-server round-trips than a non-prepared statement. This is why the SELECT is not run as a prepared statement above.

Maybe the server memory (is/was) full?

Tekst from: http://php.net/manual/en/mysqli.quickstart.prepared-statements.php