将数组从一个表插入另一个表

Question

I'm afraid this is going to sound redundant. I have done my diligence, and substituted at least three ideas I've found so far, and nothing works.

The goal is to merge unique fields from two tables, but right now I can't even get identical fields from one table into another that was created as an identical table. Here is the code so far, with my comments:

$result = mysql_query("SELECT * from $db.$tbl LIMIT 50");

if($result) {
while($maindata = mysql_fetch_assoc($result)) {

$newdata = implode ("," , $maindata);

$newdata = mysql_escape_string($newdata);

$pop = "INSERT INTO $db.address_new (id,entitynum,address,city,state,zip,country,remarks)
SELECT FROM $db.address";        

//This is the original code replaced by the above
//$pop = "INSERT INTO $db.address_new (id,entitynum,address,city,state,zip,country,remarks)
//  VALUES ($newdata)"; 

mysql_query($pop);

//print_r($pop);
   //Seems to give the correct output in the browser, but the table address_new remains empty. `

Thank you in advance. I really appreciate your help.

Answer 1

To directly insert from another table (note: if ID is auto_increment you may or may not want to insert it this way):

INSERT INTO db.address_new (id,entitynum,address,city,state,zip,country,remarks)
   SELECT (id,entitynum,address,city,state,zip,country,remarks) 
      FROM db.address LIMIT 50

(do not put this in a loop)

If you're looking for unique values, you can do that a couple of ways. Personally, I would have a unique key on one (or a set of) the values and then just do INSERT IGNORE:

INSERT IGNORE INTO db.address_new 
  (id,entitynum,address,city,state,zip,country,remarks)
   SELECT (id,entitynum,address,city,state,zip,country,remarks) 
      FROM db.address LIMIT 50

As a side note:

// use mysql_real_escape_string 
mysql_escape_string($newdata); 

// since you're passing this through PHP, you need to make sure to quote 
// all of your values. This probably means you'll need to loop and replace this
// $newdata = implode ("," , $maindata);
// with something like:

$newdata = array();
foreach( $maindata as $column )
{
    $newdata[] = "'" . mysql_real_escape_string( $column ) . "'";
}
$newdata = implode(',', $newdata);


// you're missing the columns in your select clause. 
$pop = "INSERT INTO $db.address_new ".
       "(id,entitynum,address,city,state,zip,country,remarks) ".
       // You need to select *something*
       "SELECT FROM $db.address";