0

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.

2

The problem with the query is not to do with the colon, but rather the quote characters you're using. Instead of , use '.

  • Thanks, that was it. Wasn't a problem in my PHP code, but for some reason when I typed it out with my iPad in phpMyAdmin it was using the wrong type of single quote. – Luke Jan 14 at 11:15
  • The issue in PHP was unrelated to the query in the end. Had the wrong database name in the mysql_select_db call. – Luke Jan 14 at 11:16

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.