Order by number of matches : The Official Microsoft ASP.NET Forums

Reply

Mojorz

Member

3 Points

22 Posts

Order by number of matches

Dec 27, 2012 03:54 PM|LINK

Hi ,

In my project i have two tables one has the field tag and saves document titles ex: debit systems

the other saves the keywords such as "debit" "systems" .

The db structure is the following:

http://imageshack.us/photo/my-images/545/87622413.jpg/

My aim is to get this result: if user introduces a search term like debit systems i want to count that it has two keywords .

my code so far :

string words = txt_search.Text;

string[] source = words.Split(new char[] { '.', '?', '!', ' ', ';', ':', ',' }, StringSplitOptions.RemoveEmptyEntries);

foreach (string str in source)

{SqlConnection connection = Connection.GetDBConnection();

SqlCommand cmd = new SqlCommand("select * from form_keyword,form where form.id_form=form_keyword.id_form and form_keyword.id_k like'%'+@tag+'%' ", connection);
SqlDataAdapter da = new SqlDataAdapter(cmd);

cmd.Parameters.AddWithValue("@tag", str);

}

Any help will be appreciated

thanks

Edited by Mojorz on Dec 27, 2012 03:55 PM
Edited by Mojorz on Dec 31, 2012 10:27 AM

Reply

sameer_khanj...

Contributor

7060 Points

1378 Posts

Re: Order by number of matches

Dec 28, 2012 05:29 AM|LINK

string words = txt_search.Text;

            string[] source = words.Split(new char[] { '.', '?', '!', ' ', ';', ':', ',' }, StringSplitOptions.RemoveEmptyEntries);
            SqlConnection connection = Connection.GetDBConnection();
            SqlCommand cmd = new SqlCommand();
            int i = 0;
            string Command = "select * from form_keyword,form where form.id_form=form_keyword.id_form ";
            foreach (string str in source)
            {
                i = i + 1;
                if (i == 1)
                    Command = Command + "and (form_keyword.id_k like'%'+@tag_" + i.ToString() + "+'%' ";
                else
                    Command = Command + "or form_keyword.id_k like'%'+@tag_" + i.ToString() + "+'%' ";
                cmd.Parameters.AddWithValue("@tag_" + i.ToString(), str);
            }
            Command = Command + ");";
            cmd.CommandText = Command;
            cmd.Connection = connection;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);

sameer.khanjit@gmail.com
View Blog
Click "Mark as Answer" on the post that helped you.

Edited by sameer_khanjit on Dec 28, 2012 05:29 AM
Edited by sameer_khanjit on Dec 31, 2012 10:27 AM

Reply

Mojorz

Member

3 Points

22 Posts

Re: Order by number of matches

Dec 28, 2012 01:41 PM|LINK

Hi ,

Thanks for the reply it is working but the result, when the user searches a keyword should display first the expression with more number of matches like above:

record number of matches

17 3

12 2

11 2

8 1

Any ideas?

Edited by Mojorz on Dec 28, 2012 01:43 PM
Edited by Mojorz on Dec 28, 2012 01:44 PM
Edited by Mojorz on Dec 31, 2012 10:27 AM

Reply

sameer_khanj...

Contributor

7060 Points

1378 Posts

Re: Order by number of matches

Dec 31, 2012 10:44 AM|LINK

you can add Order by in command

ORDER BY
    CASE WHEN field1 LIKE '%schoolA%' THEN 1 ELSE 0 END AS field1IND +
    CASE WHEN field2 LIKE '%schoolB%' THEN 1 ELSE 0 END AS field2IND +

string words = txt_search.Text;

            string[] source = words.Split(new char[] { '.', '?', '!', ' ', ';', ':', ',' }, StringSplitOptions.RemoveEmptyEntries);
            SqlConnection connection = Connection.GetDBConnection();
            SqlCommand cmd = new SqlCommand();
            int i = 0;
            string Command = "select * from form_keyword,form where form.id_form=form_keyword.id_form ";
			string OrderbyCommand ="ORDER BY ";
            foreach (string str in source)
            {
                i = i + 1;
                if (i == 1)
                    Command = Command + "and (form_keyword.id_k like'%'+@tag_" + i.ToString() + "+'%' ";
                else
                    Command = Command + "or form_keyword.id_k like'%'+@tag_" + i.ToString() + "+'%' ";
                cmd.Parameters.AddWithValue("@tag_" + i.ToString(), str);
				OrderbyCommand =OrderbyCommand + " CASE WHEN form_keyword.id_k LIKE '%"+ str +"%' THEN 1 ELSE 0 END AS field" + i.ToString() + "IND + "
            }
            Command = Command + ");";
            cmd.CommandText = Command + OrderbyCommand ;
            cmd.Connection = connection;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);

sameer.khanjit@gmail.com
View Blog
Click "Mark as Answer" on the post that helped you.

Edited by sameer_khanjit on Dec 31, 2012 10:45 AM
Marked as answer by Mojorz on Dec 31, 2012 01:50 PM

Reply

gerrylowry

All-Star

20513 Points

5712 Posts

Re: Order by number of matches

Dec 31, 2012 03:47 PM|LINK

@ Mojorz welcome to forums.asp.net

Tip: in addition to providing your relevant code snippets, you might also wish to show sample data.

Note: my intuition, which may be wrong, suggests to me that the code provided by sameer_khanjit may be insufficiently robust.

