Insert into database (SQL syntax error)

Question

<?php 

require "connect.php";

$file = "./myFile2.txt";
$document = file_get_contents($file);

$lines = explode("\n",$document);




foreach($lines as $newline) { 
    $arr = explode(': ', $lines[0]);
    $order = $arr[1];

    /* echo $order.'<br>'; */
}

foreach($lines as $newline){ 
    $art = explode(': ', $newline);
    $total = $art[1]; 

    echo $total.'<br>';

    $sql = "USE receipts INSERT INTO receipt_content (total_price) VALUES ($total)";

    if (mysqli_query($conn, $sql)) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

}

?>

When attempting to add the echoed information into my database (phpmyadmin) [MariaDB server], I get the following error:

Error: USE receipts INSERT INTO receipt_content (total_price) VALUES (12364 ) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INSERT INTO receipt_content (total_price) VALUES (12364 )' at line 1

600$

Error: USE receipts INSERT INTO receipt_content (total_price) VALUES (600$) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INSERT INTO receipt_content (total_price) VALUES (600$)' at line 1

I have a database called 'receipts' with table 'receipt_content', containing column 'total_price' and 'order_number'.

How can i fix the error to properly insert the parsed data ('12364' and '600$') into the columns of the tables of my database.

`USE` and `INSERT` are separate commands, and need to be executed separately. Or you can specify the database to use in the initial connection. — iainn, Oct 31 '16 at 11:52
code should be mysqli_select_db($conn,'receipts'); and $sql = "INSERT INTO receipt_content (total_price) VALUES ($total)"; — JYoThI, Oct 31 '16 at 11:52
Agree with @nospor. You would select your db "receipts" in I assume your connect.php. Remove the USE receipts. You're not connecting via the command line here. — Chris Coppenbarger, Oct 31 '16 at 11:54
We had the same question often over the last days. Mass insert scenarions. Most likely some course or similar. Those are text files holding sql commands that are not separated by the required semicolon. That's all. — arkascha, Oct 31 '16 at 11:55
agree with @devpro and use $sql="USE receipts; other query;" mysqli_multi_query($conn,$sql) — JYoThI, Oct 31 '16 at 11:57
[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)*** Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! [Don't believe it?](http://stackoverflow.com/q/38297105/1011527) — Jay Blanchard, Oct 31 '16 at 12:00

score 0 · Answer 1 · answered Oct 31 '16 at 12:14

As people mentioned in the replies, you should select the database before the query, not together with the query. (Eg. select the db when opening the connection)

Also, you should use binding, either the one provided in mysqli, or even easier is PDO.

http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers

PDO allows name-based bindings and will perform all escaping and quoting for you.

$stmt = $db->prepare("INSERT INTO receipt_content VALUES(:total)");
$stmt->bindParam(':total', $total);
$stmt->execute();

Alternatively, you can prefix the table name with the db name: `INSERT INTO receipts.receipt_content...`, but that gets tedious. — Rick James, Nov 01 '16 at 01:29

Insert into database (SQL syntax error)

1 Answers1