Thierry Posted August 18, 2006 Share Posted August 18, 2006 I was wondering if there was a way in which PHP/SQL will copy the selected record, simply copy & pasting all its contents into a new record, with only 1 field being changed.So if I had ten records, and I hit the copy button, then record 11 should be made.Right now, I do this by using a select query to select all the data, then getting all the quotes and stuff right (again) and then an insert query.Anyway in which it goes a lot faster then that? Something like "COPY * FROM table WHERE id = 'id'"? Quote Link to comment Share on other sites More sharing options...
ToonMariner Posted August 18, 2006 Share Posted August 18, 2006 You would have to select a record then insert a new record with all the fields except the unique id. Quote Link to comment Share on other sites More sharing options...
Thierry Posted August 18, 2006 Author Share Posted August 18, 2006 Thats currently what I am doing.I select the record I want, get all the data, and put all that in another query to insert it.But with tables that have a lot of fields(not to mentions quotes) this can take a long time.I was wondering if there was simply some kind SQL statement in which it simply adds a new record with the data of the selected record. Quote Link to comment Share on other sites More sharing options...
ToonMariner Posted August 18, 2006 Share Posted August 18, 2006 IF you use the field `id` as the unique or primary key then do this.Adapted the example in the manual for your needs[code]<?php$qry = "SELECT * FROM `atable` WHERE `id` = $id";$qry = mysql_query($qry);// generate field names..$fields = "(";$i = 0;while ($i < mysql_num_fields($qry)) { echo "Information for column $i:<br />\n"; $meta = mysql_fetch_field($qry, $i); if (!$meta) { echo "No information available<br />\n"; } if ($meta->primary_key == 1) { $pripos = $i; } $fields .= strcomp($fields,'(') == 0 ? "`" . $meta->name . "`" : ", `" . $meta->name . "`"; $i++;}$fields .= ")";That is your string...Now the values...$row = mysql_fetch_assoc($qry);$vals = "(";$i = 0;foreach($row as $key => $val){ if ($i == $pripos) { $vals .= strcomp($vals,'(') == 0 ? "NULL" : ", NULL" ; } else { if (is_null($vals)) { $vals .= strcomp($vals,'(') == 0 ? "NULL" : ", NULL" ; } else { $vals .= strcomp($vals,'(') == 0 ? "'" . $vals . "'" : ", '" . $vals ."'" ; } }}$vals .= ")"$qry = "INSERT INTO `atable` " . $fields . " VALUES " . $vals . "";$qry = mysql_query($qry);?>[/code]Try that.... Quote Link to comment 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.