Filtering problem 3

[Zoom1177]

Hi,

Lets say i have the following table

Name________Sport
=================
Brian Soccer
Karen Golf
Barb Basketball
Terry Soccer
Marie Swimming
Tim Basketball
Jack Swimming
Amy Golf

I am getting this whole table in a recordset and i am trying to display the following result on my page

In my ASP/VBscript got a table and in the first row i want to put the sport and underneath it(in other rows) the people that plays this sport. like such:

Soccer
=======
brian
terry

This display is in a "search_result.asp" My search page is passing the result in a text box "txtSearch" and in the recordset i am writing "where sport LIKE txtSearch%

so if someone wrote the letter S only not soccer i should get the following result


Soccer
=======
brian
terry

Swimming
=======
marie
jack

as both starts with the letter S

But what happens is that i got the first sport (alphabetically) in that case would be Soccer and the names' of people of both groups (soccer and swimming) under the same title which is Soccer

Now the problem is i am making a Repeat region ONLY on the NAME field not on the sport because when i tried to put the repeat region on the whole table it didn't work

And in SQL Server i couldn't group my result the way i want...so is there an easy way i can get both results if someone entered a keyword that have more than one match in my database and display it as i explained above with items/people belong to that category AND repeat the same process for other categories juse like i explained before

so if someone Entered the letter S, i should get the following result in my page

Soccer
=======
brian
terry

Swimming
=======
marie
jack


I am SORRY for explaining in so much details and repeatings but i wanted to make myself clear so that the thread doesn't get longer later.

Thank you and any help would be appreciated.

 

[Abraxus]

This is with SQL Server? You should be able to have your SQL statement for the recordset be:

strSQL = "SELECT name,sport FROM [COLOR=blue]yourtablename[/color] WHERE sport LIKE '" & txtSearch & "%' ORDER BY sport,name"

Which should generate a recordset with all of the names of the people returned alphabetically and grouped by sport alphabetically. Perhaps the trick is constructing your output loop properly:

lastsport = ""
while not rs.EOF
   currsport = rs.fields.item("sport").value
   if currsport = lastsport then
      response.write("<br>" & rs.fields.item("name").value)
   else
      response.write(currsport & "<br>=======<br>" & rs.fields.item("name").value)
   end if
   lastsport = currsport
   rs.MoveNext
wend

Ok, based on how I understand your question that's what I see for a possible solution. I know there are different ways to reference recordset fields so let me know if my code gives you any questions.

Eric

 

[Sheco]

Suppose the user entered this: [tt]S' OR 1=1[/tt]

If this is for a public website then do a google search on "SQL Injection" attacks. I think wikipedia has something OK about it.

 

[Abraxus]

Super point Sheco! Most of my ASP programming has the luxury of being on an intranet so I may be pretty soft when it comes to leaving my examples open to SQL injection

 

[Zoom1177]

Thanks ecwcee i'll try it.

Thanks Sheco for the tip i'll check it out.

 

[Zoom1177]

my site is for an intranet but when you get two categories one called "Soil Grouting" and the other called "Soils something" and someone just entered the word Soil...i think my code should pull both results for both categories.

I'll check SQL injection.

thanks all

 

[Abraxus]

That's right (strange names for sports, though) and the example that I've written above will do that. Sheco's point relates to the user entering an apostrophe, which will break out of the 'txtSearch%' string by changing the construction of the statement to read something like:

...WHERE sport LIKE 'S' (now you can put whatever you want here) '%'...

Which shows a simple example of how people can reconstruct your SQL statements if you're not careful. Definitely a topic any programmer working with web scripting and databases should be aware of.

Eric

 

[Zoom1177]

ecwcee,

Actually its not sports at all, it's a construction company and there are custom categories they built "soil grouting" is one of them, i just put sports to make it easy.


my select statment that return the table for me in the page is working fine
here it is(i should've past it earlier but it could've made things more complicated in my first post)

