locked
Help with Search Script RRS feed

  • Question

  • User-371148474 posted

    Just like twitter Search result, how to I write my SQL query and result display to display and differentiate between a Search Query as a user and a Search Query as an article title.

    Tuesday, December 2, 2014 6:53 AM

Answers

  • User281315223 posted

    You have quite a few options. 

    You could consider having a drop-down list of sorts that would allow the user to determine what area they want to search (e.g. users, articles, places, etc.). This way whenever you searched, you could just checked the contents of your drop-down to determine which area should be searched. 

    Another option might be to include some type of identifier within your search string such as ":article" and when you see this in the string, you would know that this is a specific search for an article (so only query that table). 

    One final option might be to explicitly search all of the tables and return the results in a categorized manner (e.g. article : "Article Name", article : "Article Name 2", person : "Person 1", etc.).

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 2, 2014 8:31 AM
  • User281315223 posted

    You may want to try and revisit Option A as it's going to be the easiest from an implementation perspective. You could likely make (or find) a very clean way of toggling these options if you don't want an ugly drop-down list. This will also limit the user to searching a single area, but I suppose it's up to you if that is an option.

    Approaches like Option C generally do require a bit of tinkering. You'll need to find a way to make all of your schema (or at least the properties that you want from each) to fit into a single model. It wouldn't be out of the realm of possibility to actually include every property on your model along with a few boolean ones (such as IsArticle or IsUser) to help differentiate the properties that you might need.

    So you might have a model that would have everything (e.g. ID, Link, CatID, Title, Subtitle, Content, Email, username, ProfilePic, Name, Description, etc.) and when you query your Users table, just populate the properties that you have (and set the IsUser property to true) and then do the same within your Articles table and any other tables you need to handle. Using this approach, you could output the matching values as you might expect :

    <b>Matching Users</b>
    
    @if(Results.Any(r => r.IsUser)){
        foreach(var user in Results.Where(u => u.IsUser))
        {
            <!-- Output your properties that are only on your User items here (e.g. user.Email, user.Username, user.ProfilePic) -->
        }
    }
    else{
        <i>No Users Available</i>
    }
    
    <b>Matching Articles<b>
    
    @if(Results.Any(r => r.IsArticle)){
        foreach(var article in Results.Where(a => a.IsArticle))
        {
            <!-- Output your properties that are only on your User items here (e.g. article.Title, article.Subtitle, article.Content) -->
        }
    }
    else{
        <i>No Articles Available</i>
    }

    Obviously your syntax may vary, but hopefully you get the gist of it.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 2, 2014 11:10 AM

