SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    Nov 2002
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Can you copy columns to another table?

    Greetings,

    Is it possible to copy one column from a table into a new table? I have an existing song database with the authors listed in a column and I want to move the authors into a songs_authors table. I tried running this code, but it didn't seem to work:

    $result = @mysql_query("SELECT author FROM songs");

    while ($row = mysql_fetch_array($result)){

    $name = $row["author"];

    $add = @mysql_query("INSERT INTO songs_authors SET name=$name");

    echo($add);

    }

    Any ideas? Thanks!

  2. #2
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It sounds like there is another column in the songs_authors table, probably "song"? And it sounds like both author and song would be a composite primary key for the table songs_authors? If so, song can not be null.

    A good thing to do is to echo out the SQL queries for debugging purposes. That way you can copy and paste the query into the mysql client on the terminal window, or in phpMyAdmin and see what error message comes up exactly.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    here ya go --
    Code:
    DROP TABLE song_authors
     ;
    CREATE TABLE song_authors
     ( id   integer primary key  auto_increment
     , author_name  varchar(50)
     );
    INSERT INTO song_authors ( author_name )
     SELECT DISTINCT author FROM songs
     ;
    rudy
    http://rudy.ca/

  4. #4
    SitePoint Member
    Join Date
    Nov 2002
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks for the code rudy. it worked like a charm!

    i have an additional question. in my current table, i included an author entry like: joe smith, george brown and bob doe.

    is there any way to break those names up while copying the column from the one table to the new table and making sure they aren't already in the database?

    thanks!

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    is there any way to break those names up? yes, but you won't like the best one -- do it by hand

    the only way you can "automate" breaking up a string into individual authors is by making a lot of assumptions, and doing things like nesting the INSTR function to pick up second consecutive space, etc... very complicated

    as for guaranteeing uniqueness, your first population was created by INSERT / SELECT DISTINCT, so you should be okay at this point

    however, this is now a good time for

    ALTER TABLE song_authors
    ADD UNIQUE distinctauthors (author_name)


    rudy

  6. #6
    SitePoint Member
    Join Date
    Nov 2002
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Rudy,

    What would that alter table add unique line do?
    I was afraid I'd might have to do it by hand. I guess there's not shortcut then Thanks for the help!

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    that alter table sentence adds a unique index on the author_name column

    this means you cannot add the same author to the authors table more than once

    as for doing it by hand, you can prepare a short list of names that have to be looked at simply by pulling out the names that contain a blank -- those are the ones you'll have to make duplicates of


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
  •