Printing a drop down menu for a specific field.

[Νίκος Αλεξόπουλος]

Στις 21/10/2013 9:58 πμ, ο/η Steven D'Aprano έγραψε:
> On Mon, 21 Oct 2013 09:07:17 +0300, Νίκος Αλεξόπουλος wrote:
>
>>>       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
>>>               )
> [...]
>>> 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.
>>>
>>> I hope i made it clear what i want to achieve.
>>
>>
>> Any help would be appreciated.
>
>
> Step 1:
>
> Decide what counts as "the same visitor". Is it...?
>
> - anyone with the same IP address?
> - anyone with the same IP address and the same useros?
> - anyone with the same IP address, the same useros, and the same browser?
> - something else?

First let me show you the database insertion to start form there:

The definition of the same visitor in my case is basically a combination 
of they page the visitor tries to visit along with its hostname. At 
MySQL's definition iam implementing this as:

   unique index (counterID, host)


Up until now i was updating the record of the same visitor as follows:

============================
# if first time visitor on this page, create new record, if visitor 
exists then update record
cur.execute('''INSERT INTO visitors (counterID, host, city, useros, 
browser, ref, lastvisit) VALUES (%s, %s, %s, %s, %s, %s, %s)
		ON DUPLICATE KEY UPDATE city = %s, useros = %s, browser = %s, ref = 
%s, hits = hits + 1, lastvisit = %s''',
	(cID, host, city, useros, browser, ref, lastvisit, city, useros, 
browser, ref, lastvisit) )
=============================


Since now i have decided to have more records for the same visitor if 
i'm gonna save its history of visits, i'm thinking that i can no longer 
update the same unique visitor record but save many records related to 
the same visitor. so i use this:


=============================
# ~ DATABASE INSERTS ~
=============================
try:
	# if first time for webpage; create new record( primary key is 
automatic, hit is defaulted ), if page exists then update record
	cur.execute('''INSERT INTO counters (url) VALUES (%s) ON DUPLICATE KEY 
UPDATE hits = hits + 1''', page )
	# get the primary key value of the new added record
	cID = cur.lastrowid

	# if first time visitor on this page, create new record, if visitor 
exists then update record
	cur.execute('''INSERT INTO visitors (counterID, host, city, useros, 
browser, ref, lastvisit) VALUES (%s, %s, %s, %s, %s, %s, %s)''',
					   (cID, host, city, useros, browser, ref, lastvisit) )

	con.commit()
except pymysql.ProgrammingError as e:
	print( repr(e) )
	con.rollback()
=============================


Are we good up until this point as it concerns the database insertions?
If we are then we can discuss how to present the saved data.