Have you come across a situation where you want to show Filtered Recordset results based on some Criteria/ conditions? Please Note that I am not talking about Filtering Recordset values but Filtered Recordsets.
e.g. you have a search - Result/Detail page in which some of Filteres Recordset Results are supposed to be shown only to authorised Members.
OR
You have Search - Result pages in which you have given the Users the option of "OR" and "AND" (sorry for my terrible english, to have an idea of what i am talking about see this search page).
We will go through the two Scenarios one by one.
Filtered Recordset Results are supposed to be shown only to authorised Members or any other criteria. |
Make a new Asp/Vbscript page, define and filter your Recordset asusuall.
Go to the code view and find your Recordset Code. Here i have defined a Recordset Album and filtered it based on the Session "Username" and a Database Field "fld_private" (Yes/NO Field) which may indicate that the particular Data entry is supposed to be shown to Authorised/logged Members
<%
Dim rsAlbum__MMColParam
rsAlbum__MMColParam = "1"
If (Session("MM_Username") <> "") Then
rsAlbum__MMColParam = Session("MM_Username")
End If
%>
<%
Dim rsAlbum
Dim rsAlbum_numRows
Set rsAlbum = Server.CreateObject("ADODB.Recordset")
rsAlbum.ActiveConnection = MM_ConAlbum_STRING
//mind the breaks consider two lines below as a single line
rsAlbum.Source = "SELECT * FROM tbl_album WHERE fld_owner = '" + Replace(rsAlbum__MMColParam, "'", "''") + "' AND fld_private = 0"
rsAlbum.CursorType = 0
rsAlbum.CursorLocation = 2
rsAlbum.LockType = 1
rsAlbum.Open()
rsAlbum_numRows = 0
%>
|
So what now?
Either we can define a new recordset and show the results based on the new recordset. If you want to do that go ahead. You kow better that you have to spent twice as much time as usuall.
OR you just make a little modification in the code and save yourself lot of time and Headache. Consider the part of the code:
//mind the breaks consider two lines below as a single line
rsAlbum.Source = "SELECT * FROM tbl_album WHERE fld_owner = '" + Replace(rsAlbum__MMColParam, "'", "''") + "' AND fld_private = 0" |
now modify it so it looks like this. Pay attention to the part of the code marked as RED:
IF Session("MM_UserAuthorization") = "User " THEN
//mind the breaks consider two lines below as a single line
rsAlbum.Source = "SELECT * FROM tbl_album WHERE fld_owner = '" + Replace(rsAlbum__MMColParam, "'", "''") + "' AND fld_private = 0 "
ELSE
//mind the breaks consider two lines below as a single line
rsAlbum.Source = "SELECT * FROM tbl_album WHERE fld_owner = '" + Replace(rsAlbum__MMColParam, "'", "''") + "' AND fld_private = 1 "
End IF |
you can see the difference ? The above code will show those Results to the USERS with STATUS "User" where "fld_private" is set to "0".
Similraly we show the Recordset Results based on the fact if the User has logged in or not.
IF Session("MM_Username") = "" THEN
//mind the breaks consider two lines below as a single line
rsAlbum.Source = "SELECT * FROM tbl_album WHERE fld_owner = '" + Replace(rsAlbum__MMColParam, "'", "''") + "' AND fld_private = 0 "
ELSE
//mind the breaks consider two lines below as a single line
rsAlbum.Source = "SELECT * FROM tbl_album WHERE fld_owner = '" + Replace(rsAlbum__MMColParam, "'", "''") + "' AND fld_private = 1 "
End IF |
Now comming to the 2nd Scenario:
You have Search - Result pages in which you have given the Users the option of "OR" and "AND" (sorry for my terrible english, to have an idea of what i am talking about see this search page).
First of all insert the Radio Group "AND" and "OR" and name it "SearchType" (Go to Insert > From Option in Dreamweaver and hit the Option Radio Group). Now enter for "AND" Radio Button AND as checked value and for "OR" Radio button OR as Checked Value in the property Inspector.
It is up to you what you want to do with your Search Page, i will cover the Result page code modification which is not different than what we have done so far.
Again consider the same code snippet:
//mind the breaks consider two lines below as a single line
rsAlbum.Source = "SELECT * FROM tbl_album WHERE fld_owner = '" + Replace(rsAlbum__MMColParam, "'", "''") + "' AND fld_private = 0" |
It should now look like this ( Pay attention to the part of the code marked as RED):
IF Request("SearchType") = "AND" THEN
//mind the breaks consider two lines below as a single line
rsAlbum.Source = "SELECT * FROM tbl_album WHERE fld_owner = '" + Replace(rsAlbum__MMColParam, "'", "''") + "' AND fld_private = 0 "
ELSE
//mind the breaks consider two lines below as a single line
rsAlbum.Source = "SELECT * FROM tbl_album WHERE fld_owner = '" + Replace(rsAlbum__MMColParam, "'", "''") + "' OR fld_private = 1 "
End IF |
Thas it, we are done.
The results shown will be based on the Request( "Search Type") which has either the value "AND" or "OR".
This little modification will help us save lot of time and Headache
I hope you find this useful. Any questions? Post them in the Mxmania Forums (Tutorial Section)
|