Welcome to WebmasterWorld Guest from 54.159.19.75

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

PHP MySQL report

     
7:34 am on Oct 30, 2010 (gmt 0)

New User

5+ Year Member

joined:Oct 30, 2010
posts: 4
votes: 0


Hi Guys,
I have an array of data, that I want to compare against a field in my db, and drag out the items in the db that are NOT in the array..

Any ideas please...


TIA
8:38 am on Oct 30, 2010 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Apr 24, 2005
posts:697
votes: 0


Hi Sparkes, Welcome to WebmasterWorld

select * from table where field not in ("item1","item2","item3")

and so on.

You can create a comma seperated list of your array items by using php's implode function
10:55 am on Oct 30, 2010 (gmt 0)

New User

5+ Year Member

joined:Oct 30, 2010
posts: 4
votes: 0


perfect, it's too easy to start looking at complicated solutions, when a nice simple one liner is there..

Thank v much
11:49 am on Oct 30, 2010 (gmt 0)

New User

5+ Year Member

joined:Oct 30, 2010
posts: 4
votes: 0


Ahh one possible stumbling block, the array can have over 1000 elements ! Will that be ok ?
9:07 am on Oct 31, 2010 (gmt 0)

Senior Member

WebmasterWorld Senior Member 5+ Year Member

joined:Apr 30, 2007
posts:1394
votes: 0


There is a mysql setting "max_allowed_packet" and puts a cap to the max length of your mysql statement. Default is 1MB so you should be ok.
4:44 am on Nov 1, 2010 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Apr 24, 2005
posts:697
votes: 0


You might want to store those 1000+ items in a table and then run a join to grab only the results that dont have those vlaues. if the table is large then this IN() with 1k+ values might slow your query down
8:37 am on Nov 1, 2010 (gmt 0)

New User

5+ Year Member

joined:Oct 30, 2010
posts: 4
votes: 0


Am pleased to say that it works ok, no significant speed issue. In reality it doesn't pull that much data..

The DB only has 9k rows, so nothing to worry about there..