Single mysql query to insert columns values from one table to another table

PHP
1

I am trying to code a mysql query that will enable me select and insert multiple vaules from one table to another table in the same mysql database. I want to automatically populate the values from my id,levy from my tb_residents into table 2 resident_payments uid , rate .

I know there has got to be an easier way than first selecting all the values from my tb_residents table then writing a separate insert query. I have written this so far, but i’m not sure how to tie both queries together. I believe the 2 queries would look likes this

   mysql_select_db($database_conn, $conn);


$query= "SELECT * FROM residents ";
$result = mysql_query($query, $conn) or die(mysql_error());
$row = mysql_fetch_assoc($result);


$sql = array();
foreach( $data as $row ) {
    $sql[] = '("'.$row['uid'].'", '.$row['levy'].')';
}
mysql_query('INSERT INTO residents_payments (`uid`, `rate`) VALUES '.implode(',', $sql));
2

Not tested but I think this would work.

$query= "INSERT INTO residents_payments (`uid`, `rate`)
SELECT `uid`, `levy` FROM residents";
mysql_query($query, $conn) or die(mysql_error());
3

Please be aware that the mysql_* extension is now deprecated as of the current version of PHP and will very likely be removed from the next 5.x version and will likely not be in PHP 6.x (when it eventually is released). You should migrate over to either the mysqli_* extension or to PDO. PDO is a better choice as it doesn’t tie you down so much to a particular database server software.

Once you have migrated you should use Prepared Statements to prevent SQL Injection attacks. Have a read of this article from the PHP manual, it shows how to use prepared statements with PDO and also explains the principle.