Selecting and displaying the data--- Part 2 1

[amorous]

Hi Drexor...

Small Change ( of couse not small i guess)

want to display my table in the below format now...

Sunday|Monday|Tuesday|Wednesday|Thrusday
________________________________________
Type1 |Type2 |Type1 |Type4 |Type1
value11|value21|value31|value41|value51
Type2 |value22|Type4 | |Type4
value12| |value32| |value52
value13| | | |


I mean grouping them by categories....type1, type2, etc....

Thanks in advance

-VJ

 

[amorous]

And one more thing if it helps... There are only 4 different types in which all items can be categorized...

-VJ

 

[DreXor]

is this like value1 = "crab platter" and type1 = "seafood"?

is the type stored in the actual record, or is the type an ID reference to a Type table externally?

When should the type be displayed?
does the type ONLY appear when there's a vertical change? ( see below )

example :

Monday
Seafood
crab platter
shimp and chips
Mexican
Burrito
Taco

Tuesday
Mexican
Burrito
Taco
fiesta platter
American
Hamburger and fries
Steak and potatoes


DreX
aKa - Robert

 

[amorous]

The table displays the weekly menu:

its something like this:


Monday:

Breakfast
_________

Eggs
Bacon

Lunch
________
Fish
Potatoes

Dinner
__________
Meatballs
Mexican Rice

Specials
__________

Reuben

and so on for the whole week

Now data in my cafemenu table is as follows:

MenuId|Menudate|Menuday|ItemId|typeID|ActiveInd
________________________________________________
1 |06/24/2004|Monday|123|2|1


IN the above data 123- Fish and i get this from table cafe which has list of items using the key itemid

and type id 2 means lunch and i get this value from Itemtype table

Thanks

-VJ

 

[DreXor]

hmmm
i suppose luck wouldn't have it that the type id's are in order, in correct order, for display? like :

1 breakfast
2 lunch
3 dinner
4 specials

?

DreX
aKa - Robert

 

[amorous]

yes they are in order

-VJ

 

[amorous]

Hi DreXor...

I did not understand what you meant by

"I suppose luck wouldn't have it that the type id's are in order, in correct order, for display? "

did u mean that we cannot display them in order...i dont mind with that as long as the items show under each category correctly..

Thanks

-VJ

 

[DreXor]

before i do anything else on this, you happen to have relationships set up already, or at least lookup values for the items/types? or do we need an all new SQL to translate the items/types in the RS?

DreX
aKa - Robert

 

[DreXor]

unfortunately my join/crosstab sql is extremely rusty...

anyone know how to put together the sql statement? i'm currently sitting on :

SQL = "select cafemenu.menudate,cafe.item,itemtype.type from cafemenu,cafe,itemtype where itemtype.typeid = cafemenu.typeid and cafe.itemid = cafemenu.itemid and cafemenu.menudate BETWEEN #" & founddate & "# AND #" & founddate+6 & "# Order by cafemenu.menudate asc,cafemenu.typeid asc,cafemenu.itemid asc"

basically just to translate the value of 143 from cafemenu.itemid to "eggs"

DreX
aKa - Robert

 

[amorous]

Hi Drexor...

Here is the query that i tried in SQL query analyzer..

select t1.menudate,
t2.Itemname, t3.Typename 
from cafemenu t1
INNER JOIN cafe t2 ON
t1.itemselected=t2.Itemnum
INNER JOIN foodtype t3 ON
t3.Typeid = t1.Menutypeid 
WHERE
t1.Menudate BETWEEN '06/20/2004' AND '06/26/2004' 
Order by t1.Menudate asc,
t1.Menutypeid asc,
t1.itemselected asc

Sample (dummy data)results:

