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]

When i changed the first 1 in the function call to 7 -- in the display table the saturday column got empty....

AND I want the display to be something like this:

Sunday|Monday|Tuesday|Wednesday|Thrusday
________________________________________
breakfast|lunch |Lunch |Dinner |Lunch
value11|value21|value31|value41|value51
Lunch |value22|Specials| |Dinner
value12| |value32| |value52
value13| | | |

Instead of
Sunday|Monday|Tuesday|Wednesday|Thrusday
________________________________________
breakfast|lunch |Lunch |Dinner |Lunch
value11|value21|value31|value41|value51
Lunch |value22|Specials| |Dinner
value12| |value32| |value52
[COLOR=red yellow]Lunch[/color] |
value13| | | |


see the lunch is repeated for every item..

I hope you got my point...

-VJ

 

[DreXor]

hmm might need a little patch in the code, either flipping Sunday to the beginning of the dayarray, or removing the day array and making a dynamic reference to the value of the dayname .. looking into it, and going by how things are showing up, i think i might have got the type table and the item table flip flopped in my datasample

take a look at

http://24.124.64.25/samplecode/displaydata.asp

and verify the data structure

meanwhile i will look at the day shifting ability of this

DreX
aKa - Robert

 

[amorous]

Ok here is what i found...

this is what you have:
********************************
Cafemenu
menudate
menutypeid ( references foodtypes.typeid )
itemselected ( references cafe.itemnum )

Cafe
itemnum
itemname(contains breakfast,dinner etc)

Foodtypes
typeid
typename ( contains eggs, fish, bacon etc )

****************************************************

I have cafe and Foodtypes interchanged....

i mean my itemname contains...eggs,fish,bacon and typenames contain....breakfast, dinner and so on..

_VJ

 

[DreXor]

yup, what i thought, i got flip flopped
let me re-update my dataset, and the sql and repost the updated code. which will be viewable at that link listed earlier

DreX
aKa - Robert

 

[amorous]

OK Sounds good to me...

I will keep an eye on that link..

-VJ

 

[amorous]

And one more thing DreXor...

In my old code...

i displayed the data in my table in the following format..

Response.Write "<a href='#' title="""&rscomments(i)&"""> "&rsitemname(i)&" </a>"

Now how do i incorporate this in your code....

rscomments(i) is the array which has comments/descrition of each item

and

rsitemname(i) is the array which has item names...

rsitemname(1) (item name) has corresponding rscomments(1)(description)

I hope you got what i meant...

-VJ

 

[DreXor]

i'll add it in .. it's part of the write to array value, like the headers have "<B>" added in there

what's the fieldname of the description? and what table is it in ( i'm learning )

DreX
aKa - Robert

 

[amorous]

field name ---> comments
tablename ---> cafemenu

cafemenu

MenuId|MenuDate|itemselected|menutypeid|comments
_____________________________________________


-VJ

 

[DreXor]

well here's the updated code, due to the tables being flipped, and due to the fieldnames that refer to the tables being flipped, i did alot of flipping, which in turn effects the code, couple field names got changed, and added the href titles in , updaet teh sql as necessary to point to the right description

( only posting the changed code )

'[b] this all gets killed [/b][COLOR=black red]
'dayarrays = split("Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday",",") ' this is for ease of dimming, reordering etc.
'this is now disabled, and built dynamically below
'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[/color]

' did a conversion so you can have the table rotate as teh days go on if you'd like, otherwise it's static based on the finddate call
'to rotate the days just make founddate = date() and rem the finddate call
' establishing your connection, recordset etc
Dim founddate
founddate=FindDate(1,date(),1) '[COLOR=black cyan][b]my mistake, the day array being static is what caused the problem before(layout) leave this as 1, 1 is sunday in the function, 7  is saturday, 2 is monday[/b][/color]
[b]
'double dutied this loop to dim the arrays, and to make your array set for column headers and array reference
For dateval = FoundDate to FoundDate+6
  execute(DayName(DateVal) & " = Array()")' dynamically dims the dayname as an array
  dayarrays = dayarrays & DayName(DateVal) & "," ' builds custom dayarray of day names based of your "start Date"
Next
'Strip the last comma off the dayarrays variable
dayarrays = left(dayarrays,len(dayarrays)-1)
'convert dayarrays to an array
dayarrays = split(dayarrays,",")
[/b]
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 cafemenu.menudate, cafe.itemname, cafe.description, foodtype.typename FROM (cafemenu INNER JOIN foodtype ON cafemenu.menutypeid = foodtype.typeid) INNER JOIN cafe ON cafemenu.itemselected = cafe.itemnum WHERE (((cafemenu.menudate) Between #" & FoundDate & "# And #" & FoundDate+6 & "#)) ORDER BY cafemenu.menudate, cafemenu.menutypeid, cafemenu.itemselected"
' 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")) & "LastType <> """ &  rsitem("typename") & """ Then " & dayname(rsitem("menudate")) & " = AddToArray(" & dayname(rsitem("menudate")) & ",""<b>" & RSitem("typename") & "</b>"")")
  Execute(dayname(rsitem("menudate")) & " = AddToArray(" & dayname(rsitem("menudate")) & ",""<a href='#' title=""""" & Server.HtmlEncode(rsitem("description")) & """"">" & RSitem("itemname") & "</a>"")")
  Execute(dayname(rsitem("menudate")) & "LastType = """ &  rsitem("typename") & """")
' 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

DreX
aKa - Robert

 

[amorous]

Just one word...Awesome....

Thanks DreXor... You are awesome...

Your code worked like charm...

One last change needed...some of items may not have the comments/description...so i need a msg displayed ....i did this earlier using the below code...

IF ISNULL(rsitem("comments")) OR rsitems("comments")="" Then

Response.Write "<a href='#' title='No description available.'>"&rsitem("Itemname")&"</a>"
Else
Response.Write "<a href='#' title='"&rsitem("comments")&"'> "&sun("Itemname")&" </a>"
End If

how do i incorporate this one in the new code...

Thanks a million


-VJ

 

[DreXor]

easiest fix is handle it partially outside the exec statements then replace out the rsitem("description") with a variable :

do while not rsitem.eof[COLOR=black cyan]
  If rsitem("description") <> "" then
    Description = rsitem("description") 
  Else
    Description = "No description available"
  End If[/color]
  Execute("IF " & dayname(rsitem("menudate")) & "LastType <> """ &  rsitem("typename") & """ Then " & dayname(rsitem("menudate")) & " = AddToArray(" & dayname(rsitem("menudate")) & ",""<b>" & RSitem("typename") & "</b>"")")
  Execute(dayname(rsitem("menudate")) & " = AddToArray(" & dayname(rsitem("menudate")) & ",""<a href='#' title=""""" & [COLOR=black cyan]Server.HtmlEncode(Description)[/color] & """"">" & RSitem("itemname") & "</a>"")")
  Execute(dayname(rsitem("menudate")) & "LastType = """ &  rsitem("typename") & """")
' 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

DreX
aKa - Robert

 

[amorous]

Yep that did the job...

Thanks a lot..

-VJ

 

[amorous]

Hi DreXor...

May be its getting little weird but one small requirement came up...

I want to be able to give an option to the user to click on any item displayed in the table ( besides the title )...and when the user clicks any item...I want a nice small pop up with a text box in it so that the user can enter his comments regarding the item and if possible email these comments to the cafeteria department head in the organization...

I mean something like this....

I see the item "Hot Dogs" item in the table under breakfast type. AS a user i want to enter my comments after having my breakfast... so if i can click the item or a small icon besides a pop up opens up with a text box on it where i can write "The hot dogs sucked" and when i click submit send email these comments or store the comments in the database...which ever is easier...

Any ideas??

-VJ

 

[amorous]

I am sure we can do something like this...

do while not rsitem.eof

itemid=rsitem("itemselected") <--- this gives us the Itemnum

i dont know how to put some Javascript pop up function that can has a text box for inputting comments related this item id so that

i can send this data ( itemid,comments) in a new table in the DB when the submit button on the pop up window is clicked...

Thanks for your suggestions...

-VJ

 

[DreXor]

<a href='#' title="""<CLIP>
change to
<a href=""""PopUpCommentPage.asp?item=" & rsitem("itemid") & """"" target='_blank' title="""<CLIP>

then it's just a metter of working out the necessary code for the popup window... OR make a javascript function that generates a sized, centered, whatever window for you that you only need to feed it the itemid
then you'd need :

<a href'#' onclick='mypopupfunction(" & rsitem("itemid") & ");' title="""<CLIP>

