Site Statistics
Lets see.. 356 users have been online together, the search feature has been used 12143 times, we have 6218 listed tutorials, a total of 3120134 tutorials have been served so far and a total of 17076 registered members.
Donate | Privacy policy | RSS | FAQ's | Contact us
Bookmark: 
| Connect with FB: 
| Connect with FB: Please Note: Do NOT copy our entire tutorials, we work hard to make these. You are not allowed to translate our tutorials and add them to your site without our permission.
Checked out our Comics yet?
In this short tutorial, I am going to show you how to transfer data from any column in a table to another in sql using php.
In this case, we will be transferring the date and id from one table to the other.
To begin, we'll use the php syntax and connect to the sql server:
Code:
//php syntax
<?php
//Connecting to the database. Edit with your own information.
mysql_connect("hostname", "username", "password") or die(mysql_error());
mysql_select_db("database") or die(mysql_error());
Executing the query, defined as result. I am selecting all the data from the table, this way I can choose to grab as many columns as I like, but in this tutorial, we'll use two as an example, id and date.
Code:
$result = mysql_query("SELECT * FROM table1 order by id") or die(mysql_error());
Creating the loop, I've added descriptions to the code below, so you can follow what is actually being done:
Code:
// keeps getting the next row until there are no more to get
while ($row = mysql_fetch_array($result)) {
// defines $num as the number of rows in the table selected.
$num=mysql_numrows($result);
// defines $i as 0, we are going to use $num as a constant and $i will
// be the number of loops.
$i=0;
// this is the failsafe, it will stop the loop when the number of loops
// gets higher than the number of rows but also keep it going until
// it reaches the number of rows.
while ($i < $num) {
// here we execute the query above, grab the result or data from the
// column id, where the $i as the number of row and define it as $f1.
// The data will keep changing every time the query loops according
// to the row.
$f1=mysql_result($result,$i,"id");
// the same for the date.
$f2=mysql_result($result,$i,"date");
// execute a simple query to insert the data into table 2. define it
// as result2.
$result2 = mysql_query("INSERT into table2(`id`,`date`) VALUES('".$f1."','".$f2."')") or die(mysql_error());
// Print the number of id copied.
echo "Done id: ".$f1.". <br />";
// this will increment the value of $i by 1 everytime its executed
// and until the number of rows is higher than the value of $i this
// will keep repeating itself.
$i++;
}
}
// Display finished when done.
echo "<strong>Finished.</strong>"
// end the file
?>
Save this file as datatransfer.php or any other name, upload and execute on your browser. If you did everything proper you should see your data being transferred one by one.
If you wish to test your code by printing the data you are about to copy it can be done easily by replacing:
Code:
$result2 = mysql_query("INSERT into table2(`id`,`date`) VALUES('".$f1."','".$f2."')") or die(mysql_error());
// Print the number of id copied.
echo "Done id: ".$f1.". <br />";
// this will increment the value of $i by 1 everytime its executed
// and until the number of rows is higher than the value of $i this
// will keep repeating itself.
$i++;
//close all open tags.
}
}
// Display finished when done.
echo "<strong>Finished.</strong>"
// end the file
?>
With:
Code:
// Printing the data on the screen
echo "Id: ".$f1.", Date: ".$f2." <br />";
$i++;
}
}
// Display finished when done.
echo "<strong>Finished.</strong>"
// end the file
?>
Execute the file in your browser, you should see the data printed. Its always good to review the data before doing the actual transfer to see if you're getting what you want.
I hope this tutorial was easy to follow and useful. If there are any questions please let me know through comments or use the forum.
Pages (1)
Encourage us, share our content:
Comments
Post by DevilsWrath on October 7th, 2009The code seems to work fine for me, and it doesnt input n*n times into table2, I've checked it, the number of rows is equal to the number of rows in table1. Yes, you can and should use the mysql_real_escape_string in the query where we call the data from table1, to make sure that the data being transferred to the second database is safe(reference: http://us.php.net/manual/en/function.mysql-real-escape-string.php). In this case, since it was all numerical data, it didnt really make a difference. Thirdly, no offence taken, I love learning and discussing this stuff.
Post by johncarney on October 6th, 2009No offense, but this code is long winded and broken in several ways. For instance, if you're using mysql_fetch_array you do not, and SHOULD NOT, use mysql_result. Second, you should NEVER, EVER, EVER stuff values into an SQL string without running them through mysql_real_escape_string.
Also, you'll end up inserting n*n rows in table2 where "n" is the number of rows in table1.
Thing is, there's a simpler way to do this entirely in SQL:
$db = mysql_connect("hostname", "username", "password") ;
mysql_select_db("database", $db) ;
$sql = <<<SQL
INSERT INTO `table2` (`id`, `date`)
SELECT `id`, `date` FROM `table1`
SQL;
mysql_query($sql, $db) ;
If you really need to do it in PHP, then it should be done this way (leaving out the comments and diagnostic output):
$db = mysql_connect("hostname", "username", "password");
mysql_select_db("database", $db) ;
$result = mysql_query("SELECT `id, `date` FROM `table1` ORDER BY `id`", $db) ;
while ($row = mysql_fetch_object($result)) {
$sql = sprintf(
"INSERT INTO `table2` SET `id` = '%s', `date` = '%s'",
mysql_real_escape_string($row->id, $db),
mysql_real_escape_string($row->date, $db)
) ;
mysql_query($sql, $db) ;
}
Please Login to Write a Comment.
Not a Member? It takes just a minute... Register here.
Tutorial Statistics
Votes: 4
Rating: 6
Views: 7601
Author: DevilsWrath
Message this User
View this User's ProfileMain Category:
PHP Tutorials
Sub-Category:
PHP Coding
Rate this Tutorial:
Get the latest tips and tricks delivered to you!Subscribe to SurfnLearn, and get the latest tutorials delivered to you for free!
Follow our tutorial updates on
