I'm in the process of creating a game for myself and I have been using the MySQLi extension. This is really my first "big" project using it and so far I don't mind it too much, though I do find it very... verbose. Anyway, so far so good except this one issue I have run into regarding inserting a value into one of my tables. I have created a function to insert a user into the users table but it's failing and I am getting several PHP warnings. The following is the function.
/**
* Inserts a user into the user table.
*
* @param mysqli $conn The database connection object.
* @param string $email The user's email address.
* @param string $password The user's hashed password.
* @param string $salt The user's unique salt.
* @param int $country The user's country.
* @return bool Returns true if the user was inserted successfully or false if not.
*/
function insert_user($conn, $email, $password, $salt, $country)
{
$query = "INSERT INTO users (user_email, user_pass, user_salt, country_id)
VALUES (?, ?, ?, ?)";
//Prepare the query
$statement = mysqli_prepare($conn, $query);
//Bind the parameters
mysqli_stmt_bind_param($statement, 'sssi', $email, $password, $salt, $country);
//Execute the query
mysqli_stmt_execute($statement);
//Close the statement
mysqli_stmt_close($statement);
return (mysqli_affected_rows($statement)) ? true : false;
}
The messages I am getting:
Warning: mysqli_stmt_bind_param() expects parameter 1 to be mysqli_stmt, boolean given in F:\Web\airline\api\registration.php on line 148
Warning: mysqli_stmt_execute() expects parameter 1 to be mysqli_stmt, boolean given in F:\Web\airline\api\registration.php on line 151
Warning: mysqli_stmt_close() expects parameter 1 to be mysqli_stmt, boolean given in F:\Web\airline\api\registration.php on line 154
Warning: mysqli_affected_rows() expects parameter 1 to be mysqli, boolean given in F:\Web\airline\api\registration.php on line 156
These error messages seem pretty straightforward and easily fixable if you read the manual for the specific functions (while I am transitioning to MySQLi I prefer to stick with a procedural approach). It seems obvious that my mistake is mysqli_prepare() does not return a statement object but a boolean instead. Silly me. But I also have the following function which works as expected:
/**
* Checks if the email address is unique.
*
* @param mysqli $conn The database connection object.
* @param string $email The email to check.
* @return bool Returs true if the email address is unique or false if not.
*/
function unique_email($conn, $email)
{
$query = "SELECT user_email
FROM users
WHERE user_email = ?";
//Prepare the query
$statement = mysqli_prepare($conn, $query);
//Bind the parameters
mysqli_stmt_bind_param($statement, 's', $email);
//Execute the statement
mysqli_stmt_execute($statement);
//Bind the result set
mysqli_stmt_bind_result($statement, $result);
//Fetch the result set
mysqli_stmt_fetch($statement);
//Close the statement
mysqli_stmt_close($statement);
return (NULL == $result) ? true : false;
}
According to the manual, this should not work, but it does. It was only going back and rereading the manual did I notice this discrepancy (I haven't gone back and changed this function yet).
Anyway, I have noticed in a lot of the examples in the manual they use mysqli_stmt_init(), which does return a mysqli_stmt object. So I tried using that (removed some of the irrelevant code):
//Initialize a prepared statement
$statement = mysqli_stmt_init($conn);
//Prepare the query
mysqli_prepare($statement, $query);
//Bind the parameters
mysqli_stmt_bind_param($statement, 'sssi', $email, $password, $salt, $country);
//Execute the query
mysqli_stmt_execute($statement);
//Close the statement
mysqli_stmt_close($statement);
return (mysqli_affected_rows($statement)) ? true : false;
But this also produces errors:
Warning: mysqli_prepare() expects parameter 1 to be mysqli, object given in F:\Web\airline\api\registration.php on line 148
Warning: mysqli_stmt_bind_param(): invalid object or resource mysqli_stmt in F:\Web\airline\api\registration.php on line 151
Warning: mysqli_stmt_execute(): invalid object or resource mysqli_stmt in F:\Web\airline\api\registration.php on line 154
Warning: mysqli_stmt_close(): invalid object or resource mysqli_stmt in F:\Web\airline\api\registration.php on line 157
Warning: mysqli_affected_rows() expects parameter 1 to be mysqli, object given in F:\Web\airline\api\registration.php on line 159
Notably it says that mysqli_prepare() expects the first parameter to mysqli, but the manual page for [man]mysqli_prepare/man specifically states that it returns a mysqli_statement object for mysqli_prepare().
If I try to echo out mysqli_stmt_error() or mysqli_error() I get an empty string.
I figured it must be my $conn variable that has an issue but I printed it out and I got the following (not sure what to expect so the problem may be evident here):
mysqli Object
(
[affected_rows] => -1
[client_info] => mysqlnd 5.0.10 - 20111026 - $Id: b0b3b15c693b7f6aeb3aa66b646fee339f175e39 $
[client_version] => 50010
[connect_errno] => 0
[connect_error] =>
[errno] => 0
[error] =>
[error_list] => Array
(
)
[field_count] => 1
[host_info] => localhost via TCP/IP
[info] =>
[insert_id] => 0
[server_info] => 5.5.25a
[server_version] => 50525
[stat] => Uptime: 413269 Threads: 2 Questions: 19489 Slow queries: 0 Opens: 392 Flush tables: 1 Open tables: 2 Queries per second avg: 0.047
[sqlstate] => 00000
[protocol_version] => 10
[thread_id] => 1046
[warning_count] => 0
)
You could say I am quite confused on how this all works. I am sure my mistake is obvious but I have been looking at this for so long that I cannot see it, which is why I hope another pair of eyes will notice my mistake. Sorry for the long post; I just wanted to present as much information as I could.
Thanks in advance!
