FRIHOST • FORUMS • FAQ • TOS • BLOGS • DIRECTORY

You are invited to Log in or Register a Frihost Account!

Moving fields from one table to another

Frihost Forum Index -> Scripting -> Php and MySQL

Suppose there are two tables namely "table1" and "table2" in my database
These fields are present in both tables

Quote:

name
age
id

I have 11 rows[names,age and id of 11 persons] in table2 and want to transfer any row[name,age and id of any one person] to table1
how can i do this using php.

vignesh_natraj wrote:

Suppose there are two tables namely "table1" and "table2" in my database
These fields are present in both tables

Quote:

name
age
id

I have 11 rows[names,age and id of 11 persons] in table2 and want to transfer any row[name,age and id of any one person] to table1
how can i do this using php.

You don't need PHP for that. A simple query will do it.

Code:

insert into table1 (name, age, id) select name, age, id from table2

If you really need PHP, try

Code:

<?php
// connect to database
$sql = "insert into table1 (name, age, id) select name, age, id from table2";
$res = mysql_query($sql) or die(mysql_error);
echo mysql_affected_rows($res), " rows copied.<br>\n";
?>

Now, in a similar fashion, delete the rows from table2.

thanks for the reply but wont this just copy the field is there a way to move the fields
and how do i implement this in phpMyAdmin

vignesh_natraj wrote:

wont this just copy the field

Yes.

vignesh_natraj wrote:

is there a way to move the fields

Not with a single command.

vignesh_natraj wrote:

how do i implement this in phpMyAdmin

First copy the records, then delete them from the source table.
All code below is meant to be directly interpreted by MySQL, you can use it on phpMyAdmin.

Code:

insert into table1 select * from table2;
delete from table2;

If your tables are updateable while you are doing this, you may need to use a temporary table.

Code:

create temporary table temp_table select * from table2;
insert into table1 select * from temp_table;
delete from table2 where id in (select id from temp_table);
-- drop table temp_table;

or LOCK the tables

Code:

lock tables table1 write, table2 write;
insert into table1 select * from table2;
delete from table2;
unlock tables;

or even both methods together. Ah! Don't forget about transactions either.

My database structure is like this there are 3 rows in a table

Code:

id,name,parent_id

i have three fields like

Code:

1, City1, 0
2, City2, 0
3, City3, 0

Now if i add another a field

Code:

4, Street1, 1
This means Street1 is a child of City1

I want the fields to be displayed like this in a php file

Code:

City 1 City 2
Street1
City3 City4

ie each child under its parent field

Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2007 Frihost, forums powered by phpBB.