I have a database on a hosting service that was recently move and upgraded without much notice which has caused me many problems. This problem, however, seems to arise from an upgrade from MySQL to MariaDB 5.5.
I have a table called “Gigs” which stores date, time, Location, description etc. of some upcoming gigs/concerts. It’s an old database I created in my youth, and so time is implemented as a string field.
The PHP code of the website generates a time string using a colon to separate hours and minutes, for example 16:40, it then generates an INSERT query like this example:
INSERT INTO Gigs VALUES (NULL, ‘16:40’, ‘Test’, ‘Location’, NULL, ‘2019-01-09’)
I had no problems with this query until the migration and upgrade of the hosting.
My PHP code was silently failing, so I attempted to execute this SQL on the database directly using phpMyAdmin, and this is the message I got back:
#1064 - 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 ':40’, ‘Test’, ‘Location’, NULL, ‘2019-01-09’)' at line 1
It seems as though the colon is causing an issue? Is anybody able to help? I cannot find mention of colon as a special character in MariaDB documentation, nor any way to escape it.
Best Answer
The problem with the query is not to do with the colon, but rather the quote characters you're using. Instead of
‘, use'.