How do the big "search sites" do it?

mixed search terms yield results

[dickbaker]

I'm putting together a site where visitors can look for a product based on make and model. So far, so good. But I notice that the big auction and classifieds sites can do what I cannot.

At those sites, visitors can search for Acme small red widgets, or Acme red widgets small, or Acme widgets small red, and come up with results.

How are they able to structure their queries so that the search terms can be shuffled around like that? Or are they not using SQL?

[txbakers]

str = "Acme red widgets small"

newStr = str.split(" ")
"select * from table where (field like'" & newStr[0] & "' or field like'" & newStr[1] & "'") etc.

Basically, you read the Request, split it at the space, count the number of entries, add that many "likes" to your query.

[dickbaker]

Thanks very much for the reply. It makes sense to me, until I have to figure out where in the "MM" code the new string should be.

Here's the ASP code that's on the page right now:

<%@LANGUAGE="VBSCRIPT"%>
<!--#include file="Connections/shopfinder.asp" -->
<%
Dim Recordset1__MMColParam
Recordset1__MMColParam = "1"
If (Request.QueryString("ID") <> "") Then
Recordset1__MMColParam = Request.QueryString("ID")
End If
%>
<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_shopfinder_STRING
Recordset1.Source = "SELECT dbo.shopowners.shopname, dbo.shopowners.streetaddress, dbo.shopowners.city, dbo.gunshopowners.state, dbo.shopowners.phone, dbo.classifieds.ID, dbo.classifieds.username, dbo.classifieds.pass, dbo.classifieds.make, dbo.classifieds.model, dbo.classifieds.price, dbo.classifieds.description, dbo.classifieds.description2, dbo.classifieds.description3, dbo.classifieds.phonenumb, dbo.classifieds.emailaddy, dbo.classifieds.photo1, dbo.classifieds.photo2, dbo.classifieds.photo3 FROM dbo.classifieds INNER JOIN dbo.shopowners ON dbo.shopowners.username = dbo.classifieds.username WHERE dbo.classifieds.ID='" + Replace(Recordset1__MMColParam, "'", "''") + "'"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0
%>

I can't figure out if the New.String=str.split('' ") should be following the Request.QueryString("ID") line, or after the Recordset1.ActiveConnection = MM_shopfinder_STRING line.

Thanks for your help so far. Any other advice you can give is warmly welcomed.

Dick

[dickbaker]

Sorry, that was the code for a different page. Here's the code for the page I'm struggling with:

<%@LANGUAGE="VBSCRIPT"%>
<!--#include file="Connections/shopfinder.asp" -->
<%
Dim Recordset1__MMColParam
Recordset1__MMColParam = "1"
If (Request.Form("make") <> "") Then
Recordset1__MMColParam = Request.Form("make")
End If
%>
<%
Dim Recordset1__MMColParamtwo
Recordset1__MMColParamtwo = "1"
If (Request.Form("model") <> "") Then
Recordset1__MMColParamtwo = Request.Form("model")
End If
%>
<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_gunshopfinder_STRING
Recordset1.Source = "SELECT ID, make, model, price FROM dbo.classifieds WHERE make LIKE '%" + Replace(Recordset1__MMColParam, "'", "''") + "%' AND model LIKE '%" + Replace(Recordset1__MMColParamtwo, "'", "''") + "%' OR make LIKE '%" + Replace(Recordset1__MMColParam, "'", "''") + "%' AND description LIKE '%" + Replace(Recordset1__MMColParamtwo, "'", "''") + "%'"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0
%>

I've been trying what TXBAKERS suggested, but coming up with errors all over the place. This is really driving me crazy. It seems like it should be simple to have a search return results for any words the user submitted.

Any other suggestions?

Thanks in advance for any replies.

[digitalv]

Just one suggestion to add to the answers everyone else gave you ... if you're going to do this, make sure you have more RAM in your SQL server than the size of your database(s). SQL 2000 running on Windows Server 2003 or Windows 2000 Data Center will handle up to 16 GB of RAM, other versions are 4 GB and 8 GB ... I can't exactly remember which version supports how much memory, but its somewhere on Microsoft's site.

Having more RAM than your databases will allow the DB's to load entirely into the RAM instead of on the hard disk. This is important if you're doing a lot of text queries, especially using LIKE '%whatever%' because those take the most resources. If the entire DB is in RAM, the whole thing will run super fast.

[Mike_Levin]

Also for performance, of course make sure you have the right indexes built. You can always use the Index Optimization Wizard built into Query Analyzer. Sometimes you get huge performance increases.

[dickbaker]

Thanks for the tips, digitavl and Mike. Right now the search is only conducted on two fields in the table: the make and model. My problem--aside from not knowing entirely what I'm doing--is that I won't have any control over how people will enter the model name in their classified ad.

