Printing a drop down menu for a specific field.

[Nick the Gr33k]

Στις 26/10/2013 9:33 μμ, ο/η rurpy at yahoo.com έγραψε:
> On 10/20/2013 05:30 PM, Νίκος Αλεξόπουλος wrote:
>> try:
>> 	cur.execute( '''SELECT host, city, useros, browser, ref, hits,
>> lastvisit FROM visitors WHERE counterID = (SELECT ID FROM counters WHERE
>> url = %s) ORDER BY lastvisit DESC''', page )
>> 	data = cur.fetchall()
>> 		
>> 	for row in data:
>> 		(host, city, useros, browser, ref, hits, lastvisit) = row
>> 		lastvisit = lastvisit.strftime('%A %e %b, %H:%M')
>> 			
>> 		print( "<tr>" )
>> 		for item in (host, city, useros, browser, ref, hits, lastvisit):
>> 			print( "<td><center><b><font color=white> %s </td>" % item )
>> except pymysql.ProgrammingError as e:
>> 	print( repr(e) )
>> ===========================================
>>
>> In the above code i print the record of the mysql table visitors in each
>> row like this:  http://superhost.gr/?show=log&page=index.html
>>
>> Now, i wish to write the same thing but when it comes to print the
>> 'lastvisit' field to display it in a <select></select> tag so all prior
>> visits for the same host appear in a drop down menu opposed to as i have
>> it now which i only print the datetime of just the latest visit of that
>> host and not all its visit datetimes.
>
> Perhaps something like this is what you are looking for?
>
>> try:
>> 	cur.execute( '''SELECT host, city, useros, browser, ref, hits,
>> lastvisit FROM visitors WHERE counterID = (SELECT ID FROM counters WHERE
>> url = %s) ORDER BY lastvisit DESC''', page )
>> 	data = cur.fetchall()
>>
>          newdata = coalesce( data )
>          for row in newdata:
>              (host, city, useros, browser, ref, hits, visits) = row
>                # Note that 'visits' is now a list of visit times.
>              print( "<tr>" )
>              for item in (host, city, useros, browser, ref, hits):
>                  print( "<td><center><b><font color=white> %s </td>" % item )
>              print( "<td><select>" )
>              for n, visit in enumerate (visits):
>                  visittime = visit.strftime('%A %e %b, %H:%M')
>                  if n == 0: op_selected = 'selected="selected"'
>                  else: op_selected = ''
>                  print( "<option %s>%s</option>" % (op_selected, visittime) )
>              print( "</select></td>" )
>              print( "</tr>" )
>
>      def coalesce (data):
>          '''Combine multiple data rows differing only in the 'hits' and
>          'visits' fields into a single row with 'visits' changed into a
>          list of the multiple visits values, and hits changed into the
>          sum of the multiple 'hits' values.  Order of rows is preserved
>          so that rows with most recent visits still come first.'''
>
>          newdata = []
>          seen = {}
>          for host, city, useros, browser, ref, hits, visit in data:
>              # Here you have to decide how to group the rows together.
>              # For example, if you have
>              #   178-20-236.static.cyta.gr | Europe/Athens | Windows | Explorer | Direct Hit | 1 | Παρασκευή 25 Οκτ, 20:48
>              #   178-20-236.static.cyta.gr | Europe/Athens | Windows | Explorer | Direct Hit | 3 | Παρασκευή 25 Οκτ, 20:06
>              # do you want those as one row on the html page, or two?
>              # If one, what value do you want to show for 'hits' (Επανάληψη)?
>              # "1", "3", "4"?
>              # I'll assume that you want an html row for every unique
>              # combination of (host, city, useros, browser) and that hits
>              # should be summed together.
>              key = host, city, useros, browser, ref
>              if key not in seen:
>                  newdata.append ([host, city, useros, browser, ref, hits, [visit]])
>                  seen[key] = len (newdata) - 1    # Save index (for 'newdata') of this row.
>              else:  # This row is a duplicate row with a different visit time.
>                  rowindex = seen[key]
>                  newdata[rowindex][5] += hits
>                  newdata[rowindex][6].append (visit)
>          return newdata
>
> Several caveats...
> The code above is untested, you'll probably have to fix some errors
> but hopefully it is clear enough.
> I only read this group intermittently these days so it you respond
> with question about the code, or need more help, it is likely I
> will not see your message so don't be surprised if you don't get an
> answer.
>
> Hope this is more helpful than the other answers you got.

Thank you very much Rurpy, i appreciate your help very much.
Even of you havent tested it your code runs flawlessly.

Only 1 side effect.
If visitor comes from a referrer link then the visit[] list doesn't not 
add its timestamp into it.

It only adds it in case of a direct hit when there is no referer present.






-- 
What is now proved was at first only imagined! & WebHost
<http://superhost.gr>