By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,230 Members | 2,471 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,230 IT Pros & Developers. It's quick & easy.

Updating rows from one table to another without PHP?

P: 1
[PHP]$insertTable = "Table";

$query = "SELECT
`gb`.`id` AS `id`,
`gb`.`engineId` AS `engineId`,
`gb`.`engineUPN` AS `engineUPN`,
`gb`.`linkURL` AS `link`,
`gb`.`imageURL` AS `image`,
`gb`.`retailerTitle` AS `title`,
`gb`.`retailerDescription` AS `description`,
`gb`.`manufacturer` AS `brand`,
`gb`.`partcode` AS `mpn`,
`gb`.`retailerPrice` AS `price`,
`gb`.`retailerCode` AS `UPN`,
`gb`.`retailerStock` AS `stock`,
`gb`.`condition` AS `condition`,
`gb`.`dateUpdated` AS `dateUpdated`,
`r`.`retailerId` AS `retailerId`,
`r`.`countryId` AS `countryId`,
`r`.`languageId` AS `languageId`,
`r`.`currencyId` AS `currencyId`
FROM ".TABLE_ENGINE." AS `gb`
INNER JOIN ".TABLE_RETAILERS." `r` ON (`gb`.`retailerId` = `r`.`ENGINE_Retailer_Id`)
WHERE (`r`.`useMAINfeed` = '1' AND `r`.`authorised` = '1')
ORDER BY `retailerName` ASC LIMIT 0, 10";

$result = $db->query($query);

while($row = mysql_fetch_assoc($result))
{

$insert = array();
$insert['retailerId'] = $db->mySQLSafe($row['retailerId']);

$insert['engineId'] = $db->mySQLSafe($row['engineId']);
$insert['engineUPN'] = $db->mySQLSafe($row['engineUPN']);

$insert['linkURL'] = $db->mySQLSafe($row['link']);
$insert['imageURL'] = $db->mySQLSafe($row['image']);

$insert['title'] = $db->mySQLSafe($row['title']);
$insert['description'] = $db->mySQLSafe($row['description']);
$insert['brand'] = $db->mySQLSafe($row['brand']);
$insert['mpn'] = $db->mySQLSafe($row['mpn']);
$insert['price'] = $db->mySQLSafe($row['price']);
$insert['upn'] = $db->mySQLSafe($row['UPN']);
$insert['stock'] = $db->mySQLSafe($row['stock']);
$insert['condition'] = $db->mySQLSafe($row['condition']);

$insert['dateUpdated'] = $db->mySQLSafe($row['dateUpdated']);

$insert['countryId'] = $db->mySQLSafe($row['countryId']);
$insert['languageId'] = $db->mySQLSafe($row['languageId']);
$insert['currencyId'] = $db->mySQLSafe($row['currencyId']);

$qry = "SELECT `id` FROM ".$insertTable." WHERE
(`title` LIKE ".$db->mySQLSafe($row['title'])." AND
`brand` LIKE ".$db->mySQLSafe($row['brand'])." AND
`upn` LIKE ".$db->mySQLSafe($row['UPN']). " AND
`retailerId` = ".$db->mySQLSafe($row['retailerId']). ") OR

(`engineUPN` = ".$db->mySQLSafe($row['engineUPN'])." AND
`engineId` = ".$db->mySQLSafe($row['engineId']).") ";

//$numrows1 = $db->select($query);

if($db->numrows($qry)>0) {

$existing = mysql_fetch_row($db->query($qry));
// Update Row.
$db->update($insertTable, $insert, "`id` = ".$db->mySQLSafe($existing['id']));
//echo $existing[0]['id']." Updated<br />";
unset($existing);
$addedRows++;

} else {

$db->insert($insertTable, $insert);
$addedRows++;
//echo $db->insertid(). " Added<br />";
}

unset($insert);


} // end while[/PHP]

OK, to any fluent programer it's pretty obvious what this does...

Queries a table, extracts the info then searches another table to see if that row exists in the second table, if so update it and if not add it.

Pretty simple stuff ehh? - Not with 2,000,000 rows it's not, it takes about 3 days!

Someone please tell me how do do this with mysql alone or at least an example of what I have to do.

..PHP i'm great at, mysql don't have much idea, apart from a simple table join or select statement.

Please HELP!?
Oct 5 '07 #1
Share this Question
Share on Google+
1 Reply


pbmods
Expert 5K+
P: 5,821
Heya, iceomnia. Welcome to TSDN!

Try using REPLACE ... SELECT:
Expand|Select|Wrap|Line Numbers
  1. REPLACE
  2.     INTO
  3.         `table`
  4.         (
  5.             `col`,
  6.             .
  7.             .
  8.             .
  9.         )
  10.     SELECT
  11.             `col`,
  12.             .
  13.             .
  14.             .
  15.         FROM
  16.             `table2`
  17.         .
  18.         .
  19.         .
Note that by using REPLACE, you don't have to worry about existing rows.

http://dev.mysql.com/doc/refman/5.1/en/replace.html
Oct 6 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.