Topic: Spreadsheet to MySQL - correct syntax for URLs?

Trying to export from ods spreadsheet into mysql via phpmyadmin

[code=sql]
="INSERT INTO `spamurls` VALUES ("&A2&","&B2&","&C2&","&D2&","&E2&","&F2&""&");"
[/code]

like this;  but  2 probs
1. Date is not in date format?
2. [code]://[/code] causes problem - any idea how to solve?

[code=sql]
INSERT INTO `spamurls` VALUES (2,http://www.mindmagiclive.com/,magicians toronto ,1,40409,S);
INSERT INTO `spamurls` VALUES (3,http://mobilemonopolyreview.easyonlinejobsreview.com/,mobile monopoly review ,1,40409,S);
INSERT INTO `spamurls` VALUES (4,http://doubleglazingleeds.org,double glazing leeds ,1,40409,S);
INSERT INTO `spamurls` VALUES (5,http://www.wordsworth.co.uk,industrial PC ,1,40409,S);
INSERT INTO `spamurls` VALUES (6,http://www.cheapipodnano.org,Cheap iPod Nano ,1,40409,S);
INSERT INTO `spamurls` VALUES (7,http://www.semenaxsale.com,semenax review ,1,40409,S);
[/code]

this is original code in spreadsheet:

[code]
2    http://www.mindmagiclive.com/    magicians toronto     1    19.08.10    S
3    http://mobilemonopolyreview.easyonlinejobsreview.com/    mobile monopoly review     1    19.08.10    S
4    http://doubleglazingleeds.org    double glazing leeds     1    19.08.10    S
5    http://www.wordsworth.co.uk    industrial PC     1    19.08.10    S
6    http://www.cheapipodnano.org    Cheap iPod Nano     1    19.08.10    S
7    http://www.semenaxsale.com    semenax review     1    19.08.10    S
[/code]

The SQL I used to create table

[code=sql]
CREATE TABLE `spamurls` (
  `id` int(11) NOT NULL auto_increment,
  `url` varchar(150) NOT NULL default '',
  `keyword` varchar(100) NOT NULL default '',
  `position` varchar(4) NOT NULL default '',
  `date` varchar(12) NOT NULL default '',
  `type` varchar(25) NOT NULL default '',
  PRIMARY KEY (`id`)
) TYPE=MyISAM
[/code]


Error I'm getting
[code=sql]
#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 '://www.mindmagiclive.com/,magicians toronto ,1,40409,S)' at line 1
[/code]

Re: Spreadsheet to MySQL - correct syntax for URLs?

If you have selected date as the format for your table then it needs to be in this format


2010-08-19

you other issue is because strings need to be enclosed by ""

Hope this helps!

Re: Spreadsheet to MySQL - correct syntax for URLs?

Thanks, yes  - I needed to add ' to the string values... so in my insert command....

'"&B&"'

instead of

"&B&"

RE: Date

`date` varchar(12) NOT NULL DEFAULT '',

I'll try and get the cell to show what you just put in as date...

Re: Spreadsheet to MySQL - correct syntax for URLs?

 `date` varchar(12) NOT NULL DEFAULT '',

should probably replaced with

 `date` datetime NOT NULL DEFAULT '',

when creating table...

Re: Spreadsheet to MySQL - correct syntax for URLs?

yes then you may find you need to include the time as well

so

2010-08-19 00:00:00

Though I am not sure if you could get away with just the date part

Re: Spreadsheet to MySQL - correct syntax for URLs?

Problem is:
I've changed the date format in the spreadsheet... but when I use in my spreadsheet and this code:

="INSERT INTO `spamurls` VALUES ("&A2&","&B2&","&C2&","&D2&","&E2&","&F2&""&");"

that cell doesn't know it's a date, it has regular formatting....

Re: Spreadsheet to MySQL - correct syntax for URLs?

yes it doesn't matter that it doesn't know it is a date though it does need to be in the datetime format which is

YYYY-MM-DD HH:MM:SS

So you need to really format your spreadsheet to accommodate the date looking exactly like that, or if you have to have your date as per your past examples you will have to store them as a string which will need the '' or "" around it!

Hope this helps!

Re: Spreadsheet to MySQL - correct syntax for URLs?

I think if you are in Excel you can change to a custom field specifying exactly what I put above and it will convert all your dates to this format(I think)

Re: Spreadsheet to MySQL - correct syntax for URLs?

I'm just asking in the openoffice forum aswell... as it's not budging on the absolute number in my code... will let you know if and when it's resolved.... thanks in the meantime

Re: Spreadsheet to MySQL - correct syntax for URLs?

I do not have openoffice on this PC but if you do not get any joy then let me know as I do have it on my home PC that I will be on tomorrow! big_smile

11

Re: Spreadsheet to MySQL - correct syntax for URLs?

replace E2 with TEXT(E2;"yyyy-mm-dd") or whatever date format you like.

was the solution!

I only use OpenOffice now and am quite happy... also that quick response in their support forum really prooves you don't need MS big_smile

Re: Spreadsheet to MySQL - correct syntax for URLs?

Excellent! I am pleased that you found a solution!