PHP halted on mySQL error, however the INSERT has been recorded into database

Question

As the title says, I get BOTH an error and the INSERT has been recorded/accepted. here is the mySQL:

$anvSQL = "
    INSERT INTO members (login, date) 
    VALUES('GRFU25F9J8P5P3rYIDUP', '2014-11-11 09:59:56')
";

if (!mysqli_query($con, $anvSQL))           
{
    die('Error: ' . mysqli_error($con));
}       
else
{
    echo "<br>1 record added";
}

Field login is a text and date is in datetime format. And here is the complete error message:

1 record added Error: 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 ' '2014-11-11 09:59:56')' at line 1

The query seems fine. Do you have a trigger running on that table? — juergen d, Nov 11 '14 at 09:15
Can you really use `date` as a column name without quoting it using backticks? — ThiefMaster, Nov 11 '14 at 09:16
@ThiefMaster: Sure - it is not a reserved word: http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html — juergen d, Nov 11 '14 at 09:17
And since the insert works, it must be either another insert or a trigger running from that insert. — juergen d, Nov 11 '14 at 09:18
@ThiefMaster MySQL permits some keywords to be used as unquoted identifiers because many people previously used them. `date` is one of them — , Nov 11 '14 at 09:18
Passing in following format `yyyy-mm-dd hh:mm:ss` . If we use only date format it mayn't accept `hh:mm:sss` @Begueradj — Mahendra Jella, Nov 11 '14 at 09:24
So, first, try to eliminate the PHP as the problem—can you reproduce the error using a MySQL client directly? (If not, is it possible you've managed to paste some kind of strange character into your query in the PHP editor? I've seen this kind of oddity before, and something like a weird invisible character pasted in in the "space" between `'GRFU25F9J8P5P3rYIDUP',` and `'2014-11-11 09:59:56'` might cause it.) — Matt Gibson, Nov 11 '14 at 09:30
(Also, there's no way with the code you've shown that you could possibly get "1 record added" *and* a syntax error out, especially in the order you've shown them... What's going on in the code you're not showing us?) — Matt Gibson, Nov 11 '14 at 09:37

score 0 · Answer 1 · edited May 23 '17 at 12:15

0

I used exactly your schema and it works (MySQL server version: 5.5.40)

mysql> create table members(login text, date datetime);
Query OK, 0 rows affected (0.40 sec)

mysql> INSERT INTO members (login, date) 
    ->     VALUES('GRFU25F9J8P5P3rYIDUP', '2014-11-11 09:59:56');
Query OK, 1 row affected (0.28 sec)

Even if the official documentation states about date that:

MySQL permits some keywords to be used as unquoted identifiers because many people previously used them.

It is useful to rename your column name as it is mentioned in the answer of this question.

edited May 23 '17 at 12:15

Community

1
1

answered Nov 11 '14 at 09:27

I put backsticks over the field names, but it didn´t help. I will try to change the field names now. – Mansari Nov 11 '14 at 09:43
have you defined the table columns as I did (I run that according to your question). Rename your date column. Also, it is nice to mention your MySQL version. @Mansari – Nov 11 '14 at 09:45
PHP version 5.2.5 . Also changing the names aren´t helping. I think its something else. – Mansari Nov 11 '14 at 09:59
after renaming the column, did you get the same error ? – Nov 11 '14 at 10:02
I found out what the problem was. I have several "include" pages in the main page, which are doing different tasks. One of them rendered the error message in the "wrong" place. Now I have sorted the tasks in the right way. Thank you guys for your considerations. – Mansari Nov 12 '14 at 11:42

PHP halted on mySQL error, however the INSERT has been recorded into database

1 Answers1