SQL "IN" statement help required please

[gerkintrigg]

Hello. I'd like to work out how to make the following work:

 

$sp_sql=mysql_query("select DISTINCT poked from pokes where poked IN(".$poked_by.") && poke_deleted='n' && !poked IN(".$double_pokes.")");
		while ($sp= mysql_fetch_array($sp_sql)){
		$single_pokes.=','.$sp['poked'];
		}

This is for this website... it's nothing rude...http://www.pigorpoke.com

 

I'm trying to select all from an array where the results' poked value are NOT in a string of numbers.

 

 

[Jessica]

$sp_sql=mysql_query("select DISTINCT poked from pokes where poked IN($poked_by) && poke_deleted='n' && !poked IN($double_pokes)") or die(mysql_error());

 

Get any errors?

[gerkintrigg]

Nope, no errors @ this stage, jesirose, but it's still not working ;o)

 

I also tried

$sp_sql=mysql_query("select DISTINCT poked from pokes where poked IN($poked_by) && poke_deleted='n' && !poked IN($double_pokes)") or die(mysql_error());
		while ($sp= mysql_fetch_array($sp_sql) or die (mysql_error())){
		$single_pokes.=','.$sp['poked'];
		}

but this displays nothing...

 

Perhaps I need a left join?

 

i thought the code you wrote would work, and even tried it before trying to get some help on the forum. *shrug* weird innit?

[Jessica]

Do this:

print "select DISTINCT poked from pokes where poked IN($poked_by) && poke_deleted='n' && !poked IN($double_pokes)"

What does it show? If the values aren't what you're expecting, there's the problem.

$poked_by and $double_pokes need to be comma separated strings like 1,2,3,4

[gerkintrigg]

select DISTINCT poked from pokes where poked IN(35,49,103,90,112,123,118,108,134,144,139,39,9) && poke_deleted='n' && !poked IN(39,112)

[gerkintrigg]

when using php my admin with that query...

it says:

MySQL returned an empty result set (i.e. zero rows). (Query took 0.0063 sec)

 

I reckon the code is wrong near where it says: !poked IN(39,

[shoz]

Use NOT IN.

SELECT
DISTINCT poked
FROM
pokes
WHERE
poked IN($poked_by) && poke_deleted='n' && poked NOT IN($double_pokes)

 

You could also use PHP to remove the entries in $poked_by that are also in $double_pokes to avoid having to use the NOT IN.

[gerkintrigg]

shoz, excellent!! Thanks to both of you for your attention to this problem. it now works perfectly.

 

Regards,