my request is that you look over the code, possibly response.write out things in the loops, various points to see what's going on, then you'll be understanding enough of the situation to future update the page without assistance from the forum and it'll make you feel pretty good about working on, and understanding something that was previously a large hurdle

challenges self achieved, are best recognised by those who accomplish them

DreX
aKa - Robert

 

[DreXor]

a popup function is extremely easy to find online, in the forum here as well too, though i might recommend search the java forum for it that way you can learn about those as well.

in the meanby :

<script language="javascript">
function mypopupfunction(itemidArg)
{
winvar = window.open('/popupcomments.asp?itemid='+itemidArg,'comments','menubar=no,scrollbars=no,resizable=yes,width=200,height=200');
}
</script>


DreX
aKa - Robert

 

[amorous]

Yes i totally agree with you...but i was just totally lost in those arrays and loops and unable to get hold of wats going on....

but i will try to understand and come up with some solution...

I really appreciate your assitance.. Thanks a lot...

-VJ

 

[DreXor]

well the code documents it, but perhaps this will help some more in the process of what's going on..

take all the contents of Execute(BLAH) pull out all the BLAH's and assign them to a variable just before the EXEC statements like :

Execute("if this or that then this or that buncha quotes, ya!")

and change it to this:
execstring = "if this or that then this or that buncha quotes, ya!"
response.write execstring & "<br>"
Execute(execstring)

this would need to be done for each exec command as it goes, and when you've seen what it does, all you have to do is rem the response.write lines

this way when the page runs, you can see what exec is doing for you also in one of the loops is a statement i said to unrem to get an idea of what's going on

if you take the output that this is generating, you'll notice it looks alot like what you would have had to type in order to get all this to work (the big blocks of if then elseif then else etc.. )

the tricky part is the dayname arrays, you'll see them being generated with the exec response writes

hopefully being able to see what it spits out will help this goofy code make a lot more sense

DreX
aKa - Robert

 

[amorous]

Thanks DreXor....

Most confusing part of the code was the Execute statements...

This is the first time i am using Execute statements...

-VJ

 

[DreXor]

hopefully the response.writes helped clear up the "invisible" things going on that were probably boggling you

DreX
aKa - Robert