SitePoint Sponsor

User Tag List

Results 1 to 7 of 7

Thread: archiving

  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2000
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm trying to write code to do a copy from one table to another (from eventlog to oldeventlog...both tables have identical structure)
    Here is what I've come up with...

    $extractSQL = "select * from eventlog where ID = '$ID'";
    $result = mysql_query($extractSQL, $connectionid);

    WHILE($row = @mysql_fetch_array($result)){
    $uID = $row['uID'];
    $ID = $row['ID'];
    $event = $row['event'];
    $location = $row['location'];
    $agency = $row['agency'];
    $date = $row['date'];
    $time = $row['time'];
    $author = $row['author'];
    $logentry = $row['logentry'];

    $insertSQL="INSERT INTO oldeventlog (uID, ID, event, location, agency, date, time, author, logentry)";
    $insertSQL .= "VALUES '$uID', '$ID', '$event', '$location',
    '$agency', '$date', '$time', '$author', '$logentry')";

    $result=mysql_query($insertSQL, $connectionid);
    }

    There are many records in the table 'eventlog' with identical ID's...so what I'm trying to do is loop through all of them with the while loop and insert a new record into 'oldeventlog' with each pass.

    What it actually does is write only one record to the table 'oldeventlog'...is there a typo somewhere, or am I misusing 'mysql_fetch_array()' Or what?

    Thanks in advance,
    Luke

  2. #2
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Probably the missing ( after VALUES before '$uID'
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  3. #3
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    However don't you think it would be easier to use mysqldump to dump the table then search nad replace the tbalename in the dump file then reimport it in, I know it would be a heck of a lot faster.

    mysqldump dbname tablename > tablename.sql


    then do the search and replace

    then

    mysqldump dbname < tablename.sql

    <Edited by freddydoesphp on 01-18-2001 at 05:15 PM>
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  4. #4
    SitePoint Enthusiast
    Join Date
    Jul 2000
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ooops. The ( is in the original code, must have lost it when I tried to format the white space for here.
    Yep, doing a dump of the data would be easier, but I'm writing the code for others, who just need a simple interface...(click archive, enter a password and be done with it)
    Any other ideas, anyone?

  5. #5
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    did you try unsetting $insertSQL after the insert query, since you use .= it is probably just adding to the existing value of $insertSQL

    Try:

    $result=mysql_query($insertSQL, $connectionid);
    unset($insertSQL);
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  6. #6
    SitePoint Enthusiast
    Join Date
    Jul 2000
    Posts
    37
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Gave that a try and no luck...I was assuming the first = statement would overwrite the previous value and the second .= statement would only add to what was there for that iteration. I just wanted to keep the code neat so I broke up the statement.
    The code still seems to be overwriting one record with all the values from each iteration. So the last record to go through the loop is the only record in 'oldeventlog'. (the record who's value of uID is the highest.)
    I'll try rewriting it and see if I stumble on something...
    Luke

  7. #7
    SitePoint Enthusiast
    Join Date
    Jan 2001
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    if i'm correct in saying eventlog and oldeventlog table
    structures are the same, i think this will do

    $extractSQL = "insert into oldeventlog select * from eventlog where ID = '$ID'";
    $result = mysql_query($extractSQL, $connectionid);

    hope this helps as it is my first post on this forumn.

    billy


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
  •