seperating + grouping results by brand

[[o_O]]

here (http://www.digitalfilm.co.nz/cart/index.php?CA=Cameras&SES=20021212105739-210.54.112.199) is an example of what i'd like to do.

Have the products seperated and grouped by brand and each have the brand name displayed as a little heading over each group.

here is a quick view of my tables


brand
brand_id
brand_name

products
product_id
model
image
brand -> brand.brand_id
price
description
features


Just grouping by brand wouldn't do this so :confused:
is some sort of loop used to accomplish this?

[oracleguy]

Yeah what you'd do is run a select statement on your database to only get one peticular brand.


So....

set brands = myDB.Execute("SELECT * FROM brand;")

Do while not brands.EOF
%><h1><%=brands("brand_name")%></h1><br /><%

set tempRS = myDB.Execute("SELECT * FROM products WHERE brand='" & brands("brand_id") & "';")

Do while not tempRS.EOF
%>
#<%=tempRS("product_id")%> &nbsp; <%=tempRS("Model")%>

%>
tempRS.MoveNext
Loop

tempRS = Nothing

brands.MoveNext
Loop


Now you'll need to write the DB Connection code if you already haven't and change 'myDB' to whatever your connection object is. And you'll need to continue adding the fields where it has the product id and model.

Hope this helps.

[[o_O]]

o.k cool, it shot the first lot out under the heading 'canon' :)

but then instead of carrying on with the next brand it does

Microsoft VBScript runtime error '800a005b'

Object variable not set: 'tempRS'

//results.asp, line 275



according to homesite line 275 is:

tempRS = Nothing


? so it's saying i'm trying to use tempRS but because it's set to nothing there is no tempRS right? but that line is after loop so it should only be executed after its looped through all brands/products ?

[whammy]

There are two loops in his code... and it shouldn't be closed until after the second loop instead of the first... so move it to after the second "Loop" statement it should work fine.

Just remember when using stuff like this to make sure you don't set the object to nothing until you are no longer using that recordset, it should solve that problem. :)

[[o_O]]

oki doki, now the code now reads:

<%
set brands = connection.Execute("SELECT * FROM brand")

Do while not brands.EOF
%><h1><%=brands("brand_name")%></h1><br /><%

set tempRS = connection.Execute("SELECT * FROM product WHERE brand='" & brands("brand_id") & "';")

Do while not tempRS.EOF
%>
#<%=tempRS("product_id")%> <%=tempRS("model")%>

<%
tempRS.MoveNext
Loop

brands.MoveNext
Loop

tempRS = Nothing
%>


And it displays all products seperated and grouped like i wanted. :D
After the last it has:

Object variable not set: 'tempRS'

/results.asp, line 278


which is of course:

line 278 tempRS = Nothing


Is this becuase to set the variable it has to loop again but the loop is finished?

[whammy]

Oh you have to use...

Set tempRS = Nothing

Didn't notice it missing. But from what I can see, it should still be after the second loop. That should work ok. :)

[[o_O]]

yep that fixed it. :)


- Is it o.k to have long SQL statements or better to break them up:

SELECT ..
FROM...
WHERE ...

the above makes it easier to read but it means you have to use more code and break it up, does this affect performance even in the sligthest?

FROM table"&_
"WHERE this = that"&_
"AND this doesn't = that;"

[whammy]

Nah, I don't think it matters much, if at all...

[[o_O]]

I just noticed there was no

Set tempRS = Server.CreateObject("ADODB.Recordset")

first. Up until now (not long) I always do this but the example
still worked. I am now questioning whether i should be doing this before doing queries? whats the point

Server.CreateObject("ADODB.Recordset") ?

[whammy]

The recordset is not required unless you will be using it as an object itself (I think that's the right way to explain it... I know the difference, but I don't know how to explain it at all right now!).

I know some instances where I have to use it at work, but unfortunately I'm drawing a total blank and can't think of any examples. ;)

[[o_O]]

Hmmm alrighty, how about if you were to create a sort of next/previous function on the results page.

