Note, this is getting out of Zend Framework land....
A temp table can use any of the database engines; memory just happens to be
the fastest :-)
Now for a more practical example:
lets say you want to import product_metrics (I don't know what your columns
look like, so I'll make up something)
So assuming:
product (product_id, etc)
product_metrics (product_id, field1, field2)
ok, dumb example since metrics would only allow one row per product, but oh
well...
<?php
$db = Zend_Db::factory('mysql_pdo', $config);
//make an empty copy of the product_metrics table
$db->exec("CREATE TEMPORARY TABLE tmp_product_metrics LIKE
product_metrics");
//dump data into the empty copy
$stmt = $db->prepare("INSERT INTO tmp_product_metrics (product_id, field1,
field2) VALUES (?,?,?)");
foreach($xml as $row){
$stmt->execute(array($row['product_id'], $row['field1'],
$row['field2']));
}
unset($stmt);
//insert into the live table from the temp table, but only where a product
exists
$db->exec("
INSERT INTO product_metrics (product_id, field1, field2)
SELECT tmp.product_id, tmp.field1, tmp.field2
FROM tmp_product_metrics tmp
JOIN product USING (product_id)
ON DUPLICATE KEY UPDATE
field1 = VALUES(field1),
field2 = VALUES(field2)
");
?>
That's the general gist of it.
So rather than massaging/validating your data in PHP, and then sending it to
the database, you pass the data straight up, then do the massaging in the
database.
Cheers,
David
--
View this message in context:
http://zend-framework-community.634137.n4.nabble.com/Database-Optimization-Normalization-tp3426978p3432344.html
Sent from the Zend Framework mailing list archive at Nabble.com.
--
List: [email protected]
Info: http://framework.zend.com/archives
Unsubscribe: [email protected]
