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?

You don't need the USE command when you connect specifically to that database. — calcinai
– calcinai, Commented Oct 25, 2013 at 0:30
echo $query; ....you can test the query in that way, and see if the values are correct. — Hackerman
– Hackerman, Commented Oct 25, 2013 at 0:30
I changed it the USE command to specify the table name, but it still doesn't work. — Gannicus
– Gannicus, Commented Oct 25, 2013 at 0:31

calcinai · Accepted Answer · 2013-10-25 00:32:01Z

2

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);

answered Oct 25, 2013 at 0:32

calcinai

2,62716 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Gannicus Over a year ago

Right on, there's an excessive comma in the VALUES part. Yes, I'll look into SQL injection as well as this is my first time building a website.. thank you!

Gannicus Over a year ago

What does "ii" mean here?

calcinai Over a year ago

Look at the manual, it's the way you specify data types.

Shankar Narayana Damodaran · Accepted Answer · 2013-11-09 16:32:44Z

1

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!

edited Nov 9, 2013 at 16:32

Shankar Narayana Damodaran

68.6k43 gold badges102 silver badges129 bronze badges

answered Oct 25, 2013 at 0:38

Neil Mukerji

412 bronze badges

Comments

ssaltman · Accepted Answer · 2013-10-25 00:42:12Z

0

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.

answered Oct 25, 2013 at 0:42

ssaltman

3,7231 gold badge20 silver badges21 bronze badges

Collectives™ on Stack Overflow

Can't make INSERT INTO sql database table work

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related