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.
