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.

mysql_select_db($database_conn, $conn);


$query_rpz = "SELECT * FROM residents ";
$rpz = mysql_query($query_rpz, $conn) or die(mysql_error());
$row_rpz = mysql_fetch_assoc($rpz);


$sql = array(); 
foreach( $data as $row ) {
    $sql[] = '("'.$row['uid'].'", '.$row['levy'].')';
}
mysql_query('INSERT INTO residents_payments (`uid`, `rate`) VALUES '.implode(',', $sql));
$query="insert inot residents_payment (uid,rate) select uid, levy from residents ";

thats all you need to do, you can do directly in phpmyadmin, if it is to be run once only.

Please i want to write a mysql query script that does this, not using phpmyadmin as it is the client that is suppose to run this by simply clicking on a button to generate their residents payments table.

Can you help outline the complete sqly query format for this.

mysql_select_db($database_conn, $conn);
$query_rpz = "insert into residents_payment (uid,rate) select uid, levy from residents";
$rpz = mysql_query($query_rpz, $conn) or die(mysql_error());

That's all...

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.