Printing a drop down menu for a specific field.

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

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.

I hope i made it clear what i want to achieve.

 

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

Στις 21/10/2013 2:30 πμ, ο/η Îίκος Αλεξόπουλος έγÏαψε:

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.

I hope i made it clear what i want to achieve.

Any help would be appreciated.

 

[Steven D'Aprano]

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?


Step 2:

Scan the data, pulling out the record of the same unique visitor, and
collecting the dates for that record. For example, you might write code
like this:

# Untested, probably buggy.
visitors = []
records = []
for row in data:
host, city, useros, browser, ref, hits, lastvisit = row
if (host, ref) in visitors:
# Seen this visitor before. Add the date to that record.
record_no = visitors.index((host, ref))
else:
# New visitor, never seen before!
visitors.append((host, ref))
records.append([])
record_no = len(visitors)
records[record_no].append(lastvisit)


There may be more efficient ways to do the same thing, if you can think
of a way to associate a list of values with a visitor key.

However you do it, by the time you have finished, you'll have a list of
unique visitors, and a corresponding list containing the date of each of
their visits.

Step 3: When you go to build the table:

- identify which unique visitor this record represents
- look up the list of dates from that record
- build a <select></select> menu from that list of dates
- insert the menu into the table


and you're done.

 

[Mark Lawrence]

Any help would be appreciated.

It is considered polite to wait for at least 24 hours before pinging.
If waiting for this time isn't an option then paying for support is.

 

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

Στις 21/10/2013 9:58 πμ, ο/η Steven D'Aprano έγÏαψε:

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.