Would you need to do it for that?

I thought you might becuase you'd need to access the amount of rows returned which would be a recordset object property? or maybe another way :o I don't know

[whammy]

Yeah in that case you'd need to use the recordset object for sure (database paging). There is a simpler need for that, but right now I have no idea what it is, LOL.

I'm suffering from brain overload trying to learn ASP.NET. If I was writing an application I would probably just "know" this automatically... sigh

[[o_O]]

from above:

Do while not brands.EOF


isn't that using the recordset as an object?

*excuse me ignorance :cool:

[whammy]

Well it is, but in that case there's no reason to define the recordset itself as an object... for the life of me I can't think of an example. Go figure. If I was writing an application I would probably include it without thinking at all!

When I do figure out an example, (and I have some at work!) I will post them here, lol

I actually DO normally know this, but I am experiencing a brain cloud... help? I'm wondering if per chance I didn't lose a thousand brain cells or so a few minutes ago... lol ;)

[[o_O]]

it's working, im happy. :)

[oracleguy]

Sorry for forgeting the 'set'... I'm glad you were able to get it to work nonetheless.

Oh and if you are curious, here is an example of database paging.

http://www.cfdev.com/code_samples/code.cfm/CodeID/57/ASP/Paging_code

[[o_O]]

thanks. I would like to add some sort of next/previous functionality later but it's too confusing to look at it now :eek: One thing i'm just noticing now with this results page, if there are no products to display the brand name heading is still of course displayed.

what's the simplest way to check and suppress it from displaying all brand names when there may not be any actual products to display under them?

i.e, i'm getting:


Zing
Zing tennis ball
Zing hat
Zing pants

Tpt
Tpt basketball

Zorf
<no products here>

some other brand
<no products here>

[Roy Sinclair]

Well I hate to say it but that code is just wrong, you should avoid doing selects inside loops whenever possible and in this case it's very possible.


<%
set productRS = connection.Execute("SELECT * FROM product left join brand on product.brand = brand.brand_id order by brand.brand_name")
dim save_brand_id
save_brand_name = null
Do while not productRS.EOF
if save_brand_name <> productRS("brand_name") then
save_brand_name = productRS("brand_name")
response.write "<h1>" & save_brand_name & "</h1><br />"
end if
response.write "#" & productRS("product_id") & " " & productRS("model") & "<br />"
productRS.MoveNext
Loop
set productRS = Nothing
%>


The above code is cleaner, shorter, has a lot less execution overhead and also solves the problem of brands being listed when no products exist for that brand.

Oh yeah, it'll also make adding prev/next functionality a lot simpler too.

[[o_O]]

thanks for the reply Roy. :)

I have a couple of questions.
- using your code posted above and it outputs all products grouped by brand but the brand name heading isn't displayed over each group? basically just all products are all just listed like so (www.theory1.orcon.net.nz/list.gif)

Here is a picture (www.theory1.orcon.net.nz/tables.gif) showing all 3 database tables if your interested.


the second thing which i spent quite a few hours last night stressing over is how to get this whole thing to work with a little bit of additional sql and this is what its all about, let me clarify:

On my home page i have a <select> menu listting different product categories 'digital cameras', 'basketballs', whatever etc and on the home page also is a 'search' box (seperate form). Both of these submit to the results page. On the results page should be displayed products matching the criteria (depending on whether you came from the search box or category menu)
the results should be displayed grouped and seperated by brand name which is what i've been trying to do in regards to this thread. I thought if i could get that to work it would be easy to just to add the little bit of additional sql criteria to the query. I tried:


If Request.Form("byCategory") <> "" Then
SQL = "SELECT DISTINCT p.product_id, p.model, p.price FROM product p, brand b WHERE p.brand ='"&brandRS("brand_id")&"' AND p.category = '"&byCategory&"' GROUP BY p.product_id"
End If

