exclude several records from mysql

A rather simple operation, but I'm stuck : (

[punisa]

hello !

I want to output my articles, with an exception of few entries, this is what I tried:

$sql = "SELECT * FROM dnews WHERE newsid!= ('17' ¦¦ '25') ORDER BY date DESC";

Obviously, I want to show every record (newsid), apart from 17 and 25.

I also tried:
WHERE newsid!= 17 ¦ 25
WHERE newsid!= ('17' OR '25')
WHERE newsid!= '17' ¦¦ '25'

What would be the right way to do this?

Thank you guys ! : )

[janharders]

WHERE newsid NOT IN(17, 25)

should work.

[punisa]

janharders, what can I say... thank you ! : ))

It works perfect !

Btw, where could I look to find all these parameters like "NOT IN". When working with PHP, google is my best friend, seems it always fires up what I need. But when it comes to MYSQL... it's a windy road : P

[rocknbil]

Just a supplement, you have to be cautious with or. If you want to exclude you should be using and (but in this case in(list) is much more efficient.) Record 17 is not record 25, so when you say "not 17 or 25" when it gets to 17, it's not 25 - so it returns true, and vice versa. It would also work with

... where newsid <> 17 and newsid <> 25

[janharders]

the mysql documentation will get you started, but I always found that it's mostly a experience thing to know how to get what you want. maybe have a look at [dev.mysql.com...] for functions and operators to be used in the where-part and [dev.mysql.com...] for the general select-syntax (especially all those fine things like join, group by etc - while you may not have to use them yet, it's good to skip through the page so you know what you need when you need it and can dive into the documentation). I hope those urls are ok, if they're not and are removed, ask your favorite search engine about the mysql select syntax and start from there.

[ZydoSEO]

I agree with Rocknbil. In general, the use of NOT IN is inefficient. But the syntax for most DBs would be:

SELECT *
FROM dnews
WHERE newsid NOT IN (17,25)
ORDER BY date DESC

With a list of only 2 newsids to be removed you may be better off hardcoding them as WHERE newsid <> 17 AND newsid <> 25. This works great when you have a hand full of items to exclude and is a lot more efficient.

If you run this SELECT from several places on your site I would suggest making it a stored procedure and having your PHP/ASP[X] call the stored proc everywhere you need to perform the SELECT. Should the list of items to exclude change, you simply need to modify the stored procedure and it will take affect across the site instead of wondering which PHP/ASP[X] files include the SELECT w/ hardcoded 'WHERE newsid <> 17 AND newsid <> 25' and then having to modify multiple PHP/ASP[X] files.

If you think the list of excluded items could be eventually grow to be large (say, 10s, 100s or 1000s) I'm not sure I'd want that many hardcoded 'AND newsid <>' in my WHERE clasue. In that case I would probably have an exclusion table that contains a list of newsids to be excluded. To exclude an item I would add a row to the exclusion table. I would have my stored procedure for performing the SELECT from the dnews table use a left outer join to exclude the items similar to:

SELECT d.*
FROM dnews AS d
LEFT JOIN newsexclusions AS ne ON d.newsid= ne.newsid
WHERE ne.newsid IS NULL
ORDER BY date DESC

I believe (not tested, winging it) that will show only items that exist in dnews but do NOT exist in newsexclusions. All one would need to do to exclude a new newsitem (say, newsid=2048) would be to insert a row in the newsexclusions table like:

INSERT INTO newsexclusions (newsid) VALUES (2048)

No code change... simply a data script.

[edited by: ZydoSEO at 4:13 pm (utc) on Dec. 20, 2008]