SQLSTATE[42000]: Syntax error while syntax is OK

Question

I'm really puzzled by error that comes from my simple insert. I've checked the syntax many times by different checkers and searched for similar troubles but haven't found solution.

The Error looks like this:

'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' , , , , , , , , , , , , , , )' at line 1' in

And my code is basically this:

$yhteys = new PDO('mysql:host=localhost;dbname=XXXX', 'YYYY', 'ZZZZ');
$kysely = $yhteys->prepare("INSERT INTO hakija (Kutsumanimi, Etunimet, Sukunimi, SyntymAika, Syntymapaikka, Sahkoposti, Puhelinnumero, Postiosoite, Postinumero, Postitoimipaikka, Maa, Suosittelija, IPos, Lahetysaika, Vapaa_sana, Sosme) VALUES ($nimi, $etunimet, $sukunimi, $saika, $spaikka, $email, $puhelin, $osoite, $postinro, $postitmp, $maa, $suosittelija, $IPos, $lahetysaika, $vapaasana, $sosme)");
$kysely->execute();

If I use this INSERT directly via phpMyAdmin, it works, but from php.. Can anyone help me out?

PHP: native (5.4) MySQL 5.6

Are you sure your variables are not empty? it looks like this, but I didn't reproduce it.. And also consider the two answers: You have to encapsulate strings with single quots, and overall you should use prepared statements (for security and convenience). — Stefan Woehrer
– Stefan Woehrer, Commented Nov 3, 2015 at 7:57
Not sure, some of the variables might be empty since they not required. — tlaxin
– tlaxin, Commented Nov 3, 2015 at 8:08
ok. use the prepared statement as RafH suggested. this way it should be ok if they are empty, plus it's a must for security reasons (stackoverflow.com/questions/732561/…) — Stefan Woehrer
– Stefan Woehrer, Commented Nov 3, 2015 at 8:37

RafH · Accepted Answer · 2015-11-03 07:52:18Z

1

You should use prepared statements. It will prevent sql injections and you wont have to deal with variables types

$yhteys = $dbh->prepare("INSERT INTO hakija (Kutsumanimi, Etunimet,...) VALUES (:kutsumanimi, :ktunimet, ...)");
$yhteys ->bindParam(':kutsumanimi', $kutsumanimi);
$yhteys ->bindParam(':ktunimet', $ktunimet);
...
$yhteys ->execute();

Have a look here : http://php.net/manual/en/pdo.prepared-statements.php

answered Nov 3, 2015 at 7:52

RafH

4,5442 gold badges25 silver badges23 bronze badges

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

1 Comment

tlaxin Over a year ago

Thank you, this was the solution.

undefined_variable · Accepted Answer · 2015-11-03 07:02:05Z

0

If values you are inserting are Strings you need to enclose it in quotes

$kysely = $yhteys->prepare("INSERT INTO hakija (Kutsumanimi, Etunimet, Sukunimi, SyntymAika, Syntymapaikka, Sahkoposti, Puhelinnumero, Postiosoite, Postinumero, Postitoimipaikka, Maa, Suosittelija, IPos, Lahetysaika, Vapaa_sana, Sosme) VALUES ('$nimi', '$etunimet', '$sukunimi', '$saika', '$spaikka', '$email', '$puhelin', '$osoite', '$postinro', '$postitmp', '$maa', '$suosittelija', '$IPos', '$lahetysaika', '$vapaasana', '$sosme')");

if values are integer you can skip quotes

answered Nov 3, 2015 at 7:02

undefined_variable

6,2282 gold badges24 silver badges38 bronze badges

Collectives™ on Stack Overflow

SQLSTATE[42000]: Syntax error while syntax is OK

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related