Printing a drop down menu for a specific field

This is the place for queries that don't fit in any of the other categories.

Printing a drop down menu for a specific field

Postby NikosGr » Fri Oct 25, 2013 11:43 am

Code: Select all
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.

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:

Code: Select all
# 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:

Code: Select all
=============================
# ~ 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.
NikosGr
 
Posts: 48
Joined: Thu Mar 28, 2013 6:31 pm
Location: Thessaloniki

Return to General Coding Help

Who is online

Users browsing this forum: Google [Bot], stranac and 4 guests