Insert array from one table to another

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.

change your query line to mysql_query($pop) or die(mysql_error());, re-run, and tell us if you get any errors. — Mark Elliot
– Mark Elliot, Commented Jul 26, 2011 at 2:17
Well I'll be <insert your ending here>! The error is that db.address_new doesn't exist. However, I can see it does using mysqlcc. — happycoffeebean
– happycoffeebean, Commented Jul 26, 2011 at 2:33

cwallenpoole · Accepted Answer · 2011-07-26 02:25:54Z

2

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";

edited Jul 26, 2011 at 2:25

answered Jul 26, 2011 at 2:16

cwallenpoole

82.4k26 gold badges132 silver badges174 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

happycoffeebean Over a year ago

Seems I'm having a hard time finding the table right now. Will apply this when I get that worked out. Thank you!

happycoffeebean Over a year ago

Thank you, cwallenpoole. The problem stemmed from setting a database handle earlier in the program, then setting a pconnect later on ... silly mistake. After I added the $dbh handle as a parameter for mysql_query, everything worked (yours did too).

Collectives™ on Stack Overflow

Insert array from one table to another

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related