update / lookup problem with MySQL

[mschultem]

hi,
i have got two tables demography and invited

demography:
pid = participant id
primary_lang = language
acc_active = flag on active account
invited:
pid = foreign key from demography
experiment_id = id for experiment

i want to select 10 entries from demography, add them to invited and do the same again for, say, another 20 entries.
the selected entries (based on pid) from demography should not be in invited (participants who have been invited should not be invited twice)

here is my mysql code:

$query="SELECT * FROM demography WHERE acc_active = '1' AND primary_lang='en' AND pid!=(SELECT distinct pid from invited) LIMIT 10";
$result=mysql_query($query);
$num=mysql_numrows($result);
while($row = mysql_fetch_array($result))
{
//show me results
echo $row['pid']. " - ".$row['email'];
echo "<br>";
// write into invited table
$writequery = "INSERT INTO invited (pid, experiment_id, invited_time) VALUES ('".$row['pid']. "', '$ExperimentID', now())";
$writeresult=mysql_query($writequery) or die ("Error - ".mysql_error()."");
}

if the invited table is empty it does not work at all - guess that i have something to add to the (SELECT distinct pid from invited) statement. when i enter one row into invited that generates a result i can add the 10 rows from demography but only once. with the second try i get an error Warning: mysql_query() [function.mysql-query]: Unable to save result set in ...
in line 20 which is: $result=mysql_query($query);

any ideas?
thanks for your help
best
m

[mschultem]

did a little research - could it be that the problem lies within the!=?

does this check for only one value and not for a group of values?

thanks for your help!

best
m

[mcibor]

You could be right, then the solution would be to use:

$query="SELECT * FROM demography WHERE acc_active = '1' AND primary_lang='en' AND pid NOT IN (SELECT distinct pid from invited) LIMIT 10";