homepage Welcome to WebmasterWorld Guest from 54.211.231.221
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Accredited PayPal World Seller

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Insert Hell
Problems inserting large amounts of data through SQL window of MyPHPAdmin
marcobraynio




msg:3247164
 3:46 am on Feb 9, 2007 (gmt 0)

Hi

I just can't get my head round this.

I am using phpMyAdmin to set up and manage a database, but I'm having real problems, trying to INSERT data into a table through the SQL windows.

Now here's the strange part - if I type the data directly into the sql window it runs the query fine and adds the data to the table. However, if I paste the data from another file in exactly the same format it comes up with a syntax error?

#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 'Africa’, ‘20mm’, 258.5, 49.5),(NULL, ‘Nero Africa’, ‘30mm’, 291.5,' at line 1

If I use the MySQL prompt, it works fine too, but I can't copy and paste to this. I am 99.99999% certain there is nothing wrong with the syntax.

I could input all by hand but that would take forever. Please help before I am forced to get a paper round.

Thanks

Mark

 

mipapage




msg:3248445
 12:58 pm on Feb 10, 2007 (gmt 0)

Looks to me like you have a character encoding issue. The text you are copying, what encoding type is it?

sabai




msg:3248534
 3:34 pm on Feb 10, 2007 (gmt 0)

If you're using unix/linux you can do this:

mysql -u databse_user -p database_name < file_with_queries.sql

marcobraynio




msg:3251944
 1:53 am on Feb 14, 2007 (gmt 0)

Hi

I'm just using MS Word as a text editor and copying and pasting accross the table data with SQL INSERT statement.

mipapage




msg:3252216
 9:14 am on Feb 14, 2007 (gmt 0)

Well, that could be your answer. Try and use something that isn't MS Word, and that supports UTF-8 and the like. I'm pretty positive that your issue has to do with character encoding. Dreamweaver supports UTF-8 as do many html editors.

LifeinAsia




msg:3252556
 4:51 pm on Feb 14, 2007 (gmt 0)

Save the Word file to an Encoded Text file. Open that file in Notepad, then try cutting and pasting.

henry0




msg:3256037
 1:10 pm on Feb 18, 2007 (gmt 0)

Like LifeInAsia, I always paste it in my editor (UltraEdit) then copy and paste it in phpMyAdmin

your will encounter the same problem when copying and pasting from MS to many CMS Rich-text editors.

The Contractor




msg:3256059
 1:51 pm on Feb 18, 2007 (gmt 0)

Not to jump on the band-wagon, but the above replies are correct. You CANNOT use a rich text editor to copy/paste. Buy something like UltraEdit or TextPad..it's worth it.

marcobraynio




msg:3256551
 4:27 am on Feb 19, 2007 (gmt 0)

Hi Chaps - thanks for the responses. Have tried using Ultra Edit & Dreamweaver and still getting the following, when copying and pasting or importing. Any further suggestions would be welcome.

#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 'Africa’, ‘20mm’, 258.5, 49.5),
(NULL, ‘Nero Africa’, ‘30mm’, 291.' at line 2

AlexK




msg:3256585
 6:14 am on Feb 19, 2007 (gmt 0)

Hello marcobraynio, Welcome to WebmasterWorld!

I'm having real problems, trying to INSERT data ... it comes up with a syntax error
#1064 - You have an error in your SQL syntax;

It may help you if I set out the steps that I took to find this error:

  1. At the MySQL 3.23, 4.0, 4.1 Reference Manual [dev.mysql.com] I put in "1064 INSERT" into the search box.
  2. Choosing `all of the words` and searching again, then clicking on the first link (of just 3) gave this page [dev.mysql.com]
  3. The reference on that page for 'Error 1064' is: "Error: 1064 SQLSTATE: 42000 (ER_PARSE_ERROR)"
Note: The fact that the error you copied does not have a `SQLSTATE` suggests that your copy of MySQL is prior to v4.1. If so, that means that the suggestions above will not help you, since they refer to the internal changes made at v4.1 (all text is stored in UTF8, whereas your Windows system will use cp1252, or something similar).

So, you have a simple parse-error, and I reckon that I know what it is:

#1064 ... an error in your SQL syntax; check ... near 'Africa’, ‘20mm’, 258.5, 49.5),
(NULL, ‘Nero Africa’, ‘30mm’, 291.' at line 2

If I put the wrong line below what it should be, it should then be obvious what is wrong:

(NULL, "Africa", "20mm", 258.5, 49.5)
(NULL, ‘Africa’, ‘20mm’, 258.5, 49.5)

****************************************
Your problem is MS Word Smart Quotes
****************************************

Word will take `"Africa"` and turn it into `‘Africa’` (default action). For some reason, MySQL will not recognise Smart Quotes as valid text delimiters (I don't know why).

What you need to do is to dig into your Word manual, and discover how to turn Smart Quotes off, re-save the info in that state, and re-paste from the new file.

[edited by: AlexK at 6:17 am (utc) on Feb. 19, 2007]

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
© Webmaster World 1996-2014 all rights reserved