They'll enter "Acme" for the brand; that's not a problem The problem, as I said, is that they may enter "red widgets" or "big red widgets" or "red big widgets" in the model field. And, as I have the code written now, it will only read a certain combination of words in a particular order.

I've spent just about the entire weekend trying to find something that will work. My head is sore from banging it against the wall!

[dickbaker]

OK, I found a script that works, although it was set up for Access, not MS SQL. I made some minor changes, but it still doesn't work.

Would somebody please take a look at this and tell me where I'm going wrong?

Here's the script:

<%
Dim Recordset1__MMColParam
Recordset1__MMColParam = "1"
If (Request.Form("make") <> "") Then
Recordset1__MMColParam = Request.Form("make")
End If
%>

<%
Keywords = Request.Form("model") ' The words entered in the text field stored as a Text String in a Variable called Keywords
If Keywords <> "" Then ' If there is something in the Keywords
SearchFields = "model" ' The Database table column in which we are going to look for matches to our Keywords stored as a Text String in a Variable called SearchFields
Keywords = Replace(Keywords, ","," ") ' Replace any commas, colons, semicolons, dashes, undersores,
Keywords = Replace(Keywords, ":"," ") ' forward slashes or back slashes in the text entered in the
Keywords = Replace(Keywords, ";"," ") ' Keywords text field with a space.
Keywords = Replace(Keywords, "-"," ")
Keywords = Replace(Keywords, "_"," ")
Keywords = Replace(Keywords, "/"," ")
Keywords = Replace(Keywords, "\"," ")
WhereKeywordsString = " WHERE " & SearchFields & " LIKE '%" ' The SQL SELECT statement WHERE clause stored as a Text String in a Variable called WhereKeywordsString

SearchArray = Split(Keywords," ") ' Split the Keywords that are now separated by spaces and store them in an array
For i = 0 to Ubound(SearchArray) ' Repeat the following for each word in the array
If i > 0 Then
WhereKeywordsString = WhereKeywordsString & " " & SearchFields & " LIKE '%" & SearchArray(i) & "%'" ' Builds the SQL statement substituting AND / OR as defined by MatchType

End If
Next
Else
WhereKeywordsString = " WHERE model LIKE '%" & Keywords & "%'" 'If the SearchType selected was EXACT
End If ' End If searchtype is not EXACT
%>
<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_shopfinder_STRING
Recordset1.Source = "SELECT ID, make, model, price FROM dbo.classifieds WHERE make LIKE '%" + Replace(Recordset1__MMColParam, "'", "''") + "%' & WhereKeywordsString"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0
%>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<p><%=(Recordset1.Fields.Item("make").Value)%></p>
<p><%=(Recordset1.Fields.Item("model").Value)%></p>

[TheNige]

These examples are one way to do it. I'm not sure how many records you will be searching though, but once you start getting millions of rows you'll want to use the Full-Text indexing of SQL Server.

Using Like '%string%' on that many rows will bring the sever to its knees because it can't use an index for the query. If you stick to Like '%string' then the query can still use an index though.

[dickbaker]

Arrrrrrggghhhhh! I just found another script, added the field names, and tried it. Got the same error message that I got for the previous script: "Invalid operator for data type. Operator equals boolean AND, type equals varchar"

If anybody has an idea, PLEASE let me know what you think the problem is!

Here's the new script:

<%@LANGUAGE="VBSCRIPT"%>
<!--#include file="Connections/shopfinder.asp" -->
<%
Dim Recordset1__MMColParam
Recordset1__MMColParam = "1"
If (Request.Form("make") <> "") Then
Recordset1__MMColParam = Request.Form("make")
End If
%>
<%
Function buildSQL( strText )
Dim selectClause
Dim fromClause
Dim whereClause
selectClause = "SELECT model"
fromClause = "FROM dbo.classifieds "
whereClause = "WHERE "
Select Case Request.Form("model")
Case 1 ' Match ALL keywords
whereClause = whereClause & " (model LIKE '%" & _
Replace( Trim( strText ), " ", "%' AND model LIKE '%") & "%')"
Case 2 ' Match ANY keywords
whereClause = whereClause & " (model LIKE '%" & _
Replace( Trim( strText ), " ", "%' OR model LIKE '%") & "%')"
End Select
'Response.Write( selectClause & fromClause & whereClause )
buildSQL = selectClause & fromClause & whereClause
End Function
%>

<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_shopfinder_STRING
Recordset1.Source = "SELECT ID, make, model, price FROM dbo.classifieds WHERE make LIKE '%" + Replace(Recordset1__MMColParam, "'", "''") + "%' & 'buildSQL'"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0
%>