Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Using SQL to make a checkbox list

1 view
Skip to first unread message

Brown Cat

unread,
Oct 31, 2009, 3:03:20 PM10/31/09
to
To borrow the Student/Class example again, support I have the following
table:

Student
Class
Student_Class

The last table links Student and Class.
On the front-end (namely, a website) I want a page where I can see and
modify information about a student. This will show me what classes a
student is in as well as which they aren't in. This will be a list of
check boxes with a check in each box that the student is studying.
Like so:

[ ] Maths
[X] Science
[X] Art
[ ] Geography

I'm struggling to work out how to do this. Obviously, I can use a bit of
front-end coding to get a list of Classes and then a list of Classes the
Student is in and update an array as needed. But I'm wondering if SQL
has some way of doing this?

toby

unread,
Oct 31, 2009, 3:54:04 PM10/31/09
to

SELECT class.id, class.name,
student_class.class_id IS NOT NULL AS checked
FROM class
LEFT JOIN student_class ON student_class.class_id = class.id
AND student_class.student_id = X

You should have a UNIQUE constraint on student_class
(class_id,student_id) - I would make this the primary key.

johannes keßler

unread,
Nov 2, 2009, 8:12:55 AM11/2/09
to
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hallo,

are you want to know the SQL part or the front-end part ?

For the SQL part toby already answered, but for the frond end part you need a
programming languge which creates the correct output for your desired medium.
eg. if you want to display this in a browser you need to create the correct HTML
date.

SQL alone can not do this.

regards,
johannes keßler
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.13 (GNU/Linux)

iEYEARECAAYFAkru2tcACgkQE++2Zdc7EtfgBACdGeWAZCbGjPH4I26AQIDG85zc
HQIAnjFTVDTJGC7bP7qTc7aDSq4Hs9dd
=FIOs
-----END PGP SIGNATURE-----

Captain Paralytic

unread,
Nov 2, 2009, 9:13:12 AM11/2/09
to
On 2 Nov, 13:12, johannes keßler <m...@bananas-playground.net> wrote:
> Hallo,
>
> are you want to know the SQL part or the front-end part ?
>
> For the SQL part toby already answered, but for the frond end part you need a
> programming languge which creates the correct output for your desired medium.
> eg. if you want to display this in a browser you need to create the correct HTML
> date.
>
> SQL alone can not do this.

Well that's not totally true. Judicious use of the MySQL BiFs (e.g.
CONCAT, CONCAT_WS, REPLACE, GROUP_CONCAT, ...) would enable one to
build the required string, but I can't say it would be my first choice
of method.

johannes keßler

unread,
Nov 2, 2009, 9:23:28 AM11/2/09
to
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Well true, but this would be a really bad choice ;-)

regards,
johannes keßler
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.13 (GNU/Linux)

iEYEARECAAYFAkru62AACgkQE++2Zdc7EtcXhgCfcfj06mBZDIvTgVFHhFEQVMQf
fr0AnjkkJugPL3zJH0wMmTq/ZJ6NHbSI
=IuY/
-----END PGP SIGNATURE-----

0 new messages