Board index   FAQ   Search  
Register  Login
Board index php forum :: php coding PHP coding => General

transfer data

Ask about general coding issues or problems here.

Moderators: macek, egami, gesf

transfer data

Postby wckf » Thu Apr 04, 2013 7:34 am

hi guys,
how can i transfer data from one table to another using phpmyadmin sql query ,
both have the same primary key
wckf
New php-forum User
New php-forum User
 
Posts: 15
Joined: Wed Mar 27, 2013 7:59 am

Re: transfer data

Postby seandisanti » Thu Apr 04, 2013 8:07 am

INSERT INTO table2(field1,field2,field3) SELECT field1,field2,field3 from table1 where condition = true
seandisanti
php-forum Fan User
php-forum Fan User
 
Posts: 838
Joined: Mon Oct 01, 2012 12:32 pm

Re: transfer data

Postby wckf » Thu Apr 04, 2013 8:14 am

this is what happened when i entered your code
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'condition = true' at line 5


otherwise when i do it without the WHERE condition = true
it gives me this :
#1452 - Cannot add or update a child row: a foreign key constraint fails

so my main problem is this :

#1452 - Cannot add or update a child row: a foreign key constraint fails
wckf
New php-forum User
New php-forum User
 
Posts: 15
Joined: Wed Mar 27, 2013 7:59 am

Re: transfer data

Postby seandisanti » Thu Apr 04, 2013 8:15 am

I'm sorry, what i posted was a template, not a solution. you have to plug in the field names you want to insert and select, the table names to move data from and to, and boolean condition to evaluate the rows upon to select which records get migrated.
seandisanti
php-forum Fan User
php-forum Fan User
 
Posts: 838
Joined: Mon Oct 01, 2012 12:32 pm

Re: transfer data

Postby wckf » Thu Apr 04, 2013 9:02 am

already did that maybe it is because both of them have to same primary key ?
wckf
New php-forum User
New php-forum User
 
Posts: 15
Joined: Wed Mar 27, 2013 7:59 am

Re: transfer data

Postby seandisanti » Thu Apr 04, 2013 9:21 am

You can't have duplicate values in the primary key field. it would probably be easiest for everyone to understand if you post the fields etc associated with each table.
seandisanti
php-forum Fan User
php-forum Fan User
 
Posts: 838
Joined: Mon Oct 01, 2012 12:32 pm

Re: transfer data

Postby wckf » Thu Apr 04, 2013 9:46 am

.
Last edited by wckf on Thu Apr 04, 2013 10:22 am, edited 1 time in total.
wckf
New php-forum User
New php-forum User
 
Posts: 15
Joined: Wed Mar 27, 2013 7:59 am

Re: transfer data

Postby wckf » Thu Apr 04, 2013 9:58 am

guys can i put a foreign key auto increment ?
with letters like :
lea1
lea2
lea3
.
.
.
?
wckf
New php-forum User
New php-forum User
 
Posts: 15
Joined: Wed Mar 27, 2013 7:59 am

Re: transfer data

Postby seandisanti » Thu Apr 04, 2013 10:33 am

a foreign key is a primary key in another table, so it should already be autoincremented in the table where it is the primary.

your other question about having assigned id's reuse deleted id's actually requires a little more work. once an id has been used, it is removed from the range of id's to be auto assigned. so you can either manually assign the id at the time of insert by using a subquery to add one to the max() value in the field in the table.

Alternatively...
THIS WILL DELETE ALL DATA IN THE TABLE DO NOT DO IT UNLESS THAT IS WHAT YOU WANT

If you're starting from scratch and scrapping all of the data in the table, and just want to begin with one instead of whatever the next number was; you can truncate the table (either using truncate table command, or select the table in phpmyadmin and go to operations, and select truncate).
seandisanti
php-forum Fan User
php-forum Fan User
 
Posts: 838
Joined: Mon Oct 01, 2012 12:32 pm

Re: transfer data

Postby wckf » Thu Apr 04, 2013 10:56 am

what i meant is i have a field in a table where i want it to auto increment with letters nside
e.g :
lea1
lea2
lea3
.
.
.
forget about the foreign key thing :)
wckf
New php-forum User
New php-forum User
 
Posts: 15
Joined: Wed Mar 27, 2013 7:59 am

Re: transfer data

Postby seandisanti » Thu Apr 04, 2013 11:11 am

Probably the easiest way to achieve that would be with an insert trigger. you could have it populate a field with the contents of a string field and an auto increment field as the row is inserted. search for mysql insert trigger to get a good example
seandisanti
php-forum Fan User
php-forum Fan User
 
Posts: 838
Joined: Mon Oct 01, 2012 12:32 pm

Re: transfer data

Postby wckf » Thu Apr 04, 2013 11:17 am

UPDATE leads SET lead_no='LEA'+cast(leadid as char)

this took the number from the id field and puted it nto the lead_no field but without the LEA
wckf
New php-forum User
New php-forum User
 
Posts: 15
Joined: Wed Mar 27, 2013 7:59 am

Re: transfer data

Postby johnj » Thu Apr 04, 2013 8:26 pm

It is better to increment the number in the code and then update it using the sql. e.g. if the current value was lea001, then increment 001 to 002, concatenate and create a new string, and use that for the update so that the new value will be lea002.
johnj
php-forum Super User
php-forum Super User
 
Posts: 1805
Joined: Thu Mar 10, 2011 5:07 pm


Return to PHP coding => General

Who is online

Users browsing this forum: Google [Bot] and 1 guest

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.