2004-06-20 00:00:00Baked Potatoes w/Toppings Breakfast
2004-06-20 00:00:00Boiled Parsley Potato Lunch
2004-06-20 00:00:00Boiled Red Potatoes Specials
2004-06-20 00:00:00Cheese and Potato Pierogi Specials
2004-06-21 00:00:00Baked Potato Bar Breakfast
2004-06-21 00:00:00Healthy Choice TV Dinners Lunch
2004-06-21 00:00:00Soup of the Day Dinner
2004-06-21 00:00:00Taco and Burrito Bar Dinner
2004-06-22 00:00:00Carrots Breakfast
2004-06-22 00:00:00BBQ Chicken Pasta Breakfast
2004-06-22 00:00:00Biscuits Breakfast
2004-06-22 00:00:00Carrots Lunch
2004-06-22 00:00:00Cheesy Garlic Bread Lunch
2004-06-22 00:00:00Cream Turkey Lunch
2004-06-22 00:00:00Soup of the Day Dinner
2004-06-22 00:00:00Mashed Potatoes Dinner
2004-06-22 00:00:00Discount Salad Bar Specials
2004-06-23 00:00:00Beef/Bean Burrito Breakfast
2004-06-23 00:00:00Nacho Cheese Lunch
2004-06-23 00:00:00Deep Fried Fish Patty Dinner
2004-06-24 00:00:00Buffalo Chix Breakfast
2004-06-24 00:00:00Bruschetta Breakfast
2004-06-24 00:00:00Twice Baked Potato Lunch
2004-06-24 00:00:00Buffalo Chix Lunch
2004-06-24 00:00:00Corn Dinner
2004-06-25 00:00:00Bacon Cheddar Fries Breakfast
2004-06-25 00:00:00Bacon Cheddar Waffle Fries Lunch
2004-06-25 00:00:00Bacon TLC's Dinner
2004-06-26 00:00:00Zucchini Strips Specials
2004-06-26 00:00:00Veggie Garden Pasta Specials


As we can see they are ordered by date..

Thanks

-VJ

 

[amorous]

I meant that data is completely ordered by date and with in each date it is ordered by item name and item type...

Hope this eases our job ...

Thanks in advance

-VJ

 

[DreXor]

whoop ***, glad one of us is good with sql

i'll get right on it..


DreX
aKa - Robert

 

[amorous]

Yep... I am ok with SQL...Learnt a lot from this forum and guys like you...

Simultaneously I am also trying to generate the desired table...

but i will wait for your reply too...

Thanks

-VJ

 

[DreXor]

you're going to get a kick out of this, the nice part about "HEAVY" dynamics like this is instead of having to make a new, huge block of conditionals, a new logic map, etc, ALL i added was 2 lines to the existing code after updating the SQL statement

there's a remmed line with a comment in it that will reveal some of the ongoings beind the scenes, it's basically building it's own logic blocks/conditionals/variables to compare against for you.

but here you go, important note : the order by statements will make/break the layour, first is DATE, second is TYPE ( lunch/dinner ), third is ITEM ( fish, beef, etc ) you should have VERY little to change in the code, because i attempted to re-create the data structure for testing. although the date structure is set for msAccess, so you probably will want to change the #'s to single quotes in the SQL

and .. it's wicked fast

<%response.expires=0%>
<html>
<body>
<%
' moved some of your code down so the DB connection isn't open as long

dayarrays = split("Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday",",") ' this is for ease of dimming, reordering etc.
'part of the reasoning for this array will be more appearant later

for each dayarray in dayarrays
  execute(dayarray & " = Array()") ' dynamically dims the dayarray variable as the variable's content as an array
next

' establishing your connection, recordset etc
Dim founddate

founddate=FindDate(1,date(),1)

Set rsitem = Server.CreateObject("ADODB.Recordset")
Set ObjConn = Server.CreateObject("ADODB.Connection")
strconnect = "dsn=menu;"
' the strconnect value wasn't in there before, make sure you get it added/corrected
ObjConn.Open strConnect

'Sql = "SELECT * FROM cafemenu WHERE menudate BETWEEN #" & founddate & "# AND #" & founddate+6 & "# Order by menudate asc,typeid asc,itemid asc"
SQL = "SELECT cafemenu.menudate, foodtype.typename, cafe.itemname FROM foodtype INNER JOIN (cafemenu INNER JOIN cafe ON cafemenu.itemselected = cafe.itemnum) ON foodtype.typeid = cafemenu.menutypeid WHERE (((cafemenu.menudate) Between #" & FoundDate & "# And #" & FoundDate+6 & "#)) ORDER BY cafemenu.menudate, cafemenu.itemselected, cafemenu.menutypeid "
'response.write "|" & SQL & "|<br>"
' PS there was a typo in the SQL string last time, it's founddate not foundate
' Please note the addition of #'s to the dates instead of single quotes
' if you're using ACCESS you need #'s
' if you're using SQL you'll need the single quotes
Set rsitem = ObjConn.Execute(Sql)

