Printing a drop down menu for a specific field.

[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.