Results 1 to 15 of 15
  1. #1
    Join Date
    Nov 2011
    Posts
    21

    Unanswered: Can this be done in MySQL?

    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.

    EXISTING
    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

    row 451 football thompson alaska
    row 452 baseball thompson alaska
    row 453 golf thompson alaska
    row 454 basketball thompson alaska
    etc to 900

    etcetera...

    Does this make sense? I couldn't find a simple way to do it in Excel, and I'm not well versed in their macros.

    I figured mySQL might be the answer.

    I'm sorry to ask this, because I know this is me basically asking for free help, but I am just completely stuck.

    Can anyone show me a query that can get this done in mySQL? Thanks for any help you can provide.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes you can do this with mysql -- it's called a cross join

    load your two sheets into two separate tables, then do this --
    Code:
    SELECT table1.city
         , table2.spamword
      FROM cities AS table1
    CROSS
      JOIN spamwords AS table2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2011
    Posts
    21

    Thanks!

    Quote Originally Posted by r937 View Post
    yes you can do this with mysql -- it's called a cross join

    load your two sheets into two separate tables, then do this --
    Code:
    SELECT table1.city
         , table2.spamword
      FROM cities AS table1
    CROSS
      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.

  4. #4
    Join Date
    Nov 2011
    Posts
    21

    Thanks for the help, but...

    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?

    Thanks for the help.

  5. #5
    Join Date
    Nov 2011
    Posts
    21

    almost all went well...

    The code worked perfectly. But now there are almost 9 million records and excel only allows you to open a file that's a little over a million records in size. dang... BUT...

    mysql handled the task in a fraction of a second.

    Is there any type of code that would allow the cross join for that query that I already got to work, while adding the contents of a table in front of the query and table after the query?

    Would look like this:
    Table 1 | Table 2 | Table 3 | table 4
    blah blah blah | cities 1 | sports1 | blah blah blah
    blah blah blah | cities 2 | sports2 | blah blah blah
    blah blah blah | cities 3 | sports3 | blah blah blah

    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 16:54. Reason: clean up tables

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if table 1 and 4 only contain 1 row each, don't bother with them
    Code:
    SELECT 'blah blah table 1'
         , table2.foo
         , table3.bar
         , 'blah blah table 4'
      FROM table2
    CROSS
      JOIN table3
    to divide your results into chunks, add this --
    Code:
    ORDER
        BY table2.foo
         , table3.bar
    LIMIT offset,rows
    look up LIMIT to see how to use it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2011
    Posts
    21
    right on. Thanks.

    I'll let you know how it works. :-)

  8. #8
    Join Date
    Nov 2011
    Posts
    21

    thanks

    Quote Originally Posted by r937 View Post
    if table 1 and 4 only contain 1 row each, don't bother with them
    Code:
    SELECT 'blah blah table 1'
         , table2.foo
         , table3.bar
         , 'blah blah table 4'
      FROM table2
    CROSS
      JOIN table3
    to divide your results into chunks, add this --
    Code:
    ORDER
        BY table2.foo
         , table3.bar
    LIMIT offset,rows
    look up LIMIT to see how to use it
    Thanks. The code totally worked extremely well for the cross join adding in the phrases. Now I'm just gonna figure out how to break it up into chunks. I'm a learnin' fool. Thanks, again.

  9. #9
    Join Date
    Nov 2011
    Posts
    21

    ok...

    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.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    where is this 4000-row limit coming from?

    wouldn't it be easier just to run the query and download everything in one go?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Nov 2011
    Posts
    21
    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.

    Thanks.

  12. #12
    Join Date
    Nov 2011
    Posts
    21

    learned something new...

    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.

  13. #13
    Join Date
    Nov 2011
    Posts
    21
    Is there a way when exporting to set the "columns separated with" and "columns enclosed with" to a space rather than a common or quotes?

    like   or something? I tried just putting a space there, but it shows up as foward slashes.

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, i haven't used phpmyadmin in years, i use heidisql instead

    why a space?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Nov 2011
    Posts
    21
    The reason I need a space is because the tables are used to form sentences.

    Like this:

    "There once was a","man from","nantucket","who had"
    "There once was a","man from","france","who had"

    That's how the tables show up now. I can go through and manually do a find and replace in excel, but it'd be cool if they were GTG on the download.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •