Exclude empty fields from MySQL database

[dbzfyam]

I need to extract all manufacturers from a table to generate a manufacturer list. However, not all products may have a manufacturer filled in, so when I generate a list, I also get empty results. Example:

1. Axiomtek
2. Fabiatech
3. Acrosser
4.
5. ICOP
6. Ibase
7. Portwell

This is probably possible through PHP, but I want to do this through the database. How can I do something like this (Obviously, this doesn't work):
SELECT DISTINCT manufacturer FROM mp_products_all WHERE manufacturer!=''

Thanks in advance for the help,
Stefan

[syber]

If you allow NULL for manufacture you could do this:

SELECT DISTINCT manufacturer
FROM mp_products_all
WHERE manufacturer IS NOT NULL

[seanpecor]

I think Syber is on the money. If!= doesn't work, it means the manufacturer column in your mp_products_all table allows a NULL value. To make absolutely sure you're getting all columns you might then need to:

select distinct manufacturer from mp_products_all where manufacturer is not null and manufacturer<>'';

That way, if someone entered in a blank manufacturer string (rather than a default null value), that would get included.

I think if you allow searching products based on manufacturer it might be sensible to create an index on the manufacturer column. Then your!= logical operator would work fine. Something like the following two queries would get you there:

alter table mp_products_all modify column manufacturer char(128) not null default '';
alter table mp_products_all add index (manufacturer);

Otherwise if you've got 20,000 products and 700 manufacturers and you have a manufacturer search with no index then query times would be cumbersome. Come to think of it, even a "select distinct" would benefit from a manufacturer index.

Sean

[dbzfyam]

Thank you both for the quick response. Since the values aren't really NULL, I can't use Sybers solution. I just tried Seans solution and it works perfectly now. Eventually I have to search on manufacturer aswell, so I added the index (in case I forget to do so).

Thanks again for your help!
Stefan