SELECT distinct employees.id, employees.empname, employees.address,employees.phone, employees.email, xdmaster.expertise
FROM employees INNER JOIN XDassign ON employees.ID = XDassign.empid INNER JOIN XDmaster ON XDassign.xid = XDmaster.id
WHERE xdmaster.expertise like Search% GROUP BY xdmaster.expertise, employees.empname,employees.id, employees.address,employees.phone, employees.email
ORDER BY xdmaster.expertise,employees.empname

so i do get what i want but i can't DISPLAY it the way i want...i'll try your loop

thank you very much

 

[Zoom1177]

NOTE: Search% is a parameter that receives the txtSearch text box from the Search page.

 

[Abraxus]

Fantastic, thanks for the elaboration. For as much as I love all the programming I get to do I am also a civil engineer and my asp with sql server programming is done on my company's intranet, so there's a good chance some of our difficulties have ended up mighty similar Let us know if you need any more display suggestions or clarifications, since I'm sure I'm not the only one who has spent a lot of time ensuring that things display the way I want them to after getting a complicated select statement working.

 

[Zoom1177]

ecwcee,

Thanks and i am glad some of the difficulties are the same we can share ideas. As for more display suggestions, they will be always appreciated.

In my opinion, and to save more time down the road, i would love to do everything on SQL Server but unfortunatly sometimes it's not possible, you've got to go write some kind of looping on the front-end to filter your recordset like you put up (which i didn't try yet but i will today ) the problem is that takes more time sometimes even days...right now i am doing a Text box search...when i am done...i've got to do a TREE Menu so good luck for me!!

I've never done that before but i think when/if i finish it i'll be learned a lot.

Thanks all for your help


Thanks!

 

[Abraxus]

Good luck to you indeed! A powerful feature of SQL Server is that it can create Views. Take a look through Enterprise Manager (hopefully you're using that to admin your sql server) and learn about creating views. They can't take variables but what you can use them for is "joining" multiple tables together into one "view." A huge advantage for that in both our cases would be that you can organize it so you no longer have to construct exceedingly complex select statements on your asp pages, you merely run a simpler select statement against the view. ASP uses SQL Server views the same as tables and generates recordsets in exactly the same way. So if you work it correctly you can have your SQL Server do a lot of the table processing by constructing a view and then run queries against the view using ASP. Good luck

 

[Zoom1177]

ecwcee,

Sorry it took me long to respond

You're script worked Thank you!

here is the part you wrote

<%
dim lasttblhead
dim currtblhead
lasttblhead =""

while not rsSearch2.eof

currtblhead = rsSearch2.fields.item("expertise").value

if currtblhead = lasttblhead then
%>
<table width="600" border="0">
<tr>
<td>&nbsp;</td>
<td width="143" class="results"><% response.write(rsSearch2.fields.item("empname").value)%></td>
<td width="144" class="results"><% response.write(rsSearch2.fields.item("address").value)%></td>
<td width="122" class="results"><a title="Click to dial with your Cisco IP Phone" href="tel:<% response.write(rsSearch2.fields.item("phone").value)%>"><% response.write(rsSearch2.fields.item("phone").value)%></a></td>
<td width="122" class="results"><a href="mailto:<% response.write(rsSearch2.fields.item("email").value)%>"><% response.write(rsSearch2.fields.item("email").value)%></a></td>
</tr>
</table>
<%
ELSE
%>

<table width="600" border="0">
<tr bgcolor="#999999">
<td width="51">&nbsp;</td>
<td colspan="4" class="style2"><% response.write(currtblhead)%></td>
</tr>
<tr>
<td>&nbsp;</td>
<td width="143" class="results"><% response.write(rsSearch2.fields.item("empname").value)%></td>
<td width="144" class="results"><% response.write(rsSearch2.fields.item("address").value)%></td>
<td width="122" class="results"><a title="Click to dial with your Cisco IP Phone" href="tel:<% response.write(rsSearch2.fields.item("phone").value)%>"><% response.write(rsSearch2.fields.item("phone").value)%></a></td>
<td width="122" class="results"><a href="mailto:<% response.write(rsSearch2.fields.item("email").value)%>"><% response.write(rsSearch2.fields.item("email").value)%></a></td>
</tr>
</table>
<%
end if
lasttblhead = currtblhead
rsSearch2.movenext
wend
%>

I test it and it's working fine for now
thanks again