By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,495 Members | 1,307 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,495 IT Pros & Developers. It's quick & easy.

PHP / MySQL -- Moving rows between tables and dealing with the primary key

P: n/a
Let me start by saying that I'm still very new at PHP/MySql and will
take any advice. :-)

The issues is that every once in a while users get an error when they
use this code. I believe the issue is that I'm copying the primary
key as well as the other data in the row. So, my thoughts on fixing
this are to just select everything but the 'pkey' column, but I'm not
sure what would result from that. Would MySql use the next auto
incremented primary key for the 'pkey' column when the data is moved
into the table, or would it just leave it blank?

Also, is their a better strategy for moving data from one table to
another than the one I have taken?

///////////////my variables//////////////
$newtablename == name of the target table
$tablename == name of the current table
$pkey == the table's primary key (big int, auto
incrementing)

//////////////The Code////////////////////
mysql_connect(DB_HOST, DB_USER, DB_PASS) or die(mysql_error());
mysql_select_db(DB_NAME) or die(mysql_error());

/////////////My move Queries//////////////
$sql1 = "insert into $newtablename select * from $tablename where
pkey = $pkey";
$sql2 = "DELETE FROM $tablename WHERE pkey = $pkey";
$result1 = mysql_query($sql1) or die(mysql_error());
$result2 = mysql_query($sql2) or die(mysql_error());

Thanks to any and all who reply :-)
Eric
Jul 17 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
On 23 May 2004 22:29:28 -0700, Eric <jo******@speakeasy.net> wrote:
Let me start by saying that I'm still very new at PHP/MySql and will
take any advice. :-)

The issues is that every once in a while users get an error when they
use this code. I believe the issue is that I'm copying the primary
key as well as the other data in the row. So, my thoughts on fixing
this are to just select everything but the 'pkey' column, but I'm not
sure what would result from that. Would MySql use the next auto
incremented primary key for the 'pkey' column when the data is moved
into the table, or would it just leave it blank?

If your wanting to move the contents of one table to another without
retaining the primary key just adjust your insert into query eg for a
table structure such as:

newtable(id*,name,toot)
oldtable(pkey*,name,bleh)
* indicates primary key, autonumber.

the following query would work:

INSERT INTO newtable (name, toot)
SELECT name, bleh
FROM oldtable
WHERE pkey = $pkey;
if there is another column in newtable that has an autonumber, it will as
usual, increment as needed.
I'm assuming you have a reason to move data from one table structure to
exactly the same table structure so I won't go into that :]

Hope that helps,

Dank.

--
If we can't play God, who will?
Jul 17 '05 #2

P: n/a
In article <f6**************************@posting.google.com >, Eric wrote:
The issues is that every once in a while users get an error when they
use this code. I believe the issue is that I'm copying the primary
key as well as the other data in the row. So, my thoughts on fixing
this are to just select everything but the 'pkey' column, but I'm not
sure what would result from that. Would MySql use the next auto
incremented primary key for the 'pkey' column when the data is moved
into the table, or would it just leave it blank?


You can read the answer in the mysql manual (http://www.mysql.com)
in the section on AUTO_INCREMENT.

--
Tim Van Wassenhove <http://home.mysth.be/~timvw/contact.php>
Jul 17 '05 #3

P: n/a
Thanks all for your help.
Tim Van Wassenhove <eu**@pi.be> wrote in message news:<2h************@uni-berlin.de>...
In article <f6**************************@posting.google.com >, Eric wrote:
The issues is that every once in a while users get an error when they
use this code. I believe the issue is that I'm copying the primary
key as well as the other data in the row. So, my thoughts on fixing
this are to just select everything but the 'pkey' column, but I'm not
sure what would result from that. Would MySql use the next auto
incremented primary key for the 'pkey' column when the data is moved
into the table, or would it just leave it blank?


You can read the answer in the mysql manual (http://www.mysql.com)
in the section on AUTO_INCREMENT.

Jul 17 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.