If Request.Form("quicksearch") <> "" Then
SQL = "SELECT DISTINCT p.product_id, p.model, p.price FROM product p, brand b WHERE p.brand ='"&brandRS("brand_id")&"' AND p.model = '"&quicksearch&"' OR b.brand_name = '"&search&"' OR p.description LIKE '"&search&"' OR p.features LIKE '%"search"' GROUP BY p.product_id"
End If


Basically no matter what i tried with that i ened up with resulting products on the result page repeating over and over, either that or it just didn't work at all sometimes the byCategory was o.k but never using the search
This seems like such a mess now :( and this was the idea (www.theory1.orcon.net.nz/idea.gif)

[[o_O]]

It's almost working now with the code below. The one problem is now that is only displays one brand heading (in blue) but not others. I'm sort of stuck


<%
Set productRS = connection.Execute("SELECT * FROM product left join brand on product.brand = brand.brand_id Where product.category = '"& byCat &"' order by brand.brand_name")
save_brand_name = Null

If productRS("brand") <> "" then
save_brand_name = productRS("brand_name")
%>
<strong><%=save_brand_name%></strong><br>
<%
End If
Do while not productRS.EOF
%>
<a href="detail.asp?item=<%=productRS("product_id")%>" class="main" title="See details for this item"><%=productRS("brand_name")%> <%=productRS("model")%></a> <%=FormatCurrency(productRS("price"))%> <a href="results.asp?add=yes&item=<%=productRS("product_id")%>" class="blueLink" title="Add this item to your shopping cart">Add to Cart</a>&nbsp;&nbsp;<img src="graphics/add.gif" width="8" height="8" alt="" /><br />

<%
productRS.MoveNext
Loop

Set productRS = Nothing
%>



it does this:

http://www.theory1.orcon.net.nz/results.gif


how can i alter the code so that it displays the blue brand name heading over each brand group?

[whammy]

P.S. I thought of a simple example (besides database paging) in which you'd need to use the recordset object, and that's if you need to access any of its properties (of course), i.e.:

Dim item
For each item in rs.Fields
'do something
Next

[whammy]

P.S. You don't have the part that writes the brand name in your loop, therefore it's only going to execute once...

[[o_O]]

I know, if i put it in the loop then it makes that heading for every product instead of just once over that brand group

:confused: this is where i'm stuck.

[whammy]

In cases like these, I know of no other way than to use nested loops... yeah, it slows performance down a bit, but how else are you going to do it?

[[o_O]]

I didn't have a problem with doing that, it's just that it showed all brand names even if their were no products to display under them ??

[oracleguy]

This revision should clear up the problem of a heading if there are no products.


set brands = myDB.Execute("SELECT * FROM brand;")

Do while not brands.EOF

set tempRS = myDB.Execute("SELECT * FROM products WHERE brand='" & brands("brand_id") & "';")

If not tempRS.EOF Then%><h1><%=brands("brand_name")%></h1><br /><%End If

Do while not tempRS.EOF
%>
#<%=tempRS("product_id")%> <%=tempRS("Model")%><br />

%>
tempRS.MoveNext
Loop

Set tempRS = Nothing

brands.MoveNext
Loop


Don't get me wrong Roy I totally see where your coming at but as whammy said this is pretty much the only way. And as far as performance goes, what its going to take an extra 5 miliseconds to execute?

[Roy Sinclair]

Originally posted by [o_O]
It's almost working now with the code below. The one problem is now that is only displays one brand heading (in blue) but not others. I'm sort of stuck


<%
Set productRS = connection.Execute("SELECT * FROM product left join brand on product.brand = brand.brand_id Where product.category = '"& byCat &"' order by brand.brand_name")
save_brand_name = Null

If productRS("brand") <> "" then
save_brand_name = productRS("brand_name")
%>
<strong><%=save_brand_name%></strong><br>
<%
End If
Do while not productRS.EOF
%>
<a href="detail.asp?item=<%=productRS("product_id")%>" class="main" title="See details for this item"><%=productRS("brand_name")%> <%=productRS("model")%></a> <%=FormatCurrency(productRS("price"))%> <a href="results.asp?add=yes&item=<%=productRS("product_id")%>" class="blueLink" title="Add this item to your shopping cart">Add to Cart</a>&nbsp;&nbsp;<img src="graphics/add.gif" width="8" height="8" alt="" /><br />

<%
productRS.MoveNext
Loop

Set productRS = Nothing
%>



it does this:

http://www.theory1.orcon.net.nz/results.gif


how can i alter the code so that it displays the blue brand name heading over each brand group?

When you adjusted the code I gave you, you also moved the check highlighted above in red from inside the loop to outside the loop which is why you only get the first brand listed.

[[o_O]]

:) ! cool :cool:

