mysql sorting by checkbox array data

Question

Hello all, I have a quick problem with mysql query.

I'm trying to do a selection and I'm not sure it can be done - or if I'm doing it right.

I have a table with a field called ListeningMethod, which contains values from a comma separated array values, inserted from html check boxes. There are 5 possible entries and the field can have all, or none of the checkbox values in it.

The sample data would be similar to this:

NOLISTEN,RADIO,INTERNET,SATELLITE,MOBILE

What I'd like to do is sort the data, but do it by counts. So, if there are 10 appearances of "RADIO", 5 of "MOBILE" and 3 of "SATELLITE" spaced over 10 records, then searching through 100 records, it should be able to show and sort those mentioned results, with the "order by" set to the most common listeningmethod at the top.

I tried giving it a go, but didn't go too well.

Any thoughts? Thanks.

SELECT 
ListeningMethod, 
ListeningMethod REGEXP ("NOLISTEN") as ViewNOLISTEN,
ListeningMethod REGEXP ("RADIO") as ViewRADIO,
ListeningMethod REGEXP ("INTERNET") as ViewINTERNET,
ListeningMethod REGEXP ("SATELLITE") as ViewSATELLITE,
ListeningMethod REGEXP ("MOBILE") as ViewMOBILE
FROM VAT

I tried adding more, such as "COUNT(ViewRadio) CountRadio", but it says that column doesn't exist, so I'm a little lost.

Example Insert statement:

INSERT INTO `VAT` (`ListeningMethod`) VALUES ('NOLISTEN,RADIO,INTERNET,MOBILE');
INSERT INTO `VAT` (`ListeningMethod`) VALUES ('RADIO');
INSERT INTO `VAT` (`ListeningMethod`) VALUES ('RADIO');
INSERT INTO `VAT` (`ListeningMethod`) VALUES ('RADIO');
INSERT INTO `VAT` (`ListeningMethod`) VALUES ('RADIO');
INSERT INTO `VAT` (`ListeningMethod`) VALUES ('RADIO');
INSERT INTO `VAT` (`ListeningMethod`) VALUES ('INTERNET');
INSERT INTO `VAT` (`ListeningMethod`) VALUES ('RADIO'); 
INSERT INTO `VAT` (`ListeningMethod`) VALUES ('RADIO');
INSERT INTO `VAT` (`ListeningMethod`) VALUES ('NOLISTEN');
INSERT INTO `VAT` (`ListeningMethod`) VALUES ('INTERNET');
INSERT INTO `VAT` (`ListeningMethod`) VALUES ('RADIO');
INSERT INTO `VAT` (`ListeningMethod`) VALUES ('INTERNET');
INSERT INTO `VAT` (`ListeningMethod`) VALUES ('RADIO');
INSERT INTO `VAT` (`ListeningMethod`) VALUES ('RADIO');
INSERT INTO `VAT` (`ListeningMethod`) VALUES ('RADIO');
INSERT INTO `VAT` (`ListeningMethod`) VALUES ('MOBILE');
>

inserting comma seperated values is not preferrable instead of that insert listenmethod as 1,2,3,4,5 based on the listening method so that you can get the highest listening method using order by easily — Sivagopal Manpragada
– Sivagopal Manpragada, Commented Nov 15, 2012 at 8:51
This would be so much easier if you used a separate table with one method per row rather than a comma-separated list. — Barmar
– Barmar, Commented Nov 15, 2012 at 9:20
Hi @Barmar, yes in an ideal world yes, but I needed this running very quickly and didn't have the time. Thanks again. — vr_driver
– vr_driver, Commented Nov 15, 2012 at 10:26

Tariq M Nasim · Accepted Answer · 2012-11-15 09:50:17Z

1

You could use separated table as suggested by Barmar in the comment, other(un-)wise you can create a temporary table like that using union as given below: (If you have no problem with union and sub-query):

SELECT * FROM
(
    SELECT "NOLISTEN" AS "ListeningMethod", COUNT(ListeningMethod) as "AMOUNT" FROM VAT WHERE ListeningMethod LIKE "%NOLISTEN%"
    UNION
    SELECT "Radio" AS "ListeningMethod", COUNT(ListeningMethod) as "AMOUNT" FROM VAT WHERE ListeningMethod LIKE "%RADIO%"
    UNION
    SELECT "INTERNET" AS "ListeningMethod", COUNT(ListeningMethod) as "AMOUNT" FROM VAT WHERE ListeningMethod LIKE "%INTERNET%"
    UNION
    SELECT "SATELLITE" AS "ListeningMethod", COUNT(ListeningMethod) as "AMOUNT" FROM VAT WHERE ListeningMethod LIKE "%SATELLITE%"
    UNION
    SELECT "MOBILE" AS "ListeningMethod", COUNT(ListeningMethod) as "AMOUNT" FROM VAT WHERE ListeningMethod LIKE "%MOBILE%"
) AS VAT2 ORDER BY AMOUNT DESC;

edited Nov 15, 2012 at 9:50

answered Nov 15, 2012 at 9:44

Tariq M Nasim

1,27811 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

vr_driver Over a year ago

Thankyou, thankyou thankyou Heartbeat. That did the trick! I know I could have had look up tables, but I needed something solved really quickly. I appreciate the quick response to all.

Collectives™ on Stack Overflow

mysql sorting by checkbox array data

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related