Grouping recordsets into columns

[meldrape]

Hello,

I am using the following code to group menu items with categories and subcategories, this one being entrees. This code works great, but since there are 6 entree groups, there are too many columns spread across. What I need is to take the following code one step further and split the whole thing up into three across, i.e. if there are 9 strTypes which there are in other menu groups, I need to split the whole shmere into 3 groups of three. Any thoughts how I can do this? Thanks in advance for your input.

<%
set rsEntrees=objConn.execute("select * from qyEntrees")
Response.Write "<tr><td colspan=5 align=center valign=top>" & rsEntrees("entree_notes") & "<tr><td colspan=5 valign=top align=center><table width='100%' cellpadding=5 align=center><tr>"
'entrees
do While NOT rsEntrees.EOF
	strType = rsEntrees("entree_name")
  If strType <> strOldType Then
    Response.Write "<td width=" & width & " valign=top class=timesit18><U>" & strType & "</U><br>"
  End If
	strName=rsEntrees("entrees_items_name")
	if strName <> strOldName then
	  Response.Write "<br><div class=timesit14>" & strName & "</div>"
	end if
		if rsEntrees("entrees_items_notes")>"" then
		Response.Write "<div class=timesit12>" & rsEntrees("entrees_items_notes") & "</div>"
		end if

  strOldName = strName
  strOldType = strType
  rsEntrees.MoveNext
loop
Response.Write "</table></table>"

 

[FesterSXS]

Before you do anything you need to fix your HTML code.

Nested tables should look something like this:

<table>
<tr>
 <td></td>
 <td></td>
</tr>
<tr>
 <td colspan="2">
 <table>
 <tr>
  <td></td>
  <td></td>
  <td></td>
 </tr>
 </table>
 </td>
</tr>
</table>

I dont see any closing </td> or </tr> tags in your code. You also have 2 closing </table> tags next to each other - that is just incorrect coding. You may find that the page renders/looks OK in Internet Explorer but that is only because it is so forgiving. Standards compliant browsers would certainly not like it.

Sorry if I seem a little harsh but you really do need to fix (and understand) these fundamental errors first before you get involved with anything more complex.

Tony

Spirax-Sarco - steam traps control valves heat exchangers
Sun Villa - Luxury Florida accommodation

 

[BillyRayPreachersSon]

When you have tidied your code, you can validate it at:

http://validator.w3.org

It will point out any errors that may have slipped through the net.

Hope this helps,
Dan


[tt]Dan's Page [blue]@[/blue] Code Couch

http://www.codecouch.com/dan/

[/tt]

 

[Tarwn]

As far as a solution to your problem (after you clean up the output), you could easily have a counter that you reset when you start a new category. This way you can keep track of the number of cells you output to the table. When the number of cells is divisble by 3 (ie: ctr mod 3 = 0) then you will want to start a new row. After you start outputting a new cell you will want to increment the counter each time. Something like this:

[highlight]Dim col_ctr[/highlight]
do While NOT rsEntrees.EOF
   strType = rsEntrees("entree_name")
   If strType <> strOldType Then
      [highlight]'End the previous open column - if the col_ctr is empty then it was never reset, hence we don't have an open row to close[/highlight]
	  [highlight]If Not IsEmpty(col_ctr) Then Response.Write "</td>"[/highlight]
	  [highlight]'if col_ctr is evenly divisble by 3 we need to end previous row and start next one[/highlight]
	  [highlight]'   we can use the IsEmpty again to determine if there were any previous rows[/highlight]
	  [highlight]If Not IsEmpty(col_ctr) And col_ctr mod 3 = 0 Then Response.Write "</tr>"[/highlight]
	  [highlight]If col_ctr mod 3 = 0 Then Response.Write "<tr>"[/highlight]

      Response.Write "<td width=" & width & " valign=top class=timesit18><U>" & strType & "</U><br>"

	  [highlight]'increment the column counter as we have started a new column[/highlight]
	  [highlight]col_ctr = col_ctr + 1[/highlight]
   End If

   strName=rsEntrees("entrees_items_name")
   If strName <> strOldName Then
      Response.Write "<br><div class=timesit14>" & strName & "</div>"
   End If

   If rsEntrees("entrees_items_notes")>"" Then
      Response.Write "<div class=timesit12>" & rsEntrees("entrees_items_notes") & "</div>"
   End If

   strOldName = strName
   strOldType = strType
   rsEntrees.MoveNext
Loop

Additional comments: You should not need to define the width in your td, as you could easily do this using CSS for the class you have assigned to that cell. Ditto with the underline tag (text-decoration:underline for the cell class, text-deoration: none for the rest). Also, the br tags are unnecessary after the divs, as those are block elements. If you would like some spacing between the vertical blocks then I would suggest using margin-bottom in your CSS for that block, as you will be able to fine-tune it a great deal more then a break tag.
I am also unsure why your are using two tables, unless there is something above this section in your table, you only need one.

