Question about copying data from one table to another.
Posted 17 November 2003 - 07:06 PM
I have a question about copying data from one table to another. Well actually, I would like to move the data from one table into another.
ie. I have a php page when the user can view one image at a time. If the user wishes to keep the image, he/she clicks a button and the image is copied to another database and removed from the current then the next image is displayed and so forth.
I tried to do this from the mysql prompt. However, I recieve a duplicate entry \'1\' for key 1. Obviously, I have a unique primary index.
Is there a way I could work around this?
Posted 17 November 2003 - 07:21 PM
Posted 17 November 2003 - 08:52 PM
Posted 17 November 2003 - 09:16 PM
Let me know if those wont work or if your thinking of something else and I\'l throw out more suggestions
Posted 18 November 2003 - 12:52 AM
Is there a way perhaps that I can have php find the last record in the second table and add the next record to it?
ie the first table record is 2 the last record in the second table is 68. Can I simply insert the record into the second table to be the 69th record? This way I won\'t have to worry about that error previously stated.
Posted 18 November 2003 - 01:26 AM
Or you could just insert a new record into the backup db and then pull up the new id that gets created and use that id from then on.
If you just insert new data then it will grab the next available id. The problem you are running into is you are trying to insert the id, which it doesn\'t like since the field is auto increment. So just do an insert for everything but the id and then use
$newid = mysql_insert_id();
to get the new id of the item.
I just wasn\'t sure if you absolutely had to keep that same id or not
Posted 18 November 2003 - 04:12 AM
But I then noticed something strange. Take a look at this code;
$sql = \"INSERT INTO oldtable (`imagefilepath`) select (`imagefilepath`) from newtable where ID=$count\";
The above works fine. However, if I add another column see below;
$sql = \"INSERT INTO oldtable (`imagefilepath`,`v5`) select (`imagefilepath`,`v5`) from newtable where ID=$count\";
Nothing happens. That is the records will not copy to the oldtable. If I just use one column or \'Select *from new table\', that\'s not a problem. Is there any reason why it won\'t take more than one column? It works with the regular \'Insert\' commands I have in php.
Posted 18 November 2003 - 04:25 AM
$query = mysql_fetch_array(mysql_query(\"SELECT imagefilepath, v5 FROM new_table WHERE ID=$count\"));
$insert = mysql_query(\"INSERT INTO old_table (\'imagefilepath\', \'v5\') VALUES (\'$query[imagefilepath]\', \'$query[v5]\')\");
Posted 19 November 2003 - 09:00 AM
Its not a subquery. Checkout INSERT ... SELECT syntax in MySql manual
It is section 188.8.131.52 in mine. (version 3.23.47)
The only reason I can think of is an error in the second field name - are you sure its valid
Donations gratefully received
|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
Posted 19 November 2003 - 10:43 AM
Posted 19 November 2003 - 04:35 PM
I had this problem similar to this one. The \'Insert\' command would work in direct mode at the mysql prompt but would not work in the php code.
I really didn\'t do anything to correct it. But the following day it worked in the php code.
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users