PHP MySQLi INSERT not working, no errors

Question 1

Different from this question, but similar in that I don't get an error when adding information to my database.

$sql = "INSERT INTO 'nlcc_ver1'.'tUsers' ('userID', 'userName', 'userPassword', 'userHash',
'user_first_name', 'user_last_name', 'user_corps', 'is_admin', 'is_trg', 'is_sup', 'is_co')
VALUES (NULL, '" . $userName . "', '" . $hash . "', '" . $salt . "', '" . $f_name . "', '" .
$l_name . "', '" . $corps . "', '" . $admin . "', '" . $trg . "', '" . $sup . "', '" . $co . "')";
$hostname_Database = "localhost";
$database_Database = "nlcc_ver1";
$username_Database = "root";
$password_Database = "";
$mysqli = new mysqli($hostname_Database, $username_Database, $password_Database, $database_Database); 
if (mysqli_connect_errno()) {
printf("Connect failed: %sn", mysqli_connect_error());
exit();
}
$result = $mysqli_query($mysqli, $sql);
echo "Query run. Inserted UserID " . mysqli_insert_id($mysqli) . "<br />";

Line breaks inserted to avoid sideways scrolling... It says on the web page that mysqli_insert_id($mysqli) is 0, and nothing is added to the table on my database. I do not see an error connecting to the database appearing, and MySQL is running on my server, and phpinfo() shows both the MySQL and MySQLI extension loaded. This is just a development machine, so don't worry about the security (i.e. no password). I have tried googling the problem, but am not finding too much. I don't know about object oriented PHP programming with ->, I am used to using _. Is this method still supported?

Question 2

You've mixed procedural and object-oriented MySQLi styles. This has led to you trying to use the functions like mysqli_query($mysqli) instead of the member functions like $mysqli->query(). Your $mysqli is an object, not a resource handle.

And, you're not performing any error checking on your query. If you were, you'd see that you have mistakenly used single quotes to delimit table and field names, not backticks.

$sql = "INSERT INTO `nlcc_ver1`.`tUsers`
       (`userID`, `userName`, `userPassword`, `userHash`,
        `user_first_name`, `user_last_name`, `user_corps`,
        `is_admin`, `is_trg`, `is_sup`, `is_co`)
       VALUES (NULL, '" . $userName . "', '" . $hash . "', '" . $salt . "', '" .
               $f_name . "', '" . $l_name . "', '" . $corps . "', '" . $admin .
               "', '" . $trg . "', '" . $sup . "', '" . $co . "')";

$hostname_Database = "localhost";
$database_Database = "nlcc_ver1";
$username_Database = "root";
$password_Database = "";

$mysqli = new mysqli($hostname_Database, $username_Database, $password_Database, $database_Database); 
if (mysqli_connect_errno()) {
   printf("Connect failed: %sn", mysqli_connect_error());
   exit();
}

$result = $mysqli->query($sql);
if (!$result) {
   printf("%sn", $mysqli->error);
   exit();
}

echo "Query run. Inserted UserID " . $mysqli->insert_id . "<br />";

I strongly suggest using the manual as your reference. It's quite clear on how to use these functions when you're using either procedural or object-oriented style MySQLi.

Question 3

I went ahead and ran a test where one query uses a prepared statement, and the other builds the entire query then executes that. I'm probably not making what I'm wanting to know easy to understand.

Here's my test code. I was thinking prepared statements sort of held back execution until a $stmt->close() was called to optimize it or something. That doesn't appear to be the case though as the test that builds the query using real_escape_string is at least 10 times faster.

<?php

$db = new mysqli('localhost', 'user', 'pass', 'test');

$start = microtime(true);
$a = 'a';
$b = 'b';

$sql = $db->prepare('INSERT INTO multi (a,b) VALUES(?, ?)');
$sql->bind_param('ss', $a, $b);
for($i = 0; $i < 10000; $i++)
{
    $a = chr($i % 1);
    $b = chr($i % 2);
    $sql->execute();
}
$sql->close();

echo microtime(true) - $start;

$db->close();

?>

Question 4

This actually depends on the Mysql server. The default max size for all data combined in the entire query is 1mb. See: http://dev.mysql.com/doc/refman/5.1/en/packet-too-large.html

If your data combined is under that "max_allowed_packet" threshold, just use "s" for the binding type for any text field. Infact, you can usually get away with using "s" for any field type at all (date, float, etc).

If your entire entry combined that you want to insert is over 1mb (or whatever you reset it to) in length, you'll want to use mysqli_stmt::send_long_data method and the "b" binding type to send this particular field in chunks.

Question 5

In mySQL the identifier quote character is the backtick , so replace the single-quotes surrounding the column-names by backticks:

$sql = "INSERT INTO accounts (`username`,`password`,`email`)    
                     VALUES('{$username}','{$hashedpass}','{$email}')";

Question 6

This script was running fine

I find that very hard to believe. You have specified that none of the 10 attributes can be null, only 2 have default values (id, access), and your script only sets 6 of the values on INSERT.

If you have specified that a value MUST NOT BE NULL then you must either specify a default or set a value.

If the script works on the old server, it must have a different schema to that above.

PHP MySQLi INSERT not working, no errors

Answers

php,mysqli