I'm trying to teach myself how to use mysql and php via a couple of books and a lot of hacking away.
I have Jesse Feiler's book "How to Do everything with Web 2.0 Mashups" and I'm stuck on chapter 11 which is a practical exercise in mashup building. The game is to download data files from the US govt websites about US counties and employment stats and then load them into SQL tables before designing an interface and starting page etc. to make a basic mashup.
OK, so I've dowloaded the two data files and I have to get the data into appropriate MYSQL tables.
The first file is done. I managed to transfer the data into the appropriate table in the MySQL database by first converting it to a csv file and then uploading with phpmyadmin (using the IMPORT tab rather than writing out full SQL commands). Eventually this worked OK.
The second file is more complex because, as the author points out, some data "munging" needs to be done, so the code for the MySQL command looks like this:
load data infile local 'dc_dec_2000_sf1_u_data1.txt' into table population fields terminated by '|' lines terminated by '\n' ignore 2 lines (@var1, @geocode, @var2, County_Name, TotalPopulation, TotalMen, TotalWomen) set State_FIPS_Code=left(@geocode, 2), County_FIPS_Code=right(@geocode, 3)
So, being more complex - I think I'm better to use fully typed-out commands cos I wouldn't know where to find the easy drop-down boxes to cope with everything that's in that command.
But the command aint working (error 1064) and I get the feeling that the problem may be the path to the file. How exactly do you write the syntax for a file path that's on your computer?
Which one of these is most likely to be correct?
- load data infile local 'C:/dc_dec_2000_sf1_u_data1.txt'
- load data infile local 'C:\dc_dec_2000_sf1_u_data1.txt'
- load data infile local 'dc_dec_2000_sf1_u_data1.txt'
And does "local" mean my laptop here or does it mean the webhosting server? or have I missed something else?
By the way, when the command fails, the error message puts LEFT and RIGHT in red. Is this signifcant?
Thanks to anyone who knows what I'm talking about and can help:)