php distinct problem

Basically what I want to do is select the "Chain_store" AND "Shop" from this database so that I can display the Shop.

$Chains = mysql_db_query("*******","SELECT DISTINCT Chain_store FROM customer WHERE Chain_store != ' '");

PHP:

 

Try <> instead of !=

!= is a php construct not an sql one.

("*******","SELECT DISTINCT Chain_store FROM customer WHERE Chain_store <> ' '");

Although, you probably need to test for null and empty string rather than just a space?

("*******","SELECT DISTINCT Chain_store FROM customer WHERE Chain_store <> ' ' AND Chain_store <> '' AND Chain_store is not NULL");


Other than that, your query is fine.

 

You can use != that is a logical operator in MySQL

 

ok, my bad!

but, in re-reading the question, I think i may have misunderstood anyway. I think perhaps the poster wants distinct chain stores and distinct shops to be returned? But, I'm not really clear. So, Greenmethod, can you expand on your question a little?

If you have seperate columns called Chainstore and Shop and on any row just one of those columns is filled in (which breaks 3rd normal form but never mind) then you'd do something akin....

SELECT DISTINCT Chain_store FROM customer WHERE Chain_store != ' '
UNION
SELECT DISTINCT Shop FROM customer WHERE Shop != ' '

But I still think you need to check for nulls and empty strings unless you are explicitly stuffing spaces into those columns when you insert the records.

 

select distinct(fieldname) from table name where <condition>

 

if i do this:

$Chains = mysql_db_query("4MCS","SELECT DISTINCT Chain_store,Shop FROM customer WHERE Chain_store != ' '");

PHP:

It does basically what i want it to do, but it puts EVERY store of every chain in the drop down box. I want one store from each chain in the box.

 

Ahh, I see. This is a common question.

In that case, you want....

SELECT DISTINCT Chain_store,MAX(Shop) FROM customer WHERE Chain_store != ' ' GROUP BY Chain");

or

SELECT DISTINCT Chain_store,MIN(Shop) FROM customer WHERE Chain_store != ' ' GROUP BY Chain");

Both will give you one store from each chain - either the first alphabetically, or the last.

 

Thanks so much for your help! When I put these in, it comes up with an empty drop down box.. I took out the group by and still the same thing until I take out the min or max.. any suggestions? Is there a good page that I can read about this?

 

oops, slight bug in my syntax....

$Chains = mysql_db_query("4MCS","SELECT Chain_store,MAX(Shop) FROM customer GROUP BY Chain_Store HAVING Chain_store != ' ' ");

 

That actually worked when I took the MAX() out, and i'm not exactly sure why. I would think that if there isn't a distinct in there, it would pull all of the customers that are in a chain store, not just one.. can you explain this to me?

 

The group by clause groups everything up, so you only get one row per grouped item.

It's slightly different to distinct - mainly in terms of what it does with the rest of the columns.