Can't make INSERT INTO sql database table work

Question

<?php
    session_start();
    $con=mysqli_connect("localhost","root","","accting");
    $query = "USE accting";
    $result = mysqli_query($con,$query);

    $query = "INSERT INTO document (docDate, supplierName, refNo, vatReg, vpGoods, viGoods, nvPurchases, totalAmt, book, cash, account, termsMonth, termsDay) VALUES ('{$_POST['invoiceDate']}', '{$_POST['supplierName']}', '{$_POST['refNo']}', '{$_POST['vatReg']}', '{$_POST['amtVat']}', '{$_POST['vatInput']}', '{$_POST['nonVat']}', '{$_POST['total']}', '{$_POST['bookType']}', '{$_POST['cash']}', '{$_POST['account']}', '{$_POST['termsMonths']}', '{$_POST['termsDays']}',)";
    $result = mysqli_query($con, $query);

    echo "Add document successful.";
?>

With or without the $query = "USE accting"; command, the entries still aren't added to the database despite the page printing Add Document Successful. What's wrong here?

Answer 1

The issue is likely to be the trailing comma in the VALUES part.

You should also consider an alternative way to pass variables in as there's an SQL Injection vulnerability there. Here's an example:

$stmt = mysqli_prepare($link, "INSERT INTO table VALUES (?, ?)");
mysqli_stmt_bind_param($stmt, "ii", $_POST['x'], $_POST['y']);
mysqli_stmt_execute($stmt);

Answer 2

There are a number of things wrong here. I suspect it's failing because there's a trailing comma within your VALUES braces. However, the most worrying thing for me is that it looks like you're pumping user input direct from the POST array straight into the database, making this code vulnerable to SQL injection.

See mysqli_real_escape_string() for more information on that.

Also, mysqli_query() returns false upon failure, so I'd check for this and if it happens call mysqli_error() to find out what went wrong. Only if mysqli_query doesn't return false should you announce that the document has been added successfully!

Answer 3

You need to dump the $_POST array to see if any data is really being sent.

session_start();
var_dump($_POST);

Then echo your sql as well after each SQL call

echo $query;

Finally, you really should validate the inputs ($_POST array) before inserting into the db, as already stated because of sql injection risk.