Now I'm working on Edit page..
and another question appeared... I have lots of checkboxes with the styles of music.
When editing, how can I make these checkboxes to be checked with the options that the user selected when signed up ?
Thanks again !
: Worked !
:
: I didn't know this way to do it.. but it's quite simple
!
:
:
: thanks a lot !!
:
:
: : I don't know how you made your tables, but if you did it the way I suggested then you can't directly join the Genre and Users tables, you need to use the 3rd GenreUsers table I mentioned. I never learned the INNER JOIN syntax, so I join the tables the old way. If you want to use INNER JOIN you'll have to change part of the query. But here is what I would use:
: :
: :
: : SELECT DISTINCT U.*
: : FROM GenreUser GU, Users U
: : WHERE GU.UserID = U.UserID <== This joins the tables
: : AND GU.GenreID IN (1, 2) <== This finds similar music interests
: : AND U.City = 'Sao Paulo' <== This limits to the same city
: : AND U.UserID <> SEARCH_USER_ID <== This makes sure the
: : user who is searching is
: : not returned as a match
: :
: :
: : Not tested, but it should work.
: :
: :
: : : How would be the SQL string if I want to match others interests. Like besides Genre table, I also have Users table...
: : :
: : : If someone wants to search besides the Style of music, also wants to search for the same city...
: : :
: : : how it would be?
: : :
: : : SELECT DISTINCT * FROM genre INNER JOIN users WHERE genre IN (1, 2) AND users.city = 'sao paulo'
: : :
: : : Am I in the right way ?? Because it says there is an error...
: : :
: : :
: : : thanks!
: : :
: : :
: : :
: : : : That's the reason I love this forum and recommend this to others.. the answers are very fast
!!
: : : :
: : : : Well Manning, I tought many ways to do that, and one of them was like this you suggested, but I didn't know how would be the SQL string..
: : : :
: : : : I'll try this one you wrote
: : : :
: : : : Thanks a lot !!
: : : :
: : : :
: : : : :
This message was edited by Manning at 2003-12-12 12:30:46
: : : : : : Ok, I've a sign up form that has some checkbox. Each check box has one style of music (like pop, rock, dance, techno, etc) and the user has to choose the styles that he likes most.
: : : : : :
: : : : : : Ok, then I get all the data (name, city, country and music style) and put it into a DB
: : : : : :
: : : : : : In DB, the result appears like "1, 3, 4" for example... in this example the value 1 = rock, 3 = pop, 4 = techno
: : : : : :
: : : : : : But I wanna make a search form... in this search form, the user can choose to search for people that likes the same music style.. for example the user wants to search for people who likes rock and techo (1,4)
: : : : : :
: : : : : : How would be the SQL string for this ??
: : : : : :
: : : : : :
: : : : : : I hope someone can help me...
: : : : :
: : : : : Oops didn't meant to post that so fast..anyway..
: : : : :
: : : : : What you have there is a many to many relationship because one person could like many styles of music, and one style of music could be liked by many people. What I would do is this:
: : : : :
: : : : : Have a user table. I'm assuming you already have this, and that it has a unique identifier of some sort (preferably a UserID)
: : : : :
: : : : : Have a genre table. Each style of music should have an entry in this table, again each should have a unique ID.
: : : : :
: : : : : Have a UserGenre table. This table will only have two columns, a UserID and GenreID, both foreign keys to the above tables, and when combined they form a primary key. Every user should have one or more records in this table to indicate which styles of music they like.
: : : : :
: : : : : This means your ASP is going to have to change a bit. When you insert the users style selections, you'll use something like:
: : : : :
: : : : :
: : : : : for I = 1 to Request.Form("Styles").Count
: : : : : SQL = "INSERT INTO UserGenre (UserID, GenreID) VALUES (UserID, " & Request.Form("Styles")(I) & ")"
: : : : : Conn.Execute(SQL)
: : : : : next
: : : : :
: : : : :
: : : : : I marked UserID in red because it should be the value of the current user's ID, not the actual string "UserID"
: : : : :
: : : : : And now finally for the search feature, you would use this:
: : : : :
: : : : :
: : : : : SQL = "SELECT DISTINCT UserID FROM UserGenre WHERE GenreID IN (" & Request.Form("SearchStyles") & ")"
: : : : : Conn.Execute(SQL)
: : : : :
: : : : :
: : : : : That'll give the UserID of any user that has at least one style in common with the ones listed in Request.Form("SearchStyles")
: : : : :
: : : : : All this was done without any testing, so there may be errors, but that's the gist of how I would set things up. If you want to keep the user's style selections as a text column in the user table, that's still possible, but IMO it'll complicate things compared to this layout.
: : : : :
: : : :
: : : :
: : :
: : :
: :
: :
:
:
