Merging 2 MySQL tables with PHP – update one table’s field with another table’s field data (both table have one common field)
Posted by Shek on October 4, 2009 under PHP | 
Read the First Comment | Total View: 991 views
Table1
|
Table2
|
We would like to update the ID field in table1 with the value of ID field in table 2.
We have created a PHP file “update.php”, here it is:
First we have to connect to the MySQL database:
mysql_connect("Host Name", "User Name", "User Password") or die("Connection Failed"); mysql_select_db("DataBase Name")or die("Connection Failed");
We now create a query to select all records from table2 which we would like to use the records to update table1. We store the query in a variable:
$query = "SELECT * FROM table2";
We then run the query and store the result with a PHP variable:
$result = mysql_query($query)or die(mysql_error());
Now we do the updating of table2 with each result of the query with a while loop, we then run another query to insert the ID record into table2 where the record of the name field of table2 matches the record of name field of table1, we will output any error with die(mysql_error()):
while ($line = mysql_fetch_assoc($result)) { mysql_query("UPDATE table2 SET ID = '".$line['ID']."' where Name = '".$line['Name']."'")or die(mysql_error());}
All together we have:
1 2 3 4 5 6 | mysql_connect("Host Name", "User Name", "User Password") or die("Connection Failed"); mysql_select_db("DataBase Name")or die("Connection Failed"); $query = "SELECT * FROM table2"; $result = mysql_query($query)or die(mysql_error()); while ($line = mysql_fetch_assoc($result)) { mysql_query("UPDATE table2 SET ID = '".$line['ID']."' where Name = '".$line['Name']."'")or die(mysql_error());} |
Similar Posts:
- Include PHP variable in a MySQL query
- Copy one table’s field records into another table’s field in MySQL database with PHP
- Display data from MySQL with PHP
- Display data from MySQL database with HTML form and PHP
- PHP and oracle database 10g Enterprise Edition Part 1
Jo Chan said,
very useful
Add A Comment