Let me explain. First, there is the use of LIKE. (http://msdn.microsoft.com/en-us/library/ms179859.aspx "LIKE (Transact-SQL)", SQL Server 2012).

"LIKE comparisons are affected by collation. For more information, see COLLATE (Transact-SQL)."

Second, your schema (http://imageshack.us/f/545/87622413.jpg/) has the form_keyword table with only two varialbes that appear to be foreign keys, likely integers(?), so there appears to be a missing third table that has the actual keywords.

thus, it appears that the SQL command built by sameer_khanjit's code may be comparing integers with strings.

Third, this code looks very suspicious:

form_keyword.id_k like'%'+@tag_" + i.ToString() + "+'%'

i is an integer, so i.ToString() looks like '1', '2', '3', '4', et cetera.

also, there is 's use of the % wildcard (http://msdn.microsoft.com/en-us/library/ms189454.aspx "Percent character (Wildcard - Character(s) to Match) (Transact-SQL)":

"Matches any string of zero or more characters.
This wildcard character can be used as either a prefix or a suffix."

This suggests that "103" might match "103", "1030", 1035", et cetera ... of course, here is a case where seeing actual sample data might help me visualize where i might be wrong in my assumptions.

below, i've created some sample document titles derived from book titles (i've highlighted candidate keywords):

a: 3D Programming for Windows
b: Accessing and Analyzing Data with Microsoft Excel
c: Code Complete, Second Edition
d: Inside C#, Second Edition
e: Inside Microsoft SQL Server 2008: T-SQL Programming
f: Inside Microsoft Windows SharePoint Services 3.0
g: JavaScript Step by Step
h: Configuring Windows Server 2008 Active Directory
i: Configuring Windows 7
j: .NET XML Web Services
k: Architecting Applications for the Enterprise
l: Writing Secure Code, Second Edition
m: Beginning ASP.NET 4 in C# 2010
n: Introducing Visual C# 2010
o: SQL Server 2008 Administration

Mojorz, i suspect that sameer_khanjit's code, if it were searching for the keywords in an order such as "Programming", "Microsoft", and "Server" would identify only the following:

"Programming": e:

"Microsoft" b:, f:, missing e: (because "Programming" was found first in e:)

"Server" h:, missing e: (because "Programming" was found first in e:)

Mojorz

if user introduces a search term like "debit systems" i want to count that it has two keywords

Mojorz, if i understand your O.P. (original post) correctly, in my example e:, above, you'd want to know that two keywords where found; sorry, but in can not see how sameer_khanjit's code would determine that. (disclaimer: i do not consider myself to be a T-SQL expert).

Fourth, i do not see how sameer_khanjit's code addresses this (from http://forums.asp.net/post/5252640.aspx):

Mojorz

the result, when the user searches a keyword should display first the expression with more number of matches like below:

record number of matches

also, is "record" from table form or table form_keyword:

Mojorz, i've a few questions for you based upon my own curiousity:

(a) have you tested this extensively with lots of test data?

(b) is this by any chance a homework assignment?

Happy 2013!

g.

B-) Please help me by completing my school survey about computer programmers on my website. Thank you!!! Gerry Lowry +1 705-429-7550 wasaga beach, ontario, canada

Edited by gerrylowry on Dec 31, 2012 03:49 PM
Edited by gerrylowry on Dec 31, 2012 03:49 PM
Edited by gerrylowry on Dec 31, 2012 03:51 PM
Edited by gerrylowry on Dec 31, 2012 03:52 PM

Reply

Mojorz

Member

3 Points

22 Posts

Re: Order by number of matches

Jan 03, 2013 07:16 PM|LINK

gerrylowry

also, is "record" from table form or table form_keyword:

Mojorz, i've a few questions for you based upon my own curiousity:

(a) have you tested this extensively with lots of test data?

(b) is this by any chance a homework assignment?

The record is from table form_keywords...

What I´m trying to do now is to get all the keywords from from_keyword then add them to an array and split that array.

The next step is to count the keywords on form_keyword (id_k is a varchar field with keywords such as "debit" ) and compare with the tag(ex: debit systems) from the form table, maybe doing a select count(*) statement...

What do you think about this strategy?

Thanks for your time

Happy 2013...

Reply

gerrylowry

All-Star

20513 Points

5712 Posts

Re: Order by number of matches

Jan 03, 2013 07:27 PM|LINK

@ Mojorz

it would help your peers here at forums.asp.net if you would show us a few rows of your actual sample data from your form_keywords table.

without seeing your sample data, it's hard for me to see what you are trying to achieve.

also, in addition to your raw sample data, it would be useful to see how you expect it to appear after you've split it up.

will id_k contain only a single keyword in each row?

g.

B-) Please help me by completing my school survey about computer programmers on my website. Thank you!!! Gerry Lowry +1 705-429-7550 wasaga beach, ontario, canada

Reply

Mojorz

Member

3 Points

22 Posts

Re: Order by number of matches

Jan 07, 2013 09:15 AM|LINK

Here is the data in form table http://imageshack.us/f/685/formu.png/

and here is data from the table form_keyword http://imageshack.us/f/145/formkeyword.png/.

id_k will contain only a single keyword in each row .

Thanks for your time I appreciate any help .

Wish you the best

Edited by Mojorz on Jan 13, 2013 02:32 AM

Order by number of matches RSS

7 replies

Mojorz

Order by number of matches

sameer_khanj...

Re: Order by number of matches

Mojorz

Re: Order by number of matches

sameer_khanj...

Re: Order by number of matches

gerrylowry

Re: Order by number of matches

Mojorz

Mojorz

Mojorz

Re: Order by number of matches

gerrylowry

gerrylowry

Re: Order by number of matches

Mojorz

Re: Order by number of matches

This site is managed for Microsoft by Neudesic, LLC. | © 2013 Microsoft. All rights reserved.

Follow Us On:

Microsoft