a circular search

1. Senior Member
Join Date
Dec 1969
Posts
190

## a circular search

...of sorts.<BR><BR>I will be able to bring in values to search between. Such as if the user says - check 5 positions each side of 15. The search would be from 10-20. That part is easy enough - except when they reach the top or bottom number. i.e. 1 or 50.<BR><BR>Except it is more like a clock. If a user says - check 5 positions of 50. Than the search would need to be from 45 to 05. And likewise if they ask to search for number 2 than the search would need to be from 47 to 07.<BR><BR>The only way I can think to accomplish this is to search each individual number - but how to write up the SQL dynamically?

2. Senior Member
Join Date
Dec 1969
Posts
342

## RE: a circular search

&nbsp;<BR>For "clock"-like logic you need to use the "mod" function: or % as it is spelt in SQL.<BR><BR>select * from Table where (50 + ID - target)%50 &#060;= range or (50 + ID - target)%50 &#062;= 50-range<BR><BR>So in your last example, "range" is 5 and "target" is 2: so the select brings back IDs where (ID + 48)%50 &#060;= 5 or (ID + 48)%50 &#062;= 45. <BR><BR>For 46, (ID + 48)%50 is 44 which doesn&#039;t match either range.<BR>For 47, (ID + 48)%50 is 45 which matches the second range.<BR>For 48, (ID + 48)%50 is 46 which matches the second range.<BR>For 49, (ID + 48)%50 is 47 which matches the second range.<BR>For 0 (or 50, whichever is in your range), (ID + 48)%50 is 48 which matches the second range.<BR>For 1, (ID + 48)%50 is 49 which matches the second range.<BR>For 2, (ID + 48)%50 is 0 which matches the first range.<BR>For 3, (ID + 48)%50 is 1 which matches the first range.<BR>...<BR>For 7, (ID + 48)%50 is 5 which matches the first range.<BR>For 8, (ID + 48)%50 is 6 which doesn&#039;t match either range.<BR><BR>So it brings back 47,48,49,0,1,2,3,4,5,6,7 as required.

3. Senior Member
Join Date
Dec 1969
Posts
190

## RE: a circular search

that looks cool, i&#039;ll give it a whirl.

4. Senior Member
Join Date
Dec 1969
Posts
96,118

## But what if you don't know...

...how many items there are? Then you&#039;d have to do a COUNT() to get the value to use with the MODULO operator. So in SQL Server you could easily do that in a StoredProc, but in Access you&#039;d probably be better off making a separate query first?<BR><BR>Incidentally, the syntax Ian shows is for SQL Server. If you are using Access, you have to use the MOD operator, same as is used in VBScript.<BR><BR>

5. Senior Member
Join Date
Dec 1969
Posts
190

## Luckily that part no problem

I&#039;m having them select how many (what number) to search on each side of what. Its an online game that I am making a search/history tool for.<BR><BR>Basically if they are on Island say 6 and they want to look 3 islands each side. They&#039;d select 3 islands on each side of island 6<BR><BR>Giving a total of 6 to search for.

6. Senior Member
Join Date
Dec 1969
Posts
190

## then chaos hits

If I could visit this.<BR><BR>I&#039;ve got this as my sql.<BR><BR>mySQL="select networthid,fldname,fldnetworth,flddate,fldIsland from tblnetworth where fldIsland=(50 + " & request("txtSearch1") & " - " & request("txtSearch2") & ")%50 &#060;= " & ((request("txtSearch1"))*2) & " or (50 + " & request("txtSearch1") & " - " & request("txtSearch2") & ")%50 &#062;= 50- " & ((request("txtSearch1"))*2) & ";"<BR><BR>Where txtSearch1 is how many spots to search on each side of the target<BR>txtSearch2 is the target.<BR><BR>Writing the sql out shows up like this.<BR><BR>select networthid,fldname,fldnetworth,flddate,fldIsland from tblnetworth where fldIsland=(50 + 2 - 10)%50 &#060;= 4 or (50 + 2 - 10)%50 &#062;= 50- 4;<BR><BR>the result is it just ignores it and displays every record.<BR>Using mySQL as the database.<BR><BR>What am I blatantly missing?<BR><BR>

7. Senior Member
Join Date
Dec 1969
Posts
96,118

## Ummm...all wrong...

