FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


Moving fields from one table to another





vignesh_natraj
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.
hexkid
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.
vignesh_natraj
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
hexkid
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.
vignesh_natraj
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
Related topics
the 9/11 truth
Question About Free Domains
ReportLab 2.0
How dose time slow, at Post-lightspeed?
Have you seen die to somebody?
The Game creation topic! - Share experience - Find resources
Stranged thing ever happend on your pc
Maintaining your site
Philosophy of self improvement
THE POWER OF FORGIVENESS.
Create Function to create SQL dynamically
A Philosophical Journey [Pt2]
JpegLoad - Free Image Hosting (BETA)
So He Was Cheating
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

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