SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    May 2003
    Location
    Lincoln NE
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy MySQL Quick Question

    I'm trying to remove a column from my main mysql table that contain repetitive information and put this column in a separate table with a unique ID for each distinct record and start using these ID's in my main table to refer to these records.

    Here is what my MAIN table looks like:

    +------+------ +-------+-------+--------+
    | ID | COMP | CODE | ADDR | MDATE

    -------+-------+-------+-------+--------+

    The CODE column contains repetitive records and I'm just looking for the mysql command that I can use to create a new table (code table) with 2 columns ID & CODE (pull from main table) and I want to replace the code column in my main table with the code ID's
    (from the new code table) instead of having the actual code in my main table to avoid having the same record twice in my main table.
    Any help is greatly appreciated!!

  2. #2
    eschew sesquipedalians silver trophy sweatje's Avatar
    Join Date
    Jun 2003
    Location
    Iowa, USA
    Posts
    3,749
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by chafikb
    The CODE column contains repetitive records and I'm just looking for the mysql command that I can use to create a new table (code table) with 2 columns ID & CODE (pull from main table) and I want to replace the code column in my main table with the code ID's
    (from the new code table) instead of having the actual code in my main table to avoid having the same record twice in my main table.
    Any help is greatly appreciated!!
    I am not up much on MySQL, but I think you want something like:
    CREATE TABLE codes (
    id int(11) NOT NULL auto_increment,
    code varchar(50) NOT NULL default '',
    PRIMARY KEY (id)
    ) TYPE=MyISAM COMMENT='normalized codes from main table';

    INSERT INTO codes (code)
    SELECT DISTINCT code FROM main;
    Jason Sweat ZCE - jsweat_php@yahoo.com
    Book: PHP Patterns
    Good Stuff: SimpleTest PHPUnit FireFox ADOdb YUI
    Detestable (adjective): software that isn't testable.

  3. #3
    eschew sesquipedalians silver trophy sweatje's Avatar
    Join Date
    Jun 2003
    Location
    Iowa, USA
    Posts
    3,749
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    opps, forgot the last part,

    make a var like this:
    $sql = "UPDATE main SET code = %d WHERE code = '%s'";

    Then loop over codes, and for every row, execute:
    sprintf($sql, $row['id'], $row['code']);

    You may run into type conversion problems, so you might want to add a column like "code_id" that is the correct type.

    HTH
    Jason Sweat ZCE - jsweat_php@yahoo.com
    Book: PHP Patterns
    Good Stuff: SimpleTest PHPUnit FireFox ADOdb YUI
    Detestable (adjective): software that isn't testable.

  4. #4
    SitePoint Enthusiast
    Join Date
    May 2003
    Location
    Lincoln NE
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Sweatje, but I was planning to get all this part taken care of using the mysql command prompt.

    I don't want to get PHP involved in this before I get all the tables in the correct shape.
    Can anybody help me with this. all the code has the same pattern (5charecters) or N/A for the blank ones.

  5. #5
    SitePoint Enthusiast escape164's Avatar
    Join Date
    Dec 2002
    Location
    Colorado, USA
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't know what kind of environment you are running on, but the phpMyAdmin project has a great tool for interfacing with MySql. It takes all the gueswork out of the commandline.

    As for your problem, to accomplish this I believe you would need to make use of some of the more advanced features of the SQL language that MySql currently does not support, depends on your version. I think 4.0 might have something that you could use for this, but I am not sure.

    I would personally write a php script from the commandline, just create a file similar to this:
    PHP Code:
    #!/path/to/php/binary
    <?php
    Enter your PHP Code here
    .
    ?>
    and you can run it from the command line by changing the permissions to executeable and doing

    Code:
    # ./scriptname
    I use this format for a number of my quick tasks that I must perform when dealing with complicated database records.


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
  •