chelsea7 Posted November 17, 2003 Share Posted November 17, 2003 Hello everyone, 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? Thanks Chelsea Quote Link to comment https://forums.phpfreaks.com/topic/1381-question-about-copying-data-from-one-table-to-another/ Share on other sites More sharing options...
DylanBlitz Posted November 17, 2003 Share Posted November 17, 2003 so do you want to know how to move the data through command line or do you want to know how to do it through php? I\'m not sure what your looking for? Quote Link to comment https://forums.phpfreaks.com/topic/1381-question-about-copying-data-from-one-table-to-another/#findComment-4562 Share on other sites More sharing options...
chelsea7 Posted November 17, 2003 Author Share Posted November 17, 2003 I would like to use php code to do this. Sorry that I wasn\'t clear. Thanks Chelsea Quote Link to comment https://forums.phpfreaks.com/topic/1381-question-about-copying-data-from-one-table-to-another/#findComment-4564 Share on other sites More sharing options...
DylanBlitz Posted November 17, 2003 Share Posted November 17, 2003 The primary key is probably also auto increment isn\'t it? There are a couple things you could do. First, you could take off the auto increment on the second table, that should allow you to insert a new record and use a field from the other table. 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. Let me know if those wont work or if your thinking of something else and I\'l throw out more suggestions Quote Link to comment https://forums.phpfreaks.com/topic/1381-question-about-copying-data-from-one-table-to-another/#findComment-4565 Share on other sites More sharing options...
chelsea7 Posted November 18, 2003 Author Share Posted November 18, 2003 Yes, the primary key is also auto increment. But if I remove the auto increment, wouldn\'t that allow for duplicates in the table? In this situation, the ID\'s must be unique because they are updated by the user . So let\'s say record ID 5 is to be updated or deleted, there can only be one record ID 5 otherwise two records will be deleted. If I\'m understanding the solution correctly. 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. Thanks Chelsea Quote Link to comment https://forums.phpfreaks.com/topic/1381-question-about-copying-data-from-one-table-to-another/#findComment-4569 Share on other sites More sharing options...
DylanBlitz Posted November 18, 2003 Share Posted November 18, 2003 yup, that\'s what I meant by 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 Quote Link to comment https://forums.phpfreaks.com/topic/1381-question-about-copying-data-from-one-table-to-another/#findComment-4570 Share on other sites More sharing options...
chelsea7 Posted November 18, 2003 Author Share Posted November 18, 2003 O.K., yes I did not include the ID field. The record copies into the second table and the auto increment gives the next available ID/record number. 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. Thanks Chelsea Quote Link to comment https://forums.phpfreaks.com/topic/1381-question-about-copying-data-from-one-table-to-another/#findComment-4574 Share on other sites More sharing options...
DylanBlitz Posted November 18, 2003 Share Posted November 18, 2003 hmm, actually I\'m surprised that it worked at all heh. mysql doesn\'t support nested subqueries. At least the latest stable doesn\'t. This is how I would do it. [php:1:924c435665] $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]\')\"); [/php:1:924c435665] Quote Link to comment https://forums.phpfreaks.com/topic/1381-question-about-copying-data-from-one-table-to-another/#findComment-4575 Share on other sites More sharing options...
Barand Posted November 19, 2003 Share Posted November 19, 2003 DylanBlitz, Its not a subquery. Checkout INSERT ... SELECT syntax in MySql manual It is section 6.4.3.1 in mine. (version 3.23.47) Chelsea7, The only reason I can think of is an error in the second field name - are you sure its valid Quote Link to comment https://forums.phpfreaks.com/topic/1381-question-about-copying-data-from-one-table-to-another/#findComment-4620 Share on other sites More sharing options...
DylanBlitz Posted November 19, 2003 Share Posted November 19, 2003 thanks for the correction barand, learn something new every day Quote Link to comment https://forums.phpfreaks.com/topic/1381-question-about-copying-data-from-one-table-to-another/#findComment-4621 Share on other sites More sharing options...
chelsea7 Posted November 19, 2003 Author Share Posted November 19, 2003 Yes, the second field is valid. I\'m going to try the example DylanBliz gave me. I\'ll let you all kno of the outcome. 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. Thanks Chelsea7 Quote Link to comment https://forums.phpfreaks.com/topic/1381-question-about-copying-data-from-one-table-to-another/#findComment-4627 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.