php and mysql copy record from one table to another

Question

I would like to archive a student by moving the record from one table to another. This is the code I am trying to use:

<?php
ini_set('memory_limit', '100M');
$sql="Select * from `register` where student_id=".$student_id;
$result=mysql_query($sql);
$row=mysql_fetch_array($result);

//Call the function to archive the table
//Function definition is given below
archive_record(archive,$row);

//Once you archive, delete the record from original table

$sql = "Delete from `register` where student_id=".$student_id;
mysql_query($sql);


function archive_record($archived_tablename,$row)
{
    $sql = "insert into $archived_tablename values(";
    $i=0;
    while($i<(count($row)-1))
    {
        $sql.="'".$row[$i]."',";
    }
    $i=$i+1;

    $sql.="'".$row[$i]."'";
    $sql.=")";

    mysql_query($sql);
    return true;
}

Problem I am having is that i am getting error:

Fatal error: Out of memory (allocated 80478208) (tried to allocate 80216043 bytes) in /archive-student.php on line XX

Is there any different way to do this, except for have a column called archive and changing from 0 to 1? This is because I have 30-50 pages selecting the table's records. :)

You could use an INSERT ... SELECT. What's the structure of your tables? — bfavaretto
– bfavaretto, Commented Mar 9, 2012 at 20:58

dev-null-dweller · Accepted Answer · 2012-03-09 21:05:47Z

38

INSERT INTO archive_table
SELECT * FROM original_table WHERE id = 1

simple as that.

If tables have different column number, other layout etc., you will have to specify columns :too

INSERT INTO archive_table(field1, field2, field3)
SELECT field7, field8, field9 FROM original_table WHERE id = 1

edited Mar 9, 2012 at 21:05

answered Mar 9, 2012 at 20:58

dev-null-dweller

29.5k3 gold badges68 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Konstantin Tarashchanskiy Over a year ago

Gah, I was just about to select the same thing. I am not sure that it will work though, since it can still cause the out of memory error

dev-null-dweller Over a year ago

@KonstantinNaryshkin - this one does not retrive any data from mysql so PHP should not reach memory limit. @DavidPassmore there was * missing in select, just corrected that.

Adro Over a year ago

In large data, how can you determine which record is inserted or not when something goes wrong?

dickwan Over a year ago

@dev-null-dweller: What if the source table contains a very large amount of records. Is there a way to assure that this script won't break. For instance does the use BEGIN and COMMIT make sense here. If the script breaks how can I continnue where it stopped. Sorry it is in the scope of OP's question

dev-null-dweller Over a year ago

@dickwan You can run this in loop with limits, like SELECT * FROM original_table LIMIT 10000 OFFSET 0 and after it SELECT * FROM original_table LIMIT 10000 OFFSET 10000. This allows you to save progress between chunks of data.

j0k · Accepted Answer · 2012-07-11 07:44:25Z

3

INSERT INTO new_table SELECT id FROM old_table

This is best practice to move one table row from any table to required table.

edited Jul 11, 2012 at 7:44

j0k

22.8k28 gold badges81 silver badges90 bronze badges

answered Jul 11, 2012 at 0:10

shaz3e

3343 silver badges15 bronze badges

Comments

Xavinou · Accepted Answer · 2012-03-09 21:02:27Z

2

while($i<(count($row)-1))
    {
        $sql.="'".$row[$i]."',";
    }
    $i=$i+1;

You have to do $i=$i+1; inside the loop...

But,

INSERT INTO archive TABLE
SELECT FROM original_table WHERE id = 1

is the best way to do ;)

answered Mar 9, 2012 at 21:02

Xavinou

8021 gold badge6 silver badges19 bronze badges

Comments

Raghav · Accepted Answer · 2017-04-24 16:44:18Z

0

    <html>
    <body bgcolor="lightblue">
    <h1>Data fetched from register.php</h1>
    <table  border=1 cellspacing=0 cellpadding=10px>
    <tr>
    <th>id</th>
    <th>fullname</th>
    <th>email</th>
    <th>username</th>
    <th>dob</th>
    <th>password</th>
    <th>gender</th>
    <th>lanuage</th>
    <th>country</th>
    </tr>
    <?php
    $xyz=mysqli_connect("localhost", "root", "", "myprogrammes");
    $abc=mysqli_query($xyz, "select * from table");
    while ($bb=mysqli_fetch_array($abc)){
          $id1=$bb['id'];
          $fullname1=$bb['fullname'];
          $email1=$bb['email'];
          $username1=$bb['username'];
          $dob1=$bb['dob'];
          $password1=$bb['password'];
          $gender1=$bb['gender'];
          $lanuage1=$bb['lanuage'];
          $country1=$bb['country'];
    ?>
    <tr>
    <th><?php echo $id1 ; ?></th>
    <th><?php echo $fullname1 ; ?></th>
    <th><?php echo $email1 ; ?></th>
    <th><?php echo $username1 ; ?></th>
    <th><?php echo $dob1 ; ?></th>
    <th><?php echo $password1 ;  ?></th>
    <th><?php echo $gender1 ; ?></th>
    <th><?php echo $lanuage1 ; ?></th>
    <th><?php echo $country1 ; ?></th>
    </tr>
<?php  } ?>
    </body>
    </html>

edited Apr 24, 2017 at 16:44

answered Apr 22, 2017 at 17:56

Raghav

11 bronze badge

1 Comment

trincot Over a year ago

Welcome to StackOverflow! Although the code you posted may answer the question, please add some explanation why it is an answer.

Collectives™ on Stack Overflow

php and mysql copy record from one table to another

4 Answers 4

5 Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related