PDO重复行

Question

I'm looking for a way to duplicate this row using PDO. I don't want to retype all the values.

$strsql="INSERT INTO `dreamyAuctions`(`category`,`type`,`city`,`host`,`host_name`,`title`,`fee`,`base_fee`,`min_fee`,`max_fee`,`min_fee2`,`max_fee2`,`last_fee_change`,`seats`,`retail_price`,`asking_price`,`details`,`delivery`,`delivery_time`,`timer`,`start_date`,`free_bids`,`seats_taken`,`show_on_homepage`,`vet_players`,`tournament`,`final_round`,`refresh`,`repost`,`started`,`completed`,`active`,`create_date`,`complete_date`,`minimum_auctions`,`video`,`video2`,`show_pinterest`,`bid_fee`,`saves`,`seat_free`) 
VALUES(\"$cat\",\"$contest_type\",\"$city\",\"$host\",\"$host_name\",'" . mysql_real_escape_string($title) . "',\"$base_fee\",\"$base_fee\",\"$min_fee\",\"$max_fee\",\"$min_fee2\",\"$max_fee2\",now(),\"$seats\",\"$retail\",\"$ask_price\",'" . mysql_real_escape_string($details) . "',\"$delivery\",\"$dtime\",'11',\"$start_date\",\"$free_bids\",'0',\"$show\",\"$vet\",\"$tournament\",'N',\"$refresh\",'Y','N','N','Y',now(),'',\"$min_auctions\",'" . mysql_real_escape_string($video) . "','" . mysql_real_escape_string($video2) . "',\"$pinterest\",\"$bfee\",\"$pre_set_saves\",\"$free_to_seat\")";
mysql_query($strsql,$connect) or die(mysql_error());
$chkrow1=mysql_affected_rows($connect);
$aid=mysql_insert_id();

I tried

$auctionId=1;
//create new table with old data
$st = $db->prepare("CREATE TEMPORARY TABLE temp_tbl SELECT * FROM `dreamyAuctions` WHERE `id`=:auctionId;
INSERT INTO dreamyAuctions SELECT * FROM temp_tbl;
DROP TABLE temp_tbl;"); // need to filter for next auction
$st->bindParam(':auctionId', $auctionId); // filter
$st->execute();

This is not working

Answer 1

You should be able to insert into dreamyAuctions with a straight select from the same table for the given auction_id - you just have to list all the fields with the exception of auction_id as this is the primary key (assumption) and cannot be duplicated.

INSERT INTO `dreamyAuctions`(`category`,`type`,`city`,`host`,`host_name`,`title`,`fee`,`base_fee`,`min_fee`,`max_fee`,`min_fee2`,`max_fee2`,`last_fee_change`,`seats`,`retail_price`,`asking_price`,`details`,`delivery`,`delivery_time`,`timer`,`start_date`,`free_bids`,`seats_taken`,`show_on_homepage`,`vet_players`,`tournament`,`final_round`,`refresh`,`repost`,`started`,`completed`,`active`,`create_date`,`complete_date`,`minimum_auctions`,`video`,`video2`,`show_pinterest`,`bid_fee`,`saves`,`seat_free`) 
SELECT
`category`,`type`,`city`,`host`,`host_name`,`title`,`fee`,`base_fee`,`min_fee`,`max_fee`,`min_fee2`,`max_fee2`,`last_fee_change`,`seats`,`retail_price`,`asking_price`,`details`,`delivery`,`delivery_time`,`timer`,`start_date`,`free_bids`,`seats_taken`,`show_on_homepage`,`vet_players`,`tournament`,`final_round`,`refresh`,`repost`,`started`,`completed`,`active`,`create_date`,`complete_date`,`minimum_auctions`,`video`,`video2`,`show_pinterest`,`bid_fee`,`saves`,`seat_free`
FROM `dreamyAuctions` WHERE auction_id=:auctionId

The reason your temporary table insert is not working is because the primary key is going with the data and you cannot duplicate it when inserting back into the main table.