SQL error when trying to create a tabel and populate it

Question

I'm getting this SQL error:

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 `tsv` (`Id`,`Date`,`Quantity`) VALUES( `1`,`2009-07-01`,`174`, `1`,`' at line 9

I'm not really experienced in SQL queries and i really can´t see whats wrong here... I tried replacing "`" with "'". And also removing "ENGINE=MyISAM" as I'm not sure thats the engine I'm using.

CREATE TABLE IF NOT EXISTS `tsv` 
( `_id` int(11) NOT NULL AUTO_INCREMENT, 
`Id` text NOT NULL, `Date` text NOT NULL, 
`Quantity` text NOT NULL, PRIMARY KEY (`_id`) ) 
ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=68 ; 
INSERT INTO `tsv` (`Id`,`Date`,`Quantity`) 
VALUES( `1`,`2009-07-01`,`174`, `1`,`2009-07-02`,`96`, 
`1`,`2009-07-03`,`271`, `1`,`2009-07-04`,`335`, `1`,
`2009-07-06`,`72`, `1`,`2009-07-07`,`246`, `1`,`2009-07-08`,
`93`, `1`,`2009-07-09`,`191`, `1`,`2009-07-10`,`136`, `1`,
`2009-07-11`,`200`, `1`,`2009-07-13`,`151`, `1`,`2009-07-15`,`99`);

And the PHP:

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 
echo "Connected successfully";
if ($conn->query($sqlContent) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sqlContent . "<br/>" . $conn->error;
}

$conn->close();

You need to separate the two queries or use [`mysqli::multi_query`](http://php.net/manual/en/mysqli.multi-query.php). — Jeto, Sep 29 '18 at 19:38
@dnFer Done. Didn't feel like elaborating, but I guess that should help a bit as is :) — Jeto, Sep 29 '18 at 19:57

Madhur Bhaiya · Accepted Answer · 2018-09-29T20:04:11.240

Every row must be enclosed in parantheses separately.
You need to use single quotes ('), not backticks (`) for values.
Also, you cannot have separate queries called inside mysqli_query. Use mysqli_multi_query for the same. From documentation:

Executes one or multiple queries which are concatenated by a semicolon.

Also, to prevent against SQL injection related issues, please use Prepared Statements.

Do the following:

CREATE TABLE IF NOT EXISTS `tsv` 
( `_id` int(11) NOT NULL AUTO_INCREMENT, 
`Id` text NOT NULL, `Date` text NOT NULL, 
`Quantity` text NOT NULL, PRIMARY KEY (`_id`) ) 
ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=68 ; 

INSERT INTO `tsv` (`Id`,`Date`,`Quantity`) 
VALUES('1','2009-07-01','174'), 
      ('1','2009-07-02','96'),
      ('1','2009-07-03','271'), ... and so on

@JonasB happy to help :) Happy coding! – Madhur Bhaiya Sep 29 '18 at 20:04 — Madhur Bhaiya, Sep 29 '18 at 20:04

Chayan · Answer 2 · 2018-09-29T19:50:18.040

1

You do not need to provide id. Id will auto increment while inserting data.

INSERT INTO tsv ('Date','Quantity') 
VALUES( '2009-07-01','174'), ('2009-07-02','96'), 
('2009-07-03','271'), ('2009-07-04','335'), 
('2009-07-06','72'), ('2009-07-07','246'), ('2009-07-08',
'93'), (,'2009-07-09','191'), ('2009-07-10','136'), 
('2009-07-11','200'), ('2009-07-13','151'), ('2009-07-15','99');

edited Sep 29 '18 at 19:50

answered Sep 29 '18 at 19:43

Chayan

604
5
12

score 1 · Answer 3 · answered Sep 29 '18 at 19:57

1

You cannot have several queries be part of the same mysqli::query call.

You need to separate the two queries or use mysqli::multi_query.

SQL error when trying to create a tabel and populate it

3 Answers3