' these next few sections will be where it might get a little more confusing on you
' what's happening here is the dynamic determination of what weekday a menudate is on
' then adding the RSitem("menuitem") values to the day array, RSitem("menuitem") being an array of recordset items, the dish name, or whatever you're looking to store
' using this way first of all cuts down on hoards of visible code, and lots of conditional statements
' like  determining if menudate is on a monday, then if/else add value to array, or proceed to determine if it's a tuesday etc
do while not rsitem.eof
  Execute("IF " & dayname(rsitem("menudate")) & "LastItem <> """ &  rsitem("itemname") & """ Then " & dayname(rsitem("menudate")) & " = AddToArray(" & dayname(rsitem("menudate")) & ",""<b>" & RSitem("itemname") & "</b>"")")
  Execute(dayname(rsitem("menudate")) & " = AddToArray(" & dayname(rsitem("menudate")) & ",""" & RSitem("typename") & """)")
  Execute(dayname(rsitem("menudate")) & "LastItem = """ &  rsitem("itemname") & """")
' Un Rem this next statement to get a better understanding of what's going on :)
'  response.write "|" & dayname(rsitem("menudate")) & "LastItem = """ &  rsitem("itemname") & """" & "|<br>"
  rsitem.movenext
Loop

'at this point we're done with the original data and using the arrays henceforth.
' so lets close and clear these buggers out 
set rsitem = nothing
objconn.close
set objconn = nothing

' this next cycle is similar to the one above, and in order to conditionally check the dynamics we'll need the exec statement again in order to plug in the array names via variable into function calls, namely ubound
' this is in order to determine the max number of records returned on any given day, that way you can cycle the output out properly

MaxCount = 0
for each dayarray in dayarrays
  Execute("if ubound(" & dayarray & ") > MaxCount Then MaxCount=Ubound(" & dayarray & ")")
next

' now that we have arrays populated with data, and know how big the biggest data array is, we can move on to putting it out on screen
' since there were changes to the handling of things, specifically the dimming of the arrays, the first slot is no longer empty, so we'll be starting the loops at (0) 

' Lets start making output 
response.write "<table border=1>" & vbcrlf
response.write "<tr>" & vbcrlf
'the vbcrlf is just for formatting in html view if you ever look at that

' writing out the column headers, the day names
for each dayarray in dayarrays
  response.Write "<td>" & dayarray & "</td>" & vbcrlf 
next

response.write "</tr>" & vbcrlf
Dim OutPut(0)
' Output will be a single position array to store the output for each row, just in case the data contained is larger than a string variable

'step thru all array posistions to the max
For i=0 to MaxCount
' step thru each day for each array posistion step
OutPut(0) = ""
  For each dayarray in dayarrays
    Execute("MaxRec = Ubound(" & dayarray & ")")
    If i <= MaxRec Then ' catching the arrays that are smaller than the biggest one, so you dont get a "subscript out of range" error
      Execute("TempVal = " & dayarray & "(" & i & ")")
      If TempVal <> "" Then
         OutPut(0) = OutPut(0) & "<td>" & TempVal & "</td>" & vbcrlf
      Else ' catches the empty ones
        OutPut(0) = OutPut(0) & "<td>&nbsp;</td>" & vbcrlf
      End If
    Else
      OutPut(0) = OutPut(0) & "<td>&nbsp;</td>" & vbcrlf
    End If
  Next
  Response.Write "<tr>" & vbcrlf & OutPut(0) & "</tr>" & vbcrlf ' this just adds the surrounding table row tags around this row of data
Next

response.write "</table>" & vbcrlf
%>
</body>
</html>
<%
' Functions sent to bottom to free visual space
Function FindDate(DayVal,StartDate,FindBackward) ' findbackward is true/false to indicate direction from start to find first matching day
    StartDate = CDate(StartDate)
    If Not IsNumeric(DayVal) Then Exit Function
    DayVal = CInt(DayVal)
    On Error Resume Next
    If FindBackward Then
        For i = 1 To 8
            If WeekDay(StartDate - i, vbSunday) = DayVal Then
                FindDate = StartDate - i
                Exit Function
            End If
        Next
    Else
        For i = 0 To 6
            If WeekDay(StartDate + i, vbSunday) = DayVal Then
                FindDate = StartDate + i
                Exit Function
            End If
        Next
    End If
    FindDate = "Error"
End Function

Function DayName(DateValue)
    If IsDate(DateValue) Then
        Select Case WeekDay(DateValue, vbMonday)
            Case 1
                DayName = "Monday"
            Case 2
                DayName = "Tuesday"
            Case 3
                DayName = "Wednesday"
            Case 4
                DayName = "Thursday"
            Case 5
                DayName = "Friday"
            Case 6
                DayName = "Saturday"
            Case 7
                DayName = "Sunday"
        End Select
    Else
        DayName = "Error"
    End If
End Function

' function snippet from Thread333-863153
Function AddToArray(OriginArray,AddValue)
  If IsArray(OriginArray) Then
   NewDim = Ubound(OriginArray)+1
   ReDim Preserve OriginArray(NewDim)
   OriginArray(NewDim) = AddValue
   AddToArray = OriginArray
  Else
    AddToArray = Array(OriginArray,AddValue)
  End If
End Function
%>

now if you want the breakfast/lunch/dinner rows to align... that's going to take some in depth thought

DreX
aKa - Robert

 

[DreXor]

in response to :

amorous (Programmer) Jun 24, 2004
Hi DreXor...

I did not understand what you meant by

"I suppose luck wouldn't have it that the type id's are in order, in correct order, for display? "

did u mean that we cannot display them in order...i dont mind with that as long as the items show under each category correctly..

Thanks

-VJ

the reason why this was important is when ordering out ( haha pun! ) the recordset the heirarchy of importance is date, type, item, if the types get out of order then your menu will be screwy, like alphabetical...

breakfast, brunch, dinner, lunch, special

which would look goofy
if you do add or think you will add something like brunch or dessert? as types, you'll need a third column added to the types table, which is an ordering sequence, just integers to tell the dataset what order they need to be in, you can also do this on the food items if you prefer , say, eggs before bacon or whatever... this comes in handy if persay food items begin to include "w/mashed potatoes" you cant have the with showing before the "steak" and you can be lazy on this ordering column, you can number from say 1 to 5 in each item type, like 1 is main dish ( first ) 2 is side dish, 3 is condiment, 4 is dessert or whatever..
and since the recordset advances by date, and fills the arrays "vertically" you can combine sequential records into one table cell by adding a condition set to denote the type ( meal ) is the same for the same day, to combine the items as one then to push the combined items into the array ( one of the execute statements ) after, they would already be in order if you have an ordering set on it: "steak(1) and potatoes(2) with green beans(3)"

hopefully that gets you squared away on this, if not please feel free to ask more questions, this is kind of indepth with "invisible" activities going on, which in turn can get quite confusing.


DreX
aKa - Robert

 

[DreXor]

*giggles* just looked at that code... it's almost all comments

DreX
aKa - Robert

 

[amorous]

Hi Drexor...Tried your above code..below is the result i got...

******************
1.the table started from Monday <- i wanted the table to start from Sunday
2.It the name of the item on the top and the name of the type below it. <--- Name of the type on top of the name of the item
3. In a single column if there are two items under category "Breakfast" then the word Breakfast is getting displayed under each item ....<- all items of same category should fall under only one type tag ie there should be "Breakfast" and if there are two item under breakfast category..they both should fall into this instead of Breafast being created twice for both items
******************
I am unable to copy paste the table here....is there any way that i can send you an email...can you provide me your email??

or is the sample table structure:

Mon |Tue |Wed |Thu |Fri |Sat |Sun
_________________________________________________
item 11|item12|item13|item14|item15|item16|item17
_________________________________________________
Breakf|Lunch|Breakf|Lunch|Dinner|Breakf|Breakfast
_________________________________________________
item 21|item22|item23|item24|item25|item26|item27
_________________________________________________
Lunch|Lunch|Breakf|Lunch|Specials|Breakf|Breakfast

Did you understand anything from the above table ??

-VJ

 

[amorous]

Ok mailed the output table to both your yahoo and sunflower accounts

thanks

-VJ

 

[amorous]

Hi DreXor..

Did you get my emails??

-Vj

 

[DreXor]

seems in the mix of things, when i replicated your datastructure i possibly got the fields/tables backwards
also remember key in instrumentation is the ordering of the records ( see above posts denoting importance of field orders )
also day the table starts on is based on the founddate in the finddate function ( see other thread for explaination, basically change the first 1 in the function call to a 7 to tell it to look for a sunday to start instead of a monday to start )


DreX
aKa - Robert