Printing a drop down menu for a specific field.

[Nick the Gr33k]

Στις 27/10/2013 2:31 πμ, ο/η 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.
>
>
>
>
>
>
Ah foun it had to change in you code this line:
			key = host, city, useros, browser, ref

to this line:

			key = host, city, useros, browser

so 'ref' wouldnt be calculated in the unique combination key.

I'am still trying to understand the logic of your code and trying to 
create a history list column for the 'referrers'

I dont know how to write it though to produce the sam

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