msqli does not INSERT

Question

im trying to insert data from a form (via POST) into a MySQL database, it does not show any errors, but it does not show up when I check it in phpMyAdmin.

    <?php

    $amount = $_POST["amount"];
    $unit = $_POST["unit"];
    $date = date('Y-m-d H:i:s');

    $host = "localhost";
    $user = "root";
    $pass = "";
    $db = "finance";
    $table = "silver";

    $mysqli = new mysqli($host, $user, $pass, 'finance');

    if(!$mysqli)
       {
       echo "<div class=\"error\">";
       echo "No connection can be established";
       echo "</div>";
       die();
       } 

   if ($unit = "gram") $amount = $amount * 28.3495231;

   // create query
   $query = "INSERT INTO 'silver'.'finance' (
    'Transaction_Num',
    'dtStamp',
    'Amount'
   ) 
   VALUES (
   NULL, \'2013-07-03 06:18:16\', \'1\'
   );";

   // execute query
   $mysqli->query($query);
   ?>

I'm running this on XAMPP 1.8.2 w/ PHP 5.4.16, Apache 2.4.4 and MySQL 5.6.11 on Windows XP.

Answer 1

Don't use single quotes on table and field names. That should be backticks.

Try this:

   $query = "INSERT INTO `silver`.`finance` (
    `Transaction_Num`,
    `dtStamp`,
    `Amount`
   ) 
   VALUES (
   NULL, '2013-07-03 06:18:16', '1'
   );";

OR/AND if the Transaction_Num is AUTO_INCREMENT you may not need to insert it like:

$query = "INSERT INTO silver.finance (dtStamp,Amount) 
   VALUES ( '2013-07-03 06:18:16', 1);";

OR/AND if you have dtStamp is DEFAULT = CURRENT_TIMESTAMP you may not need to insert that either like:

$query = "INSERT INTO silver.finance (Amount) 
   VALUES (1);";

Answer 2

You write it your own, "it does not show any errors". There are some things you need to keep in mind (and put on your checklist) with such a broad analysis.

First of all, PHP does not shows errors always. Whether or not it shows errors depends on the error configuration you are using. As you didn't show in your question it's hard to say if this is an issue or not, however, if you experience something unexpected (like you do), you should first of all check how and where you report errors.

Explaining the whole PHP error configuration would be too much for an answer here, a good resource (next to the manual which already documents everything in detail) we have this one here on the Stackoverflow website:

• How to get useful error messages in PHP?

Then you need to clarify if at all and where Mysqli gives errors. Let's review each step where things can turn out wrong.

Connection:

  $mysqli = new mysqli($host, $user, $pass, 'finance');

Instantiating a Mysqli with wrong credentials does give errors in PHP. However, the mysqli object will be instantiated anyway. That means the variable $mysqli will be always an object of type mysqli - regardless if the connection was successful or not.

As every object in PHP is always loosely true, a check like

if (!$mysqli) {

is not sufficient to tell you if there was a connection problem - so as you do it, you do it wrong here. $mysqli is always truthy so the if clause body well be never entered. Instead you need to check if the connection to the server could have been established by looking for a mysqli connection error. You can do so by checking for it's error number. It is 0 (integer number zero) if there was none, and a non-zero integer (truthy) if there was an error (because it than as the error number:

if ($mysqli->connect_errno) {

This property is documented in the PHP manual as well, see mysqli::$connect_errno. The different error numbers I can't explain in all detail, but you can find them documented in the Mysqli documentation here: C.3. Server Error Codes and Messages.

As another alternative to this - which also works to check if a previous connection is still active - is to run a test-query:

if (NULL === $mysqli->query("SELECT 1;")) {

A simple query like SELECT 1; returns NULL in case you failed to connect to the database server. This is for the many problems that can turn out when connecting to a server: You might be using a wrong username or password ((HY000/1045) "Access denied"), a wrong server ((HY000/2002) "Connection failed") or even using the wrong database ((HY000/1049) "Unknown database").

Running such test-query is easy to do and tells you quickly if the connection went well. In case you successfully connect, is returns an object of type mysqli_result which does not qualify as === NULL, therefore the update if-clause covers all these failure and success scenarios well.

It also shows you another great principle: Most methods return something that you can check to discover if something went wrong.

Execute the INSERT Query:

Next place to check for errors is with executing the INSERT ... SQL query:

// execute query
$mysqli->query($query);

As this code of yours shows, you're not doing any error checking here at all. Again, this is wrong. Instead as your expectations are not matched, you need to verify here if inserting worked or not. Again that method returns a value, in case of an INSERT ... SQL query this is a boolean whether or not the query worked:

// execute query
$result = $mysqli->query($query);
if (!$result) {
    throw new Exception(
        sprintf(
            'query "%s" failed: (%s/%d): %s', $query,
            $mysqli->sqlstate, $mysqli->errno, $mysqli->error
        )
    );
}

As this example shows, again a proper check of the return value has been added and if something went wrong, the error message is given by reading out the appropriate properties of your $mysqli connection object, here mysqli::$sqlstate, mysqli::$errno and mysqli::$error.

So better double check what is going wrong before runnning to the conclusion that "no error is given". First of all check if at all an error has to be given automatically (and where, e.g. to screen or into a log-file?). Or if not even it's up to you to check for error conditions because failure is always an option and not everything that "does not work" can be seen as a malfunction for the PHP extension or the PHP language even while you make use of it.