First, I should say that I am a complete novice with mysql. I have been using it for years with websites and know how to change records, upload, download, etc., but I don't anything about creating syntax to perform tasks. Please pardon my ignorance.
I have a task that I need to get done. I used MS Excel to create a spreadsheet of all the cities in the US and then put phrases with them. I've got like 20,000 rows.
But I have like 450 rows on another sheet that need to be inserted below each row while simultaneously duplicating the existing data.
spreadsheet 1 (contains 20,000 rows)
row 1 - johnson alaska
row 2 - thompson alaska
row 3 - smith alaska
row 4 - zebulon alaska
etc to 20,000
spreadsheet 2 (contains 450 rows)
row 1 - football
row 2 - baseball
row 3 - golf
row 4 - basketball
etc to 450
But need to have it look like this...
row 1 - football johnson alaska
row 2 - baseball johnson alaska
row 3 - golf johnson alaska
row 4 - basketball johnson alaska
etc to 450
yes you can do this with mysql -- it's called a cross join
load your two sheets into two separate tables, then do this --
FROM cities AS table1
JOIN spamwords AS table2
I actually just started doing some searching on the forum and found the command cross join and had just started reading about it. Thanks for the help. I really appreciate it. I hate seeing people go onto forums and requesting free code, but that's exactly what I ended up doing... go figure.
Thanks for the help, but my hosted shared server wouldn't handle that big of a process... So...
I had to download and install apache, mysql, php, and phpmyadmin. Spent all day and almost all night on it. Finally had to go to sleep. Got up this morning and still couldn't get phpmyadmin to go to login screen. Ultimately had to change the Path so that c:\php; was in front of the rest of the stuff in Path. That's what finally got it to work. Now I'm ready to handle this cross join business locally (which I should have done before).
This cross join should give me approximately 9 million records - should I increase the timeout level on php.ini from the standard amount?
But it would need to be able to add the contents of table 1 to each row and the contents of table 2 to each row. And table 1 would consist of only 1 row (it wouldn't be changing) and table 4 would only consist of 1 row.
If you can at least tell me what the command would be (like cross join), I can possibly figure out the code.
And then, I think this is the final question... Is there a way to easily break up the query results into groups of 4000 during the export?
Thanks for all the help.
Last edited by fencerenu; 11-08-11 at 15:54.
Reason: clean up tables
Okay... So... I ran into an error trying to download, so I had to increase some things in php.ini to increase file size, execution time, etc.
Got that handled. Thought I would just download the whole thing and use notepad2 to snag chunks of 4000 at a time. Nope... file is too large. Tried other programs, too. But won't work.
So I guess the only way for me to do this is probably the 'right way.' download it in chunks of 4000 during the query. I wish I had a program, a robot, a human being, or even a smart monkey to do this for me... such a daunting task...
Do you know of any way to tell phpmyadmin to name the downloaded file differently than the default? Right now it's giving the same name to each file when exporting. I don't know how to rename it right there.
4000 row limit comes from the server timeout on our shared hosting - the files are uploaded via a plugin that automatically turns them into complete web pages. 5000 was too many, so we lowered it to 4500, and that didn't work, and then we lowered it to 4000, and that has worked every time.
So the problem is the small amount of data gets uploaded as a .csv, .xls, or .xlsx file using the plugin, and it does whatever it needs to do to convert it into a complete page. I hope that makes sense. It automatically assigns it to a category, a section, gives it a page id, makes the title from what I upload and adds some content from what I upload. It's a pretty cool program. Would be cooler if I hosted my own website.
So I did try to download everything in one go, but I just can't do anything with the data. I can't find a program to open it while also showing line numbers. Well I just can't find a program to open it. If I could find a program to open it, then I could copy 4000 lines and paste them into excel or open office and save that file and upload it. I figure it'll take about 2 weeks of 10 hour days... not so bad in the overall scheme of things.
Do you have any ideas of any text editors to use that can handle something like 1gb of data? I can use it on an 8gb core 2 quad running windows 7.
Hey Rudy... Thanks for the help. I took a look at your book page, but didn't see anything there...
Anyway... On the export screen, of phpmyadmin, if you click on the "custom export" display all available options, then you can easily set the number of rows, beginning row, and some other cool options that will be helpful for me. I'm sure you already know about this, but I'm just posting it for anybody who might be googling this same subject down the road and could benefit from what I've learned here over the last couple of days.