Remove one list of names from another table

[DLR]

Hi,

 

There must be a simple way of doing this, but I seem to have missed it and I cannot get my own method to work

 

I have a list of emails and names (subscribers) and another list of unsubscribed emails with names (unsubscribed). (This is a legacy from a previous emailing programme - Mailloop6).

I want to create one table (final_list) where the unsubscribed names have been removed.

 

My methodology is to create an array from "subscribers", create an array from "unsubscribed" , then loop through "subscribers" and save the names that are not found in "unsubscribed", into a new table "final_list". I have checked, the "sucribers" and "unsubscribed" arrays hold 4700 and 666 records each - so the creating array part works. My problem is in inserting into "final_list".

 

Each table and array has 3 fields :email, first_name, last_name

 

foreach ($subscribed as $key => $value) {
if(in_array($value,$unsubscribed)) {
	//do nothing as we do not want this information to be stored	
               } else {
	$sql = "INSERT INTO final_list
		(email,first_name,last_name)
		VALUES
		('" . $value[$key]['email'] . " ',
		'" . $value[$key]['first_name'] . " ',
		'" . $value[$key]['last_name'] . " ') 
		";
	$res = mysql_query($sql);
	if(! $res) {
		echo '<br>..no record inserted into final list ' . $key. '<br>';
	}
}
}

Thanks for reading this - any help will be appreciated

[Kays]

INSERT INTO `final_list`

(

    SELECT *

    FROM `subscribers` S

    WHERE S.id NOT IN

    (

        SELECT U.id

        FROM `unsubscribed` U

    )

)

 

Naming your table `final_list' is not very informative. Also, you may want to do a bit of research for INSERT ... SELECT before you blindly run the SQL above. And if you do, don't run that on a production system first time.

 

Also, a LEFT JOIN work just as well and it is more efficient.

[DLR]

Thank you.

It is as if a light has gone on!

I am researching Select, but it would seem that your suggestion of left join will be more elegant.

Thanks again!