SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Addict
    Join Date
    Jan 2004
    Location
    Nashville
    Posts
    335
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Save a copy of records before truncate

    I am trying to save a copy of the records in tables_71 before I remove them and when the following executes it truncates tables_71 but does not save the records into tables_73

    any ideas? Thank you.


    $sql = "INSERT INTO 'mos_philaform_tables_73' SELECT * FROM 'mos_philaform_tables_71'";

    mysql_query($sql);

    $sql = "TRUNCATE TABLE `mos_philaform_tables_71`";
    mysql_query($sql);
    Last edited by pilotjourney; Aug 22, 2007 at 05:21. Reason: To put a question mark next to post

  2. #2
    Sell crazy someplace else markl999's Avatar
    Join Date
    Aug 2003
    Location
    Manchester, UK
    Posts
    4,007
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Probably down to your quotes:

    $sql = "INSERT INTO 'mos_philaform_tables_73' SELECT * FROM 'mos_philaform_tables_71'";

    Should be:
    $sql = "INSERT INTO `mos_philaform_tables_73` SELECT * FROM `mos_philaform_tables_71`";

    or just:
    $sql = "INSERT INTO mos_philaform_tables_73 SELECT * FROM mos_philaform_tables_71";

  3. #3
    Chessplayer kleineme's Avatar
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    608
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Does mos_philaform_tables_73 already exist? Put a
    echo mysql_error();
    after mysql_query and see what it says.
    Never ascribe to malice,
    that which can be explained by incompetence.
    Your code should not look unmaintainable, just be that way.

  4. #4
    SitePoint Addict
    Join Date
    Jan 2004
    Location
    Nashville
    Posts
    335
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes mos_philaform_tables_73 does exist.

    When I ran the correct syntax it did add the record to _tables_73 - but when it ran again it did not add another record to _tables_73

    Each time the script runs I need records to be added to _tables_73 - does the insert statement do that .. I thought it did

  5. #5
    Chessplayer kleineme's Avatar
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    608
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What does mysql_error say?

    Do you try to insert the same records over and over again? If you have a PRIMARY KEY on your table then the INSERT will fail because it tries to insert keys that already exist. You may try to use

    INSERT IGNORE INTO mos_philaform_tables_73 SELECT * FROM mos_philaform_tables_71;

    Or you could put a

    TRUNCATE TABLE mos_philaform_tables_73;

    in front of your insert.
    Never ascribe to malice,
    that which can be explained by incompetence.
    Your code should not look unmaintainable, just be that way.

  6. #6
    SitePoint Addict
    Join Date
    Jan 2004
    Location
    Nashville
    Posts
    335
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well the strangest thing is that this code does not always seem to work. Which of course means something is going on that I am missing.

    $sql = "INSERT INTO mos_philaform_tables_73 SELECT * FROM mos_philaform_tables_71";

    mysql_query($sql) or print(mysql_error());

    $sql = "TRUNCATE TABLE `mos_philaform_tables_71`";
    mysql_query($sql) or print(mysql_error());

    The truncate part works but the insert does not work. But it did a couple times.

  7. #7
    Chessplayer kleineme's Avatar
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    608
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You have to truncate the table before the INSERT. Your INSERT fails because you try you insert records with primary keys which are already there. TRUNCATE empties your table so that afterwards you can INSERT anything you want.
    Never ascribe to malice,
    that which can be explained by incompetence.
    Your code should not look unmaintainable, just be that way.

  8. #8
    SitePoint Addict
    Join Date
    Jan 2004
    Location
    Nashville
    Posts
    335
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    but if I trucate first there will not be any records to insert? Maybe change the primary key on the other table?
    Last edited by pilotjourney; Aug 23, 2007 at 04:39. Reason: to add something

  9. #9
    Chessplayer kleineme's Avatar
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    608
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You have to TRUNCATE the table in which you want to insert rows, which is, of course, mos_philaform_tables_73!
    Never ascribe to malice,
    that which can be explained by incompetence.
    Your code should not look unmaintainable, just be that way.


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
  •