Also, if you have any control over the data structure, you may want to modify it a bit to better fit what your trying to do and provide greater flexibility. From what I see of your code it appears that you are building an online menu, of sorts. Each Entree Type can have multiple Entree entries, which in turn can have multiple sets of Notes. My immediate reaction would be to determine if it is necessary for entrees to have more than one notes section. It would make things much faster if the data for notes could be entered in some sort of textarea field so that the entire notes for one entree could be stored in a single field. Using that method you could then replace any vbCrLf characters (carriage return line feeds) in the field with break tags as you output it, so that the output would look the sasme as the way the user entered it, but would be restricted to a single field in the database.
Now, if that was an acceptable modification to the requirements, I would then split the Entree Type and Entrees into two tables, like so:

EntreeType
et_id - autonumber or seeded incrementing integer, primary key
et_name - text or varchar field
et_notes - text, varchar, memo, whatever field

Entree
e_id - autonumber or seeded integer, primary key
et_id - integer, foreign key to EntreeType
e_name - text/varchar - name of entree
e_notes - text/varchar/memo/whatever - description of entree

If it is necessary to have multiple notes entries for a single entree, I would suggest going with a 3 table design:

EntreeType
et_id - autonumber or seeded incrementing integer, primary key
et_name - text or varchar field
et_notes - text, varchar, memo, whatever field

Entree
e_id - autonumber or seeded integer, primary key
et_id - integer, foreign key to EntreeType
e_name - text/varchar - name of entree

EntreeNotes
en_id - autonumber or seeded integer - primary key
e_id - integer, foreign key to Entree
en_text - text/varchar/memo/whatever - holds the actual text of this notes

A couple reasons behind doing this:
1) The Entree Type and Entree Name are both in the database one time now, meaning less storage space used and less chance of having two entries that are almost spelled the same.
2) If you are providing an admin tool to interface to the database, you can now allow the user to create Entree Type's independantly. Additionally you can make a simple Add or Edit Entree page that allows the user to pick an Entree Type from the database. If you go with the 3 table setup you can create an Add Notes section with a dropdown for both Entree Type and Entree Name.
3) Again, if you have an admin tool, you will now be passing a great deal of necessary information as a numeric id (dropdowns) instead of passing the word for the type or entree. This means that you don't have to worry as much if the end user created an Entree with chareacters like an ampersand, quotes, etc because the form would be submitting that data by the id number. Additionally it means you don't have to worry about them mispelling an Entree or Entree Type and accidentally creating a new one instead of re-using an existing one.
4) We have also added a new field called et_notes. This would be for the information that applies to all entrees (such as "These entrees are served after 5pnm and comes with your choice of rolls or whatever"). Again, seperating the tables means we only store this data once, instead of once per set of notes.
Now, in terms of requesting the data you would use some sort of INNER JOIN, for example:

[b]Two Table Design:[/b]
"SELECT EntreeType.et_id, et_name, et_notes, e_id, e_name, e_notes FROM EntreeType INNER JOIN Entree ON EntreeType.et_id = Entree.et_id ORDER BY et_name, e_name"
[b]Three Table Design:[/b]
"SELECT EntreeType.et_id, et_name, et_notes, Entree.e_id, e_name, en_notes FROM (EntreeType INNER JOIN Entree ON EntreeType.et_id = Entree.et_id) INNER JOIN EntreeNotes ON Entree.e_id = EntreeNotes.e_id ORDER BY et_name, e_name, en_id"

As far as output is concerned, I think your 3-column method will work, but I would want to make some modifications to it. First, I would restrict the whole thing to one table. My loop would look something like:

[i]using the 2 table method for example[/i]
Dim last_type, col_ctr
Response.Write "<table class=""menu"">"
Do Until MyRS.EOF
   'if EntreeType has changed or on first run
   If MyRS("et_id") <> last_type Then
      'if this not first run, end previous section
      If Not IsEmpty(col_ctr) And col_ctr mod 3 = 0 Then Response.Write "</td>"
      Response.Write "</tr>"

      'start a new Entree Type Section
      Response.Write "<tr><td colspan=""3"">"
      Response.Write "<div class=""entree_type"">" & MyRS("et_name") & "</div>"
      Response.Write "<div class=""entree_type_desc"">" & MyRS("et_notes") & "</div>"

      'reset the column counter
      col_ctr = 0
   End If

   'if col ctr is divisible by three, end previous row (or end entree type row if this is first item for this type)
   If col_ctr mod 3 = 0 Then Response.Write "</tr><tr>"

   'output an entree in it's own cell
   Response.Write "<td class=""entree"" valign=""top"">"
   Response.Write "<div class=""entree_name"">" & MyRS("e_name") & "</div>"
   Response.Write "<div class=""entree_desc"">" & MyRS("e_notes") & "</div>"
   Response.Write "</td>"

   'increment column counter
   col_ctr = col_ctr + 1

   'increment recordset
   MyRS.MoveNext
Loop

'if col_ctr is not empty then we must have output something, therefore a row must still be open
If Not IsEmpty(col_ctr) Then Response.Write "</tr>"
'and close the table
Response.Write "</table>"

The code would be a little more complicated for the 3 table method, but basically an extension of something similar to the above. The inclusion of standard classes for each datafield will allow you to use CSS completely to style the menu (similar to your example code above, but a little extended).

In any case, have to get back to work, hopefully this will have provided something useful for you,

-T

 

[meldrape]

Many thanks to you Tarwn. I will clean up the HTML and apply your ideas. I can't thank you enough for taking so much time to analyze this and help me out.