Q:

mysql - move rows from one table to another or update

PLOT : i have a PAD websites with 2 DB's : submitdata and maindata .
The submitdata DB has all the columns as maindata DB plus another 3 columns :
MAINDATA COLUMNS     : name link email website .... progid
SUBMITDATA COLUMNS : name link email website .... progid, backlink, affiliateid
I must MOVE all rows from submitdata DB to maindata DB but only IF backlink !='' and IF affiliateid != $affiliateid .
Another problem is if the progid is allready in the maindata DB and it this case I must UPDATE that row with the new data 
... another problem is that i have about 40.000 rows so SELECT * FROM submitdata kinda kills my computer .
Any ideea on how to do that in PHP ?

A:

Try this:
EXPLAIN SELECT * FROM submitdata;

Perhaps it will tell you which fields you can give an INDEX to (this could speed up the query time drastically). Then, if you're not going to move records whose backlink and affiliateid fields are empty, then you can reduce the result set from the query by adding a WHERE clause.
$result = mysql_query('SELECT * FROM submitdata WHERE backlink <> "" AND affiliateid <> ""');
if ($result) {
    while ($row = mysql_fetch_assoc($result)) {
        $newResult = mysql_query(sprintf('SELECT name,progid FROM maindata WHERE progid = %d', $row['progid']));
        $row2 = mysql_fetch_row($newResult);
        if ($row2) {
            // do UPDATE query
        } else {
            // do INSERT query
        }
    }
}

