#1
  1. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2003
    Location
    Thessaloniki
    Posts
    1,285
    Rep Power
    13

    Question Printing a drop down menu for a specific field.


    Code:
    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.
    What is now proved was once only imagined!
  2. #2
  3. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2003
    Location
    Thessaloniki
    Posts
    1,285
    Rep Power
    13

    Question


    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:
    # 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:
    =============================
    # ~ 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.
    What is now proved was once only imagined!
  4. #3
  5. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2003
    Location
    Thessaloniki
    Posts
    1,285
    Rep Power
    13
    Someone please?
    What is now proved was once only imagined!
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    16
    Rep Power
    0
    You'll have to group by the user's visiting domain rather than doing just a straight for loop.

    Basically, do an initial for loop to gather all the related visits, in like a dictionary.

    Then write a second for loop that will print out your selects.

    Does that make sense?
  8. #5
  9. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2003
    Location
    Thessaloniki
    Posts
    1,285
    Rep Power
    13
    Originally Posted by turvey
    You'll have to group by the user's visiting domain rather than doing just a straight for loop.

    Basically, do an initial for loop to gather all the related visits, in like a dictionary.

    Then write a second for loop that will print out your selects.

    Does that make sense?
    I'am not sure i follow, can you please provide code that shows this?
    What is now proved was once only imagined!
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    16
    Rep Power
    0
    Say your data is like the following:
    data = [('alice', 1), ('alice', 2), ('bob', 5), ('bob', 10), ('carrie', 3)]

    Where the first entry is your user and the second entry is a timestamp. Your data is structured basically like this, except I stripped the irrelevant details.

    Code:
    user_to_timestamps = {}
    
    # Gather all your users together.
    for user,timestamp in data:
        if user not in user_to_timestamp:
            user_to_timestamp[user] = []
        user_to_timestamp[user].append(timestamp)
    
    # You now have a data structure like this
    # {'alice': [1, 2], 'bob': [5, 10], 'carrie': [3]}
    
    for user, timestamps in user_to_timestamps.iteritems():
        print user
        for timestamp in timestamps:
            print "<select>%s</select>" % timestamp
    There. That's how you would do it. It shouldn't be much work to get your code into that form.
  12. #7
  13. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2003
    Location
    Thessaloniki
    Posts
    1,285
    Rep Power
    13
    Originally Posted by turvey
    Say your data is like the following:
    data = [('alice', 1), ('alice', 2), ('bob', 5), ('bob', 10), ('carrie', 3)]

    Where the first entry is your user and the second entry is a timestamp. Your data is structured basically like this, except I stripped the irrelevant details.

    Code:
    user_to_timestamps = {}
    
    # Gather all your users together.
    for user,timestamp in data:
        if user not in user_to_timestamp:
            user_to_timestamp[user] = []
        user_to_timestamp[user].append(timestamp)
    
    # You now have a data structure like this
    # {'alice': [1, 2], 'bob': [5, 10], 'carrie': [3]}
    
    for user, timestamps in user_to_timestamps.iteritems():
        print user
        for timestamp in timestamps:
            print "<select>%s</select>" % timestamp
    There. That's how you would do it. It shouldn't be much work to get your code into that form.
    I'am sorry but i still cannot transform my code:

    Code:
    	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) )
    to the solution you presented
    I just dont know how to write it.
    What is now proved was once only imagined!
  14. #8
  15. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2003
    Location
    Thessaloniki
    Posts
    1,285
    Rep Power
    13
    Help me write it please, i dont know exaclty how it works.
    What is now proved was once only imagined!
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Oct 2009
    Location
    Nebraska, USA
    Posts
    875
    Rep Power
    276
    Originally Posted by Nik
    Help me write it please, i dont know exaclty how it works.
    turvey gave you some example code to "help" you with your problem.....IF you want someone to write the code for you and allow you to just copy/paste it for your usage, then, there is a "Hire a Programmer" forum where you can hire someone to write the code for you.
  18. #10
  19. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2003
    Location
    Thessaloniki
    Posts
    1,285
    Rep Power
    13
    If i knew how to write it myseld i wouldn;t be asking, i tried but i just cant adjust it to my needs.
    What is now proved was once only imagined!
  20. #11
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    16
    Rep Power
    0

    Talking


    If you want, I do consulting at a reasonable fee.
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Oct 2009
    Location
    Nebraska, USA
    Posts
    875
    Rep Power
    276
    cross-posted HERE
  24. #13
  25. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2003
    Location
    Thessaloniki
    Posts
    1,285
    Rep Power
    13
    Code:
    	def coalesce (data):
    		newdata = []
    		seen = {}
    		for host, city, useros, browser, ref, hits, visit in data:
    			# Here i have to decide how to group the rows together.
    			# I 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
    			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
    
    		
    	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>" )
    Only 1 side effect.
    If visitor comes from a referrer link then the visit[] list doesn't not add its timestamp into it.

    It only adds it in case of a direct hit when there is no referer present.
    What is now proved was once only imagined!
  26. #14
  27. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    16
    Rep Power
    0
    Seems like Nik is just asking on multiple threads and then copying/pasting the bits of solution.

    I have no more interest in helping. It doesn't even look like he's trying to learn Python.
  28. #15
  29. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2003
    Location
    Thessaloniki
    Posts
    1,285
    Rep Power
    13
    Iam trying.

    Ah foun it had to change in you code this line:
    key = host, city, useros, browser, ref

    to this line:

    key = host, city, useros, browser

    so 'ref' wouldnt be calculated in the unique combination key.

    I'am still trying to understand the logic of your code and trying to create a history list column for the 'referrers'

    I dont know how to write it though to produce the same output for referrers.

    The bast i came up with is:

    Code:
    def coalesce( data ):
    		newdata = []
    		seen = {}
    		for host, city, useros, browser, ref, hits, visit in data:
    			# Here i have to decide how to group the rows together.
    			# I 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
    			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][4].append( ref )
    				newdata[rowindex][5] += hits
    				newdata[rowindex][6].append( visit )
    		return newdata
    
    		
    	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, refs, hits, visits) = row
    		# Note that 'ref' & 'visits' are now lists of visit times.
    		
    		print( "<tr>" )
    		for item in (host, city, useros, browser):
    			print( "<td><center><b><font color=white> %s </td>" % item )
    			
    		print( "<td><select>" )
    		for n, ref in enumerate( refs ):
    			if n == 0:
    				op_selected = 'selected="selected"'
    			else:
    				op_selected = ''
    		print( "<option %s>%s</option>" % (op_selected, ref) )
    		print( "</select></td>" )
    
    		for item in (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>" )
    But this doesnt work correctly for refs and also doenst not print for some reason the hits and visit colums.
    What is now proved was once only imagined!

IMN logo majestic logo threadwatch logo seochat tools logo