Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064

Question

I'm making a chat and I stumbled across an error. The error is:

Fatal error: Uncaught exception 'PDOException' with message '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 'INSERT INTO guildchat(guildID, playerID, message, `time`, chattime) VALUES(?, ?,' at line 1' in C:\xampp\htdocs\sf\sexyutility.php:14 Stack trace:#0 C:\xampp\htdocs\sf\sexyutility.php(14): PDO->prepare('SELECT @cht := ...')#1 C:\xampp\htdocs\req.php(2659): chatInsert('tewtewt', 53, 35)#2 {main} thrown in C:\xampp\htdocs\sf\sexyutility.php on line 14

The code is:

function chatInsert($message, $guild, $player){
    $time = time();
    $chattime = $GLOBALS['db']->prepare("SELECT @cht := Max(chattime) AS chattimer FROM guildchat WHERE guildID = :guild; INSERT INTO guildchat(guildID, playerID, message, `time`, chattime) VALUES(:guild, :player, :msg, :timers, @cht + 1)");
    $chattime->bindParam(":guild", $guild);
    $chattime->bindParam(":player", $player);
    $chattime->bindParam(":msg", $message);
    $chattime->bindParam(":timers", $time);
    $chattime->execute();
    return $chattime->fetch(PDO::FETCH_ASSOC)['chattimer'] + 1;
}

The error says your insert query has a syntax error. Now what's your question? — Charlotte Dunois
– Charlotte Dunois, Commented Apr 17, 2016 at 14:59
There's no guarantee that another running script won't insert a row with the same chattime between your SELECT and INSERT queries. You should investigate transactions and isolation levels if this is a concern. — Matt Raines
– Matt Raines, Commented Apr 17, 2016 at 15:35

Community · Accepted Answer · 2017-05-23 12:16:00Z

0

~~you can only do one query at a time with PDO prepare. you're running two. you will have to do it in 2 separate statements.~~

I guess I was wrong. Instead of removing the answer, I'll leave this link here as a reference.

PDO Support for multiple queries

It's supported, but with a few caveats.

edited May 23, 2017 at 12:16

CommunityBot

11 silver badge

answered Apr 17, 2016 at 15:02

I wrestled a bear once.

23.5k20 gold badges74 silver badges122 bronze badges

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

1 Comment

Matt Raines Over a year ago

According to PDO support for multiple queries (PDO_MYSQL, PDO_MYSQLND), PDO does in fact support more than one query at a time. Indeed, the OP's code works fine for me. However the syntax error does imply that this is the problem here...

Collectives™ on Stack Overflow

Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related