Copy records into the same DB

[ppgpilot]

I have a DB with 10 fields

 

Records are entered under a date

 

There can be 1 to 30 records for a given date

 

I need to copy all records of a given date with the 10 fields of each...

 

Into the same DB only changing the original date to a new date

 

Sounds easy, but so far I haven't been able to make it work.

 

Any nudge in the right directiion would be much appreciated.

 

 

[seanlim]

No idea how much you have already done, so on the whole the entire process will be as follows (assuming you are using PHP for this process):

 

[*]SELECT the records with the old dates "SELECT * FROM table WHERE date='[Date Here]'"

[*]Read all the columns in to PHP, and format them into an INSERT statement, changing the dates into the new dates

[*]Execute the INSERT statement

 

Hopefully that can get you started? If not, post what you have already tried and describe the problems you are facing.

[ppgpilot]

Thanks for the reply.

 

I should mention I am very new to loops…

 

I will have an array from the SELECT and I am using a WHILE statement to run through the array. So far so good

 

$sql=("select * from $tbl3 where day = '$org_date_unix' order by item");

$result = mysql_query($sql);

require 'my_error.php';

$works_call = mysql_num_rows($result);

 

while($rows = mysql_fetch_array($result))

{    $item = $rows['item'];  etc…}

 

I then display these on the page and all is well. But placing them back in is where I am having the problem.

 

Once I define the variables - maybe they need to be arrays since there will be an numberof them - I need to loop them back into the database with the newly defined date variable to create the same number of records with 10 fields each. Yes?

 

Would another WHILE statement be best, a FOREACH or a FOR loop be the best to use? - and I will begin my research from there.

 

Would I INSERT within the original WHILE statement that selected the records? Or outside in a different loop.

 

Can’t quite get my head around this yet. Knowing the concept and best approach / type of loop for this operation will save me a bunch of time.

 

Thanks!

 

[xyph]

Use SQL

 

INSERT INTO `table2` (`col1`,`col2`,`date`)
SELECT `table`.`col1`,`table`.`col2`, `table`.`date`
FROM `table` WHERE `table`.`date` = '2011-05-29'

 

http://dev.mysql.com/doc/refman/5.0/en/insert-select.html

[seanlim]

Okay, the code looks fine so far. I haven't tested the code below, but try replacing everything in your while loop with that.

 

array_shift($row);
$row['date'] = "[date here]";
foreach($row as $k=>$v)
$row[$k] = '"'.mysql_real_escape_string($v).'"';
$insert = mysql_query("INSERT INTO table_name VALUES(null, ".implode(',',$row).")");

 

Make sure that you (1) replace $row['date'] with the correct name of your date row, (2) replace [date here] with whatever new date you have, and (3) change the table name.

 

Basically, I am assuming that your first row is your id column which you don't want to touch, so using array_shift, remove that from your array. Then, the next line replaces the date with your new date. The foreach loop just adds quotes around the values and uses mysql_real_escape_string (if you need them). And the insert statement brings it all together.

 

It might be slightly faster to combine the insert statements, but you get the general idea of how it works.

[xyph]

It will be more than slightly faster to combine the insert statements. Looping queries is bad, especially if it can be avoided.

[seanlim]

Oh well, he doesn't have THAT many records. Maybe a second or two longer! Nevertheless, I agree it's not the best way, simply to demonstrate the concept...