[[o_O]]

what about


Set productRS = connection.Execute(SQL)
If productRS.EOF Then
%> <tr>
<td colspan="5" height="15"><img src="graphics/spacer.gif" height="15" width="1" alt="" /></td>
</tr>
<tr>
<td colspan="5" class="padd2"><p class="blueBoldTxt">No Matches Found</p></td>
</tr>
<%
End If
Do while not productRS.EOF

' Set testVar to Current Record's Brand Value
testVar = productRS("brand_name")

' Test for Header
If testVar <> examVar Then
%>
<tr>
<td colspan="5" height="15"><img src="graphics/spacer.gif" height="15" width="1" alt="" /></td>
</tr>
<tr>
<td colspan="5" class="padd2"><p class="blueBoldTxt"><%=productRS("brand_name")%></p></td>
</tr>
<% End If %>
<tr>
<td class="padd2"><p><a href="detail.asp?item=<%=productRS("product_id")%>" class="main" title="See details for this item"><strong><%=productRS("brand_name")%></strong> <%=productRS("model")%></p></td>
<td width="30"><img src="graphics/spacer.gif" border="0" width="30" height="1" alt="" /></td>
<td class="padd2"><p><strong><%=FormatCurrency(productRS("price"))%></strong></p></td>
<td width="30"><img src="graphics/spacer.gif" border="0" width="30" height="1" alt="" /></td>
<td class="padd2"><p class="blueTxt"><a href="results.asp?add=yes&item=<%=productRS("product_id") & criteria%>" class="blueLink" title="Add this item to your shopping cart">Add to Cart</a>&nbsp;&nbsp;<img src="graphics/add.gif" width="8" height="8" alt="" /></p></td>
</tr>
<%
' Set examVar
examVar = productRS("brand_name")

productRS.MoveNext
Loop

connection.Close

Set connection = Nothing
Set productRS = Nothing
%>




Not my idea but it does work :) ?

[Roy Sinclair]

Not my idea but it does work ?

Huh? Aren't you the one with the database ad the rest? Try it and see if it works.

[[o_O]]

When you adjusted the code I gave you, you also moved the check highlighted above in red from inside the loop to outside the loop which is why you only get the first brand listed.

taken from up above:
it showed all brand names even if their were no products to display under them ??



Huh? Aren't you the one with the database ad the rest? Try it and see if it works.

The '?' was implying 'what do you think of it?' it does work but could it be improved? for example :)

[whammy]

Hey... it works! For now, go with the Bruce Li technique:

"If it works, use it."

Move on to the next project - I'm sure you will find plenty of ways to optimize things on your own as you continue to learn. ;)

Right now I'm bogged down in an application that should be simple but has turned out to be incredibly complicated since I have had to squeeze minute specifications out of my account manager and others for every little detail along the way...

Specifications are sometimes WAY too hard to come by when you're trying to code an application where noone is sure what they really need, and it's a bit frustrating to try to "guess" what will be in everyone's best interest... Try to avoid that whenever possible, and see if you can get a flowchart (best scenario)! :(

To be honest, if your application isn't taking up huge amounts of server memory, etc., and you don't have any really bad scripting going on, I wouldn't worry about it. Any "improvements" would likely be in the neighborhood of nanoseconds (or milliseconds at the worst). ;)

[[o_O]]

cool :cool: