mysql solution

[Ferrous Cranus]

Τη Πέμπτη, 24 Ιανουαρίου 2013 5:39:54 μ.μ. UTC+2, ο χρήστης Lele Gaifax έγραψε:

> UPDATE visitors
> 
>    SET visitors.hits=visitors.hits+1, 
> 
>        visitors.useros=%s,
> 
>        visitors.browser=%s, 
> 
>        visitors.date=%s
> 
> WHERE visitors.pin=(SELECT counters.pin
> 
>                     FROM counters
> 
>                     WHERE counters.page=%s)
> 
>   AND visitors.host=%s
> 
> 
> 
> But I wonder about the "logic" here: why are you storing the "useros",
> 
> "browser" and "date" in a table where the primary key seems to be
> 
> ("pin", "host")? I mean, what happens if a user visits the same page
> 
> twice, first with Firefox and then with Chrome?


it doesn't work, it creates new entries on every webpage visit instead of updating.

this is what i have up until now:

[code]
	# insert new page record in table counters or update it if already exists
	try:
		cursor.execute( '''INSERT INTO counters(page, hits) VALUES(%s, %s)
								ON DUPLICATE KEY UPDATE hits = hits + 1''', (htmlpage, 1) )
	except MySQLdb.Error, e:
		print ( "Error %d: %s" % (e.args[0], e.args[1]) )
		
	# update existing visitor record if same pin and same host found
	try:
		cursor.execute('''UPDATE visitors SET hits=hits+1, useros=%s, browser=%s, date=%s 
                                                  WHERE id=(SELECT id FROM counters WHERE page=%s) AND host=%s''',
                                                                            (useros, browser, date, htmlpage, host))
	except MySQLdb.Error, e:
		print ( "Error %d: %s" % (e.args[0], e.args[1]) )
	
	# insert new visitor record if above update did not affect a row
	if cursor.rowcount == 0:
		cursor.execute( '''INSERT INTO visitors(hits, host, useros, browser, date) VALUES(%s, %s, %s, %s, %s)''', (1, host, useros, browser, date) )
[/code]

Something is definately wrong here, its logic is not correct.....