mysql select where....

[jay7981]

here’s what i want to do but not sure how to do it…

lets say i have

[code]table1
id name field3(aid) field4(level)
1 bob data3 data4

table2
id name field5(auth) field6(class)
4 james data5 data6[/code]
i want to do something like this

select name, field3, field4 from table1 where table1.data3 is not in table2
insert results from previous select into table2

so something like this would have to work
select name, aid, level from table1 where aid is not in table2

insert name, auth, class into table2 values (name,aid,level)

[Topcoder]

Actually, you can accomplish everything in just the insert statement.

[php]Insert into table2 (name,auth,class) select name, aid, level from table1
where not exists (select 1 from table2 where table1.data3 = table2.auth)[/php]

[jay7981]

hey thanks man, i actually was going to come mark this solved, i ended up doing this as i needed the information elsewhere in another function,

[php]ob_flush();
flush();
$res = $olddb->Execute(“SELECT
smbans__bans.ip,
smbans__bans.authid,
smbans__bans.name,
smbans__bans.created,
smbans__bans.length,
smbans__bans.reason,
smbans__bans.aid,
smbans__bans.adminIp,
smbans__admins.aid,
smbans__admins.user,
smbans__admins.authid
FROM
monkeys_BMMAMX.smbans__bans smbans__bans,
monkeys_BMMAMX.smbans__admins smbans__admins
WHERE
smbans__bans.aid = smbans__admins.aid
AND
smbans__bans.authid NOT IN (SELECT player_id FROM amxbans__bans)”);

$newdb = ADONewConnection($todsn);
if (!$newdb) {
    die("Failed to connect to SourceBans database");
}
$newdb->Execute("SET NAMES utf8");
$ins = $newdb->Prepare("INSERT INTO amxbans__bans
	(player_ip,player_id,player_nick,ban_created,ban_length,ban_reason,admin_ip,admin_id,admin_nick,ban_type) 
	VALUES 
	(?,?,?,?,?,?,?,?,?,?)");
while (!$res->EOF) {
    $vals = array(
        $res->fields[0],
        $res->fields[1],
        $res->fields[2],
        $res->fields[3],
        $res->fields[4] / 60,
        $res->fields[5],
        $res->fields[7],
        $res->fields[10],
        $res->fields[9],
        S
    );
    
    foreach ($vals as $ind => $cur) {
        if (is_null($cur)) {
            $vals[$ind] = '';
        }
    }
    $newdb->Execute($ins, $vals);
    $res->MoveNext();
}
$count = $res->RecordCount();
echo "Done!!<br />";
// get and print number of rows in resultset
echo $count . " SMBans transfered to AMX<br />";[/php]