Search page using 3 parameters

Question

I need to search training programs using 3 optional parameters (title, country, category).
I am confused, how I can display all data when visitor doesn't filter the data and filter it when they send it as query string ?
(ex. resutls.aspx?title=title&country=usa&category=leaders)

how to implement this using sql server and asp.net (c#) ?

<asp:Repeater runat="server" DataSourceID="srcTraining">
              <ItemTemplate>
                  <tr>
                  <td><%#Eval("title") %></td>
                  <td><span class="fa fa-map-marker"></span> <%#Eval("country") %></td>
                  <td><i class="fa fa-calendar"></i><%#Eval("days") %> days</td>
                  </tr>
              </ItemTemplate>
</asp:Repeater>

Look in sql first check that if the values are not null then Put the LIKE query for all otherwise dnt. — Kaushik, Jun 16 '14 at 13:10
@KaushikKishore it's 3 parameters, so I should write all possible case? like if location is null, exclude it and search using others.. and so on — Dot Freelancer, Jun 16 '14 at 13:12
In my sql there is option for `OR` operation see this link http://stackoverflow.com/questions/9112029/mysql-select-if-statement-with-or/9112102#9112102 — Kaushik, Jun 16 '14 at 13:15
@KaushikKishore in this case when visitor doesn't use filtering, the data will not display anything because the search query doesn't match any OR — Dot Freelancer, Jun 16 '14 at 13:22
You're not understanding what i'm saying. Look like this. Your SQL query + if(Here Like Query.). It will be appended only there would be value. — Kaushik, Jun 16 '14 at 13:54
@KaushikKishore thanks for you, please take a look to my answer, I solved the problem.. — Dot Freelancer, Jun 16 '14 at 14:00

score 0 · Answer 1 · answered Jun 16 '14 at 13:51

Ok, this take long time from me but finally I find a way to do this

SELECT DISTINCT available_training.training_id, training.title
FROM            available_training INNER JOIN training 
ON 
    available_training.training_id = training.id
where
        (@location is null or available_training.country = @location) 
    and 
        (@category   IS NULL or category = @category)  
    and
        (@title is null or training.title like '%'+@title+'%');

hopefully, it will help someone else

Search page using 3 parameters

1 Answers1