i am starting to build a web page to refine some search results. The code below works pretty well, and if i add ONE of the query strings (ie, ?beds=4, it returns the correct results. If, however, i specify both query strings (ie, ?beds=4&sleeps=8, it returns results matching either (all propertys with 4 beds (regardless of sleeps) and all propertys with 8 sleeps (regardless of beds), and not BOTH. i need some sort of AND statement, so that the results match beds AND sleeps?
@{
Layout = "~/_SiteLayout.cshtml";
Page.Title = "Search";
string searchText = Request.Unvalidated["searchText"];
var searchTerms = searchText.Split('"').Select((element, index) => index % 2 == 0 ? element.Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries) : new string[] { element }).SelectMany(element => element).ToList();
for (int i=0; i<searchTerms.Count; i++)
{
if (searchTerms[i].ToUpper() == "THE" || searchTerms[i].ToUpper() == "AND" || searchTerms[i].ToUpper() == "AS" || searchTerms[i].ToUpper() == "AN" || searchTerms[i].ToUpper() == "BUT" || searchTerms[i].ToUpper() == "OR" || searchTerms[i].ToUpper() == "OF" || searchTerms[i].ToUpper() == "IF" || searchTerms[i].ToUpper() == "IS" || searchTerms[i].ToUpper() == "IN" || searchTerms[i].ToUpper() == "IT" || searchTerms[i].ToUpper() == "BY" || searchTerms[i].ToUpper() == "TO" || searchTerms[i].ToUpper() == "FOR" || searchTerms[i].Length <= 1 || String.IsNullOrWhiteSpace(searchTerms[i]))
{
searchTerms.RemoveAt(i);
i--; //decrements 'i' if an element is removed because all indexes after this one will drop by one. This ensures that no indexes get skipped.
}
}
var db = Database.Open("StayInFlorida");
string searchQueryString = "";
int termCount = searchTerms.Count;
string[] searchTermsArray = searchTerms.ToArray();
searchQueryString = "SELECT * FROM PropertyInfo WHERE numBedrooms = ";
for (int i=0; i<termCount; i++)
{
if (i != 0)
{
searchQueryString += "OR numBedrooms = "; //Ensures that this is not appended for the first term. Alternatively, of course, you can use "AND", depending on how you want the results returned, but you probably want "OR".
}
searchQueryString += "@" + i + " ";
}
searchQueryString += "UNION ";
searchQueryString += "SELECT * FROM PropertyInfo WHERE numSleeps = ";
for (int i=0; i<termCount; i++)
{
if (i != 0)
{
searchQueryString += "OR numSleeps = ";
}
searchQueryString += "@" + i + " ";
}
searchQueryString += "UNION ";
searchQueryString += "SELECT * FROM PropertyInfo WHERE numBathrooms = ";
for (int i=0; i<termCount; i++)
{
if (i != 0)
{
searchQueryString += "OR numBathrooms = ";
}
searchQueryString += "@" + i + " ";
}
searchQueryString += "ORDER BY anyTermYouWishToOrderBy DESC";
if (searchTermsArray.Length > 0) //Prevents a server-side error if the searchTerm list was empty when converted to the searchTermsArray
{
var queryResults = db.Query(searchQueryString, searchTermsArray);
}
}
Caveat:
I hope this answer helps you approach, logically, a solution to extend the functionality to include a number of variables that you do not know until runtime, but must use to query a database. I understand that you are not searching on strings but instead numbers, or possibly other kinds of data, probably gathered from a form on a previous page, but once you get all of the data you need to the target page (using query strings may not be suitable), the logistics should be same, so we'll start there. If, in the end, you find that you are having trouble getting the unknown number of variables from the form to the target page, we can address that, also, but for now I will assume that you have gathered the necessary data on the page that queries the database.
For this example I will assume that the user types a string of text that I must split on spaces, extract the words from, and store each in a list.
Firstly, (for this example) retrieve the value in a single query string from a <form> using GET on a previous page. This, you already know how to do.
string searchText = Request.Unvalidated["searchText"];
Note that I have to retrieve this value unvalidated, because if the user were to type angle brackets in their search term, it would throw a server-side error. This is okay, though, because we are going to be careful with the untrusted data.
Next you can simply check their whole search text to make sure it isn't empty or white space like so:
if (!String.IsNullOrWhiteSpace(searchText))
Within that branch you can begin to execute all of the functionality you need to make this work.
You can replace any simple characters you want to ignore (we'll use the apostrophe and the comma).
searchText = searchText.Replace("'", "").Replace(",", "");
Now, this next line is a little complex and I'm not even sure I will explain it correctly (I'm still really new to LINQ), so, suffice it to say that it effectively splits the string on spaces (taking care of things like double-spaces and treating text wrapped in quotes as a single item) and storing them in a list (This part may be outside the scope of your question and if so, I apologize, but just in case you need to use something like this...).
var searchTerms = searchText.Split('"').Select((element, index) => index % 2 == 0 ? element.Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries) : new string[] { element }).SelectMany(element => element).ToList();
Now, if you so choose, you can omit terms from this list that you want to ignore (I will use some simple common words in this example, as well as, any single character items, and of course, empty, null, or white space items):
for (int i=0; i<searchTerms.Count; i++)
{
if (searchTerms[i].ToUpper() == "THE" || searchTerms[i].ToUpper() == "AND" || searchTerms[i].ToUpper() == "AS" || searchTerms[i].ToUpper() == "AN" || searchTerms[i].ToUpper() == "BUT" || searchTerms[i].ToUpper() == "OR" || searchTerms[i].ToUpper() == "OF" || searchTerms[i].ToUpper() == "IF" || searchTerms[i].ToUpper() == "IS" || searchTerms[i].ToUpper() == "IN" || searchTerms[i].ToUpper() == "IT" || searchTerms[i].ToUpper() == "BY" || searchTerms[i].ToUpper() == "TO" || searchTerms[i].ToUpper() == "FOR" || searchTerms[i].Length <= 1 || String.IsNullOrWhiteSpace(searchTerms[i]))
{
searchTerms.RemoveAt(i);
i--; //decrements 'i' if an element is removed because all indexes after this one will drop by one. This ensures that no indexes get skipped.
}
}
Next, declare some basic variables that you will need:
var db = Database.Open("StayInFlorida");
string searchQueryString = "";
int termCount = searchTerms.Count;
string[] searchTermsArray = searchTerms.ToArray();
Now here is where some of the logic comes into play that will help you handle compiling the sql query string when you don't know how many variables to test your columns against (for this example I will assume three database columns exist: numBedrooms, numSleeps, and numBathrooms).
searchQueryString = "SELECT * FROM PropertyInfo WHERE numBedrooms = ";
for (int i=0; i<termCount; i++)
{
if (i != 0)
{
searchQueryString += "OR numBedrooms = "; //Ensures that this is not appended for the first term. Alternatively, of course, you can use "AND", depending on how you want the results returned, but you probably want "OR".
}
searchQueryString += "@" + i + " ";
}
searchQueryString += "UNION ";
searchQueryString += "SELECT * FROM PropertyInfo WHERE numSleeps = ";
for (int i=0; i<termCount; i++)
{
if (i != 0)
{
searchQueryString += "OR numSleeps = ";
}
searchQueryString += "@" + i + " ";
}
searchQueryString += "UNION ";
searchQueryString += "SELECT * FROM PropertyInfo WHERE numBathrooms = ";
for (int i=0; i<termCount; i++)
{
if (i != 0)
{
searchQueryString += "OR numBathrooms = ";
}
searchQueryString += "@" + i + " ";
}
searchQueryString += "ORDER BY anyTermYouWishToOrderBy DESC"; //You can order by whatever term you need, and, as always, can use "ASC" instead of "DESC"
Now that that part is over with all you have left is to make sure that you pass the appropriate amount of arguments to the db.Query() method, and since we already have an array copy of our list, we can just use it.
if (searchTermsArray.Length > 0) //Prevents a server-side error if the searchTerm list was empty when converted to the searchTermsArray
{
var queryResults = db.Query(searchQueryString, searchTermsArray);
Thankfully the db.Query() method makes that simple by accepting an array of values as the second argument, which simply fills in the parameters in the query, as if you were adding multiple arguments after the first.
Lastly, to simply display the results (you probably already know this part, but for the sake of completion, I will show an example anyway)
foreach (var row in queryResults)
{
<div>Number of Bedrooms: @row.numBedrooms</div><br/>
<div>Number of Sleeps: @row.numSleeps</div><br/>
<div>Number of Bathrooms: @row.numBathrooms</div><br/><hr/><br/>
}
} // <-- Don't forget to close the `if (searchTermsArray.Length > 0)` branch.
} // <-- Don't forget to close the `if (!String.IsNullOrWhiteSpace(searchText))` branch.
I know that 100% of the logic here may not exactly fit your scenario, but hopefully gives you enough that you can modify it to your needs. Also, if you have any questions, I'll be happy to help further if I can.