MySQL - help writing search query syntax

Question

I have a table(named Assignments) that looks like this:

Username    RandomWebsite1   RandomWebsite2   RandomWebsite3
-----------------------------------------------------------------
bugs bunny        1                0                1
elmer fudd        0                1                1
popeye            1                1                0
elvis             1                1                1
bruce lee         0                1                0

What I need is three different query's like: (ill do it in English language instead of my bad attempt at writing the syntax"

1. I need to add the usernames to a listbox if I want to search for all the people with RandomWebsite1 assigned(my program recognizes 1 as the value that it is assigned)

2. I need to search for "RandomWebsite1" and anyone who has "1" in that field needs to be added to my listbox. What would the MySQL syntax look like?

3. I need to search ALL COLUMNS in the table, no matter the name of the column since it can be generated randomly, and anyone who has "1" in that field needs to be added to my listbox. What would the MySQL syntax look like? I imagine there would have to be a wildcard in the somewhere like:

   SHOW 'Username' WHERE * = 1 or something.

I know my database design probably is bad, but it is what it is and cannot fix it now, but am open to suggestions. Basically have like 5000 users in this table.

Edited:

I need to be able to type "bugs bunny" into my search field... then it searches this table and will list me ALL of the websites that have been assigned (anything with a "1")... I hope this helps... thanks again.

Answer 1

Here's the query:

SELECT 'RandomWebsite1'
FROM Assignments
WHERE Username = 'bugs bunny' AND RandomWebsite1
UNION
SELECT 'RandomWebsite2'
FROM Assignments
WHERE Username = 'bugs bunny' AND RandomWebsite2
UNION
SELECT 'RandomWebsite3'
FROM Assignments
WHERE Username = 'bugs bunny' AND RandomWebsite3

Every time you modify the "website" columns, update the query too.

Answer 2

Ok to make it clear, you should have three tables to accomplish this. First table:

Users

User_id      UserName
---------------------
0            bugsbunny
1            elmer fudd
2            popeye

Second table:

Assignments

Assignment_id        website_name
-------------------------------------
0                      www.url1.com
1                      www.url2.com
2                      www.url3.com

And finally:

UserAssignments

User_id         Assignment_id
------------------------------
0                   1
0                   2

With this setup, you can add as many Users and Assignments as you like, and you can link them together through the UserAssignment table, WITHOUT generating random columns dynamically!

So to see all of the websites that have been assigned to user bugsbunny you would do this:

SELECT a.website_name FROM Assignments a 
JOIN UserAssignments ua ON ua.Assignment_id = a.Assignment_id
JOIN Users u ON u.User_id = ua.User_id
WHERE u.UserName = 'bugsbunny'