Prepare of MySQLi statement fails

[TapeGun007]

 

 
//Grab Email and Expiration Code
 
$query = "SELECT UserEmail, UserCodeExpire FROM Users WHERE UserEmail = '$UserEmail'";
$query_result = mysqli_query($mysqli, $query) or die (mysqli_error($mysqli));
$result = mysqli_fetch_assoc($query_result);
 
// If there is no result, OR the user code has expired
 
if ( !$result || $result['UserCodeExpire'] < date("Y-m-d")) {
   $stmt = $mysqli->stmt_init();
   if(!stmt){
      echo "<span class='error'>Initialization of Statement Failed</span>";    
      // Since the user doesn't exist, INSERT 
   }else{
      $cmd = "INSERT INTO Users(UserFirstName, UserLastName, UserEmail, UserCode, UserExpireDate) VALUES (?,?,?,?,?)";
      if ($stmt->prepare($cmd)){
         $stmt->bind_param('sssss', $UserFirstName, $UserLastName, $UserEmail, $UserCode, $UserExpireDate);
         $stmt->execute();
         close();
         echo "<b>Invitation updated in the db</b>";
      }else{
         echo "<span class='error'>Prepare Failed</span>";
      }
   }
}

[TapeGun007]

The last line of code, "Prepare failed" comes up each time.  I presume this has to do with the previous query, but I'm not sure how or why.  I'm trying to move over from the regular MySQL to MySQLi so be gentle.

[Love2c0de]

I believe it is because you re checking the return value of prepare() within an if statement. prepare returns an object on success or FALSE on failure.

 

Try this out and see if it helps you:

 

$cmd = "INSERT INTO Users(UserFirstName, UserLastName, UserEmail, UserCode, UserExpireDate) VALUES (?,?,?,?,?)";

$stmt->prepare($cmd) or die ("Error with prepared statement.".mysqli_error());
$stmt->bind_param('sssss', $UserFirstName, $UserLastName, $UserEmail, $UserCode, $UserExpireDate);
$stmt->execute();
$stmt->store_result();

$rows = $stmt->affected_rows;

if($rows == 1)
{
    echo "<b>Invitation updated in the db</b>";
}
else
{
    echo "<span class='error'>Prepare Failed</span>";
}

 

Failing that you could change your if statement to this:

 

if(!$tmt->prepare($cmd)))
{
//error
}
else
{
//success
}

Does this help at all?

 

Kind regards,

 

L2c.

[Love2c0de]

Can't edit my post but in the second section of code change the variable from $tmt to $stmt.

 

Regard,

 

L2c.

[TapeGun007]

I took your code, and put it in it's own file.  I then enabled errors, so I could see what was going on.

 

The error I get from running your code sample above is

 

Warning: mysqli_error() expects exactly 1 parameter, 0 given in /test2.php on line 17
Error with prepared statement.

 

Here is the exact code that I used:

 

 

 
<?php
 
include("db/dbconnection.php");
 
ini_set('display_errors',1); 
error_reporting(E_ALL);
 
$UserFirstName = "John";
$UserLastName = "Doe";
$UserEmail = "johndoe@aol.com";
$UserCode = "1234";
$UserExpireDate = "2013-04-04";
 
$stmt = $mysqli->stmt_init();
 
$cmd = "INSERT INTO Users(UserFirstName, UserLastName, UserEmail, UserCode, UserExpireDate) VALUES (?,?,?,?,?)";
$stmt->prepare($cmd) or die ("Error with prepared statement.".mysqli_error());
$stmt->bind_param('sssss', $UserFirstName, $UserLastName, $UserEmail, $UserCode, $UserExpireDate);
$stmt->execute();
$stmt->store_result();
 
$rows = $stmt->affected_rows;
 
if($rows == 1)
{
    echo "<b>Invitation updated in the db</b>";
}else{
    echo "<span class='error'>Prepare Failed</span>";
}
 
?>

Edited March 5, 2013 by TapeGun007

[teynon]

 

 
//Grab Email and Expiration Code
 
$query = "SELECT UserEmail, UserCodeExpire FROM Users WHERE UserEmail = '$UserEmail'";
$query_result = mysqli_query($mysqli, $query) or die (mysqli_error($mysqli));
$result = mysqli_fetch_assoc($query_result);
 
// If there is no result, OR the user code has expired
 
if ( !$result || $result['UserCodeExpire'] < date("Y-m-d")) {
   $stmt = $mysqli->stmt_init();
   if(!stmt){
      echo "<span class='error'>Initialization of Statement Failed</span>";    
      // Since the user doesn't exist, INSERT 
   }else{
      $cmd = "INSERT INTO Users(UserFirstName, UserLastName, UserEmail, UserCode, UserExpireDate) VALUES (?,?,?,?,?)";
      if ($stmt->prepare($cmd)){
         $stmt->bind_param('sssss', $UserFirstName, $UserLastName, $UserEmail, $UserCode, $UserExpireDate);
         $stmt->execute();
         close();
         echo "<b>Invitation updated in the db</b>";
      }else{
         echo "<span class='error'>Prepare Failed</span>";
      }
   }
}

Call me OCD, but why do you switch between Object Oriented and Procedural mysqli calls?

[TapeGun007]

I plan to fix that, it's just because I was a complete noob when I wrote the first query.

 

The problem is, when I do searches on the web about MySQLi, I get half a dozen that give procedural examples, and half that were showing OOP, but not really explaining WHAT the code was doing and why you coded it one way or the other.  After MUCH reading, it's finally starting to sink in.

[TapeGun007]

I found an example on the web, plugged in the code, and it works!  It is using the IF statement to prepare the variable as well.

 

Here is the code:

 

 
<?php
 
include("db/dbconnection.php");
 
ini_set('display_errors',1); 
error_reporting(E_ALL);
 
$UserFirstName = "John";
$UserLastName = "Doe";
$UserEmail = "johndoe@aol.com";
$UserCode = "1234";
$UserExpireDate = "2013-04-04";
 
/* Create the prepared statement */
if ($stmt = $mysqli->prepare("INSERT INTO Users (UserFirstName, UserLastName) values (?, ?)")) {
 
    /* Bind our params */
    $stmt->bind_param('ss', $UserFirstName, $UserLastName);
    
    /* Execute the prepared Statement */
    $stmt->execute();
    
    /* Echo results */
    echo "Inserted {$UserLastName},{$UserFirstName} into database\n";
    
    
    /* Close the statement */
    $stmt->close();
}
else {
    /* Error */
    printf("Prepared Statement Error: %s\n", $mysqli->error);
}
?>