[Resolved] sql query to return 'yes' of enum fields only

laugharne

Can I run a sql query to return all 'yes' fields only from table. I have table several enum fields either 'yes' or 'no' and one called 'model'

I have before run a query based on a single enum field SELECT FROM db WHERE field1='yes' and model='xxx'. But cannot figure out how to
SELECT FROM DB WHERE ?all fields?='yes' and model='xxx'.

I have tried LIKE but no joy.

OR do I have to retrieve all and use ECHO to print fields'yes'

charles

paulnaj

Hi Charles,

You could try ...

SELECT model, IF( field1='yes' && field2='yes', 'yes', 'no') as allfields
FROM DB
WHERE allfields='yes'
AND model='xxx'

... I think that'll work!

Paul 🙂

laugharne

Thanks- looks good- will try Monday as I mislaid my memstick, so could not bring pages home.
Thanks- will mark as resolved, re-submit if i still have a problem.

laugharne

Just of course thought- fields in table are not called field 1 etc- actually things like kitch_print, weigh_scales etc ( its for a cash register database of peripherals.
I could of course either rename fields to field 1, 2 etc, which will confuse an inputter(me), or how do I run using the real field names as above- some 20 in total.

Sorry

Charles

paulnaj

Just use the names of the fields and 'join' the equalities together with logical ANDs (&&).

Here's with 3 fields ...

SELECT model, IF( kitch_print='yes' && weigh_scales='yes'&& other_value='yes', 'yes', 'no') as allfields
FROM DB
WHERE allfields='yes'
AND model='xxx'

Paul.

laugharne

thanks

Lars_Berg

That will not work. You cant use aliases in the where clause.

You need to specify the columns in the where clause

where 'yes' in (kitch_print,weigh_scales,
 other_value)

laugharne

As below you mean ?

SELECT model_no FROM tillaccess WHERE 'yes' in (kitch_print, weigh_scales)

Run in PHPmyadmin still pulls out all models, there are only 3 models with weigh_scales and kitch_print . If reduce to just weigh_scales, works okay

laugharne

This does seems to work
SELECT model_no FROM tillaccess WHERE 'yes' in (weigh_scales) AND 'YES' in (ink_print) AND 'YES' in (bar_scan)

Lars_Berg

Basically, you are backed to your initial query. There is no shortcut, you must specify the individual columns.

I missed that you wanted that all columns to be yes.

a in (b,c,d)

is just an abbreviation of

a = b or a = c or a = d

laugharne

columns have to be yes to show what till can have certain peripherals.

I understand the logic of 'a=yes' in (b,c,d,e,f,) but were we not there before- or am i being dense. I can of course just write as works, but seems laborious, though only once so I could live with it. a in (b,c,d) fails, unless I have misunderstood. Would a fn of $a as 'yes' help, then run $a in (b,c,d,e,f)