fldIsland is supposed to be in the *middle* of all that.<BR><BR>Ian&#039;s code was:<BR><BR>select * from Table where (50 + ID - target)%50 &#060;= range or (50 + ID - target)%50 &#062;= 50-range <BR><BR>Now, for starters, what the heck are the form fields "txtSearch1" and "txtSearch2" supposed to be???<BR><BR>One needs to be the target and the other the range.<BR><BR>So why not name them that in the form? Fields "txtTarget" and "txtRange"?<BR><BR>And then assign them to variables, ONE TIME, instead of having to go get them from the Request collection over and over again.<BR><BR>&#060;%<BR>&#039; might as well insure they are numbers, while you are at it<BR>range = CINT( Request("txtRange") )<BR>target = CINT( Request("txtTarget") )<BR>%&#062;<BR><BR>Hmmm...looks like you are doubling the value you get from the &#060;FORM&#062; to get the range?? Okay, so just do:<BR>&#060;%<BR>range = 2 * CINT( Request("txtRange") )<BR>%&#062;<BR><BR><BR>And then just use them in the SQL. I&#039;d probably do it this way:<BR><BR>&#060;%<BR>SQL = "SELECT networthid,fldname,fldnetworth,flddate,fldIsland " _<BR> & " FROM tblnetworth " _<BR> & " WHERE (50 + fldIsland - target)%50 &#060;= range or (50 + fldIsland - target)%50 &#062;= 50-range"<BR><BR>SQL = Replace(SQL,"target",target)<BR>SQL = Replace(SQL,"range",range)<BR><BR>Response.Write "DEBUG SQL: " & SQL & "&#060;HR&#062;"<BR><BR>...<BR>%&#062;<BR><BR><BR> <BR>

8. Senior Member
Join Date
Dec 1969
Posts
190

## ahhh

I see, I screwed up what the ID stood for.<BR><BR>Gottchya, works perfect - thank you Bill and Ian

9. Senior Member
Join Date
Dec 1969
Posts
190

## revisiting again - without luck

Here is my SQL.<BR>mySQL="Select fldName, fldnetWorth, fldhonor, fldLand, fldNWacre, fldIsland FROM tblserver" & strServer & " WHERE fldDate=&#039;" & strTodaysDate & "&#039;" & strWhatDistance & " order by " & strName & " " & strSort & ";"<BR><BR>Here is the strWhatDistance<BR><BR>strWhatDistance=" AND (50 + fldIsland - target)%50 &#060;= range or (50 + fldIsland - target)%50 &#062;= 50-range " <BR>strWhatDistance = Replace(strWhatDistance,"target",target) <BR>strWhatDistance = Replace(strWhatDistance,"range",range) <BR><BR>Here is the response.write of the SQL<BR><BR>Select fldName, fldnetWorth, fldhonor, fldLand, fldNWacre, fldIsland FROM tblserver3 WHERE fldDate=&#039;2004/10/2&#039; AND (50 + fldIsland - 3)%50 &#060;= 1 or (50 + fldIsland - 3)%50 &#062;= 50-1 order by fldNetworth ASC;<BR><BR>I removed the doubling of the range as it was doubling on both sides of the search. And it does indeed show the island search range. Which is awesome.<BR><BR>However, instead of showing just the date specified. It takes every record - like the date filter was not even there.<BR><BR>It displays records like<BR>Num Kingdom Name Networth Honor Acres Island N/W Acre <BR>1 10 left (18:4) 210,101 1,655 1,096 4 191.70 <BR>2 Dreamer Believer (2:3) 638,626 6,056 3,883 3 164.47 <BR>3 the Fiercesom Warrio 660,544 9,327 5,685 2 116.19 <BR>4 the Fiercesom Warrio 669,237 9,011 5,490 2 121.90 <BR>5 the Fiercesom Warrio 728,679 9,909 5,926 2 122.96 <BR>6 yadayadayada (1:2) 777,136 7,947 5,424 2 143.28 <BR>7 We Are Too SmaLL (1:2) 851,723 8,746 6,283 2 135.56 <BR><BR>Which it shouldn&#039;t - it should only show the records for the specified date (basically show no repeating of names). What am I missing thats glaring me in the face? or is the MOD search ignoring the date criteria?<BR><BR>

10. Senior Member
Join Date
Dec 1969
Posts
10,852

## RE: revisiting again - without luck

I didn&#039;t look closely, but it immediately strikes me as an operator precedence issues. <BR><BR>I *think* that you wanted this instead:<BR>[code language="T-SQL"]<BR>FROM tblserver3 WHERE fldDate=&#039;2004/10/2&#039; AND ((50 + fldIsland - 3)%50 &#060;= 1 or (50 + fldIsland - 3)%50 &#062;= 50-1) order by fldNetworth ASC;<BR>[/code]<BR><BR>(Note the extra set of parentheses)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•