Does this SQL error mean anything to anyone?

http://www.emaculation.com/steve/error.jpg

I'm trying to restore a MySQL database which I got from phpMyAdmin using the 'Export' thing.

The database is for a phpBB forum, and some of the text in the error is from a topic posted a few years ago.

Opening the database in MS Word, the error would appear to be located somewhere here:

http://www.emaculation.com/steve/sql.txt

Does anyone know what the error is and how to fix it?

Thanks
Steve
[Reply]

If you look at the error message its pointing you to the error.

Its taking some of the text as functions, i.e. today AND tommorow IS sunny. AND and IS are being taken as functions.

EDIT:- after a closer look, look at the (tm) bit, as that is causing problem, remove the ( ) and leave the tm. This is because the brackets open, then that tm bit opens another bracket and then closed straight after tm, making it think its a function thats ended, so everything after it is seen as functions. Hence the AND, IS, etc etc are in capitals.

It looks like it will error again by the phone no. listed in that post.

I know, I must sound like a right n00b, but im not, if the terminology is wrong, its because i have taught myself PHP and SQL. So I know what I mean, just hard to try and explain what I mean, .
[Reply]

As a non-coder I found that explanation easy to understand, FOL, which seems to indicate that you have a good understanding of the subject and a knack for passing that knowledge on to others.

I just wish my teachers at school were that good
[Reply]

Good good, lets hope that solves problem. I aint touched much PHP & SQL since I started messing with PSPUAE. Im sure thats what is causing problem though.
[Reply]

That all made sense to me. I don't know much about SQL. Although for some reason I did wonder if the (tm) bit might be a problem.

I will see if that fixes it tommorow.

Thanks!
[Reply]

The 1064 MySQL error is a general coding syntax error that appears when anything is wrongly formatted in the query you are trying to run. This is commonly if an end of line ; has been missed from PHP code or if a character hasn't been escaped correctly, confusing the script.

There are a few things that could be wrong with the syntax of the query you are trying to run. But on looking at your SQL query the first thing I noticed that I believe is the problem is that characters haven't been escaped correctly within the query. The use of single quotes ' and double quotes " within the SQL query's strings is what is causing the problems.

The (TM) mentioned by FOL is only a by-product of this error and not the cause.

If you look at your statement you can easily see what is happening. Look down to the line below the purple highlighted AND in the screenshot. The word Transitive's has been split at the 's point. This is because PHP is interpreting the single quote ' as the end of a string within the query and is therefore leaving and ending the string at this point and taking the next part as a new part of the code, and this is why the text past this point is then being highlighted as though it were php.

To get around this problem you need to escape all php characters that can cause this issue within your code's strings. To do this you escape characters by adding a backslash \ before all characters that need to have quotes in the database query.

So I recommend you try this. Go through your query and within the strings containing the forum text add a backslash before all instances of single quotes ('), double quotes ("), backslashes (\) and NUL.

So for example you would change Transitive's to Transitive\'s. This tells the PHP interpreter to ignore that single quote and continue. Try doing that and then rerunning the query.

I hope that fixes it for you.

Info regarding why this may of happened: The forum when saving this post into the database was probably coded to add and remove the backslash escape characters as it processes the data. But normally I would expect to see the backslash escape characters present within the SQL and the forum software to remove them using a "stripslashes" function when it is retrieving the data from the database. I'm not sure why it isn't coded like this. Unless the forum was using the old magic quotes method which is bad coding practice.
[Reply]

FFS, Harrison, I said it was ' first of all, but then edited cause i though it wasnt. So I should have gone with my original instint, .
[Reply]

I can try putting a \ behind the ' and ", but it would be a little tricky doing that with all the posts on the forum (assuming that they all have this problem - it could just be this post).

I don't understand why it has done this though. Last time I got the database from phpMyAdmin it uploaded without problems.
[Reply]

How did you export the database? Why are you needing to restore it?

Do you have SSH telnet shell access to the server you are restoring the database to? If so it would probably be better to bypass phpMyAdm completely and directly restore the database to MySQL via the commandline.

Doing this would be much faster and most likely without any of the errors you are experiencing. For example when I moved the main classicamiga site to the new server I did some local tests first to make sure it would restore correctly. First I created a new database and used phpMyAdmin to restore the database to the new one. It worked but took a long time and required me to answer some selection boxes along the way. In contrast I then tested it using the commandline and direct MySQL commands to restore the database directly to MySQL. This worked perfectly and finished in seconds! So when I actually moved the site, I uploaded the database backup to the server, then logged into the server using a SSH telnet shell session and restored this local backup directly to MySQL using the commandline. It was completed in seconds and worked without error.
[Reply]

Do you know how I could get access to the SQL server?
[Reply]