SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,807
    Mentioned
    158 Post(s)
    Tagged
    3 Thread(s)

    splitting a large database but keep records intact

    Hi folks,
    I have inherited a rather cumbersome database that contains the results of a questionnaire, the problem is it has 230 fields and 500+ records!

    Is there a way of splitting the databse using php or mysql so that I can get it down into the different sections. At the moment it looks like:
    id
    a_name
    a_address
    a_postcode
    b_residential
    b_service
    b_service_name
    c_number_of_staff
    c_number_fulltime
    c_number_parttime

    etc, etc

    So what I want is it split into tables...
    table_a
    a_name
    a_address
    a_postcode

    table_b
    b_residential
    b_service
    b_service_name

    etc, etc,
    but keep the record id matching the original record!

    Anybody got an idea or am I going to have to sit here for days reworking this

    Cheers

    SpikeZ
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....

  2. #2
    SitePoint Wizard stereofrog's Avatar
    Join Date
    Apr 2004
    Location
    germany
    Posts
    4,324
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, the following looks pretty dumb... but it's run-once thing so why worry?

    PHP Code:
    $rc mysql_query("SELECT * FROM big_table");
    while(
    $f mysql_fetch_assoc($rc)) {
        
    $id $f['id'];
        unset(
    $f['id']);
        
    $sql = array();
        foreach(
    $f as $field => $value) {
            
    $prefix pos(explode("_"$field));
            
    $sql[$prefix]['fields'][] = $field;
            
    $sql[$prefix]['values'][] = "'" addslashes($value) . "'";
        }
        foreach(
    $sql as $prefix => $a) {
            
    $insert sprintf("INSERT INTO %s_table (id, %s) VALUES ($id, %s)",
                
    $prefix,
                
    implode(","$a['fields']),
                
    implode(","$a['values'])
            );
            
    mysql_query($insert);
        }

    The same tecnique can be used for automating 'create table' (using describe instead of select).

    hth....

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by spikeZ
    the problem is it has 230 fields and 500+ records!
    why is this a problem?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,807
    Mentioned
    158 Post(s)
    Tagged
    3 Thread(s)
    stereofrog, thanks I will give it a try.

    Rudy, it would only be a problem if I can't get stereofrog's code to run. I don't want to sit there manually editing the tables!

    (Yes, I'm lazy!!!)

    SpikeZ
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    why do you have to edit the tables?

    why do you feel that there is a problem?

    what is wrong with leaving the tables the way they are?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,807
    Mentioned
    158 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by r937
    why do you have to edit the tables?
    I have an editting addiction!
    Really the only reason I was looking to simplify the table was because of the limitations of Excel and Word as programs. The clients want to be able to take the results from various set queries and have the data saved as an excel spreadsheet to write their own reports. Excel can't handle the size and number of fields.

    Stereofrog, worked pefectly, thanks for your help.

    Thanks to you both,

    SpikeZ
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    so you would split the table, then you would have to have the users run several set queries instead of one, such that each query pulls from a separate table

    you can achieve exactly the same thing by having each of the set queries retrieve only those portions from the single table that the several set queries running against separate tables would have got

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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