RE: [PHP] Duplicating a Record in MySQL

I tried using an alias. That doesn't work either. The following is from the MySQL manual 6.4.3.1 INSERT ... SELECT Syntax INSERT [LOW_PRIORITY] [IGNORE] [INTO] tbl_name [(column list)] SELECT ... With INSERT ... SELECT statement you can quickly insert many rows into a table from one or many tables. INSERT INTO tblTemp2 (fldID) SELECT tblTemp1.fldOrder_ID FROM tblTemp1 WHERE tblTemp1.fldOrder_ID > 100; The following conditions hold for an INSERT ... SELECT statement: _ The target table of the INSERT statement cannot appear in the FROM clause of the SELECT part of the query because it's forbidden in ANSI SQL to SELECT from the same table into which you are inserting. (The problem is that the SELECT possibly would find records that were inserted earlier during the same run. When using sub-select clauses, the situation could easily be very confusing!) _ AUTO_INCREMENT columns work as usual. _ You can use the C API function mysql_info() to get information about the query. See Section 6.4.3 [INSERT], page 407. _ To ensure that the update log/binary log can be used to re-create the original tables, MySQL will not allow concurrent inserts during INSERT .... SELECT. You can of course also use REPLACE instead of INSERT to overwrite old rows. -----Original Message----- From: David McInnis [mailto:david@dataovation.com] Sent: Saturday, October 19, 2002 10:37 PM To: Roger Lewis Cc: php-general@lists.php.net Subject: Re: [PHP] Duplicating a Record in MySQL Even if you select from an alias of the same table? insert into tablex from (select * from tablex tabley) David ----- Original Message ----- From: "Roger Lewis" <re.lewis@attbi.com> To: "David McInnis" <david@dataovation.com> Sent: Saturday, October 19, 2002 7:41 PM Subject: RE: [PHP] Duplicating a Record in MySQL > Yes, but you can't insert into the same table from which you select. > Roger > > -----Original Message----- > From: David McInnis [mailto:david@dataovation.com] > Sent: Saturday, October 19, 2002 6:28 PM > To: Roger Lewis; holmes072000@charter.net; 'Php-General' > Subject: Re: [PHP] Duplicating a Record in MySQL > > Well, I confess that I have not read the entire thread. But MySQL does > provide a INSERT from SELECT syntax which I have used often. > > David > > ----- Original Message ----- > From: "Roger Lewis" <re.lewis@attbi.com> > To: <holmes072000@charter.net>; "'Php-General'" <php-general@lists.php.net> > Sent: Saturday, October 19, 2002 1:45 PM > Subject: RE: [PHP] Duplicating a Record in MySQL > > > > > > > > -----Original Message----- > > From: John W. Holmes [mailto:holmes072000@charter.net] > > Sent: Saturday, October 19, 2002 12:52 PM > > To: 'Roger Lewis'; 'Php-General' > > Subject: RE: [PHP] Duplicating a Record in MySQL > > > > * >> It's not strictly php, but does anyone know an easy way to > > duplicate a > > > >> record in MySQL. It would be nice if you could write something > > like: > > > >> > > > >> COPY * from table WHERE id = 6; > > > >> > > > >> and have it insert a new record into the table with the next id > > > >>number. > > > > > > >I think that if you have to do this, you've got design issues. Why > > not > > > >just keep a "count" column in the row and increment that, instead of > > > >duplicating the row. > > > > > > >---John Holmes... > > > John, > > > Here's the application: I'm creating a daily report online, eg. with > > > Title, > > > Report no., Date, Author, Subject Headings, Text, etc., etc.. The > > report > > > is > > > stored in a MySQL table. When it comes time to write a new report, I > > > would > > > like to be able to call up an existing report, upon which to base the > > new > > > one, and copy the record. Then make changes where necessary and > > update > > > the > > > record. > > > > So have PHP select out the row you want to "copy", make your changes to > > it with PHP or a form, then have PHP insert a new row into the database. > > Why copy data when you're going to change it. > > > > ---John Holmes... > > Well, you're probably right. I've been working with Adobe GoLive, and > have > > been using the CopyFormData command, so that's where I got the idea. I > ran > > into trouble with GoLive when I complicated my report form by adding file > > attachments. Anyway, here's a condensed version of code base on what > > Stephen Knight suggested. (Thanks, Stephen). I'll work on this to try > and > > avoid the copying step as you are suggesting. > > > > $sql = "select * from daily_reports where report_no = '$report_no' "; > > $result = mysql_query($sql); > > $x = mysql_fetch_row($result); > > $fields = "report_no, order_no, report_date, project_no, project_name, > > client, title, subtitle, author, company, summary_heading, > > summary, section1_heading, section1"; > > $values = " '$x[1]', '$x[2]', '$x[3]', > > '$x[4]', '$x[5]', '$x[6]', '$x[7]', > > '$x[8]', > > '$x[9]', '$x[10]', '$x[11]', '$x[12]', > > '$x[13]', '$x[14]' "; > > // Note: There must be a way to have PHP generate the previous line, but > I > > don't know how. > > $sql = "INSERT INTO daily_reports ($fields) VALUES ($values)"; > > mysql_query($sql); > > > > Thanks, Roger > > > > > > > > -- > > PHP General Mailing List (http://www.php.net/) > > To unsubscribe, visit: http://www.php.net/unsub.php > > > > > > > > > > -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php