All replies

  • User281315223 posted

    You have quite a few options. 

    You could consider having a drop-down list of sorts that would allow the user to determine what area they want to search (e.g. users, articles, places, etc.). This way whenever you searched, you could just checked the contents of your drop-down to determine which area should be searched. 

    Another option might be to include some type of identifier within your search string such as ":article" and when you see this in the string, you would know that this is a specific search for an article (so only query that table). 

    One final option might be to explicitly search all of the tables and return the results in a categorized manner (e.g. article : "Article Name", article : "Article Name 2", person : "Person 1", etc.).

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 2, 2014 8:31 AM
  • User-371148474 posted

    Please Rion,

    Lets look at Options 2 and 3.. can you include some example, so i can read, understand then implement.

    Please, Regards.

    Tuesday, December 2, 2014 8:47 AM
  • User281315223 posted

    Option 2 would simply be the inclusion of a single string within your search box along with your search term. For instance, let's say you searched for "ASP.NET :article" which might specify that you want to find an article that contains ASP.NET. Within your code-behind, you would have some logic that would check for any of these identifiers (like article, user, etc.) and then apply the logic for where you would want to search :

    // Check if your search string contained an identifier
    if(SearchTextBox.Text.Contains(":article"))
    { 
         // Get your search term (basically strip off the identifier)
         var search = SearchTextBox.Text.Replace(":article","");
     
         // Open a connection to the Article database here
    }
    else if(SearchTextBox.Text.Contains(":user"))
    {
         // Get your search term (basically strip off the identifier)
         var search = SearchTextBox.Text.Replace(":article","");
     
         // Open a connection to the User database here
    }

    Option 3 would actually search all of your specific databases and return any results for each. You could do something like the following and have a single collection of Result objects to store all of your results. You would just want to create a class that could store both where the search came from (Area) and all of your other properties that you would want to retrieve from your database :

    public class SearchResult
    {
         public string Area { get; set; }
         // Other properties here
    }

    Then when you performed a search, you would build a collection of these objects and search each of your databases :

    // Build your Search Results
    var results = new List<SearchResults>();
    
    // Search your User database
    using(var userConnection = new SqlConnection("User Database Connection String"))
    {
         // Build your query here
         
         // Store the results by indicating they originated in the User area
         foreach(var x in y)
         {
               var result = new SearchResult(){ Area = "User" };
    
               // Populate your other properties here
    
               // Add this item to the collection
               results.Add(result);
         }
    }
    // Repeat for your other databases

    Does that make sense?

    Tuesday, December 2, 2014 9:48 AM
  • User-371148474 posted

    Rion,

    Thanks for the insight, but awesome users wont add that single string into their search request, they expect to just type something they want and get respective results each..

    So Option 2 looks like Plan B..

    Option C logic seems different, after considerable time on it, i can't seem to work around it, here, find my database schematic. and see if you can help make it simpler.

    ID               Email                                        Username                               ProfilePic     Name                                               Description

    3 solomon_omokehinde@yahoo.com hardweezy 11/3/2014 1:40:42 AM True profile-2.jpg Solomon Omokehinde hacking-cover.jpg I don't care about attribution, mate. — Use everything you find here under Creative Commons CC0. Find me on Twitter at @hardweezy 2 0
    4 khubone.mqapheli5@gmail.com lafo 11/5/2014 2:32:21 PM True head.jpg Wiseman Lapho nobg.jpg I like to make words on the internet. Editor @ The Next Web. 1 0
    6 214577345@yahoo.com 214577345 11/5/2014 6:00:07 PM True head.jpg Seun First-Hand nobg.jpg Growth | @EmissaryMed. #SFGiants fan, golfer, poker player, musician, entrepreneur. Previously @Tradecraft. 0 2
    8 214577345@stu.ukzn.ac.za craig 11/16/2014 2:39:51 PM True profile-3.jpg Craig Blewett nobg.jpg Social Networking, researcher, blogger #socialmedia mindset #language #media marketing communication 1 2
                         

    ID     LINK           catID     Title             Subtitle                          Content

    8 223691142 5 3 <h2>A Great Story Never Told</h2> <p style="box-sizing: border-box; margin: 0px 0px 30px; font-family: 'Droid Serif', serif; font-size: 2.2rem; line-height: 3.2rem; color: #333337;">Lorem ipsum dolor sit amet, consectetur adipiscing elit. Donec viverra ligula eget feugiat sollicitudin. Praesent accumsan, ante ut porttitor aliquet, odio felis mollis ante, et pretium arcu ipsum eget turpis. Quisque congue odio tristique, tincidunt arcu a, dignissim neque. Cras ligula erat, porta sed tristique at, sollicitudin sit amet ligula. Quisque eleifend elementum mauris, eu condimentum tortor ultrices a. Vestibulum ante ipsum primis in faucibus orci luctus et ultrices posuere cubilia Curae; Integer hendrerit, arcu a aliquam malesuada, sapien quam tempor neque, nec aliquam odio mi vel diam. Pellentesque egestas pharetra justo, id malesuada erat tincidunt ac. Ut mauris mauris, ultricies ut pharetra sit amet, suscipit sed nibh. Suspendisse vestibulum sem sed rhoncus pharetra. Nunc commodo, neque eget gravida mattis, augue est sollicitudin nunc, elementum aliquet tortor mi ac velit. Ut nec pharetra est. Vestibulum at mauris sapien. Duis sed turpis at lectus egestas rhoncus.</p> False True 12/1/2014 2:29:16 AM False
    9 275467079 10 6 Story Never Told <p style="box-sizing: border-box; margin: 0px 0px 30px; font-family: 'Droid Serif', serif; font-size: 2.2rem; line-height: 3.2rem; color: #333337;">Etiam sed condimentum elit. Curabitur eu malesuada turpis. Nam sit amet dui tellus. Nullam eget neque sed lectus tincidunt fermentum. Aenean ac interdum odio, vitae consectetur erat. Nunc venenatis turpis sed consectetur lacinia. Phasellus euismod odio feugiat, semper ligula in, egestas magna. Duis ullamcorper gravida lacus a blandit. Fusce et diam pellentesque, egestas diam pellentesque, gravida libero. Ut condimentum, nulla ac faucibus porttitor, est dui gravida est, vel varius mi justo at erat. Class aptent taciti sociosqu ad litora torquent per conubia nostra, per inceptos himenaeos. Nulla facilisis diam eu libero suscipit lacinia. Sed sollicitudin justo ut fermentum venenatis. Morbi justo magna, pellentesque rutrum scelerisque eget, vehicula ac eros. Nam quis velit ac lorem blandit egestas eget id ante. Integer nibh arcu, vulputate eget luctus vel, porta eu ligula.</p> True False 11/18/2014 1:15:07 AM False
    10 229872150 14 8 The job of leadership The job of leadership is to foster alignment and enthusiasm toward the right goal. <section style="; clear: both; color: rgba(0, 0, 0, 0.8); font-family: freight-text-pro, Georgia, Cambria, 'Times New Roman', Times, serif; font-size: 22px; letter-spacing: 0.159999996423721px; line-height: 33px;">
    <div class="section-content">
    <div class="section-inner layoutSingleColumn" style="width: 700px; margin: 0px auto; ; right: 0px; transition: right 200ms; -webkit-transition: right 200ms;">
    <p class="graf--p graf--first" style="margin: 0px 0px 30px;">This has three parts:</p>
    </section>
    False False 11/18/2014 1:18:19 AM True
    Tuesday, December 2, 2014 10:52 AM
  • User281315223 posted

    You may want to try and revisit Option A as it's going to be the easiest from an implementation perspective. You could likely make (or find) a very clean way of toggling these options if you don't want an ugly drop-down list. This will also limit the user to searching a single area, but I suppose it's up to you if that is an option.

    Approaches like Option C generally do require a bit of tinkering. You'll need to find a way to make all of your schema (or at least the properties that you want from each) to fit into a single model. It wouldn't be out of the realm of possibility to actually include every property on your model along with a few boolean ones (such as IsArticle or IsUser) to help differentiate the properties that you might need.

    So you might have a model that would have everything (e.g. ID, Link, CatID, Title, Subtitle, Content, Email, username, ProfilePic, Name, Description, etc.) and when you query your Users table, just populate the properties that you have (and set the IsUser property to true) and then do the same within your Articles table and any other tables you need to handle. Using this approach, you could output the matching values as you might expect :

    <b>Matching Users</b>
    
    @if(Results.Any(r => r.IsUser)){
        foreach(var user in Results.Where(u => u.IsUser))
        {
            <!-- Output your properties that are only on your User items here (e.g. user.Email, user.Username, user.ProfilePic) -->
        }
    }
    else{
        <i>No Users Available</i>
    }
    
    <b>Matching Articles<b>
    
    @if(Results.Any(r => r.IsArticle)){
        foreach(var article in Results.Where(a => a.IsArticle))
        {
            <!-- Output your properties that are only on your User items here (e.g. article.Title, article.Subtitle, article.Content) -->
        }
    }
    else{
        <i>No Articles Available</i>
    }

    Obviously your syntax may vary, but hopefully you get the gist of it.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 2, 2014 11:10 AM