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

    Join Date
    Jun 2003
    Location
    Thessaloniki
    Posts
    1,270
    Rep Power
    12

    Question Inserting a record & Returning a colum's value


    column 'pin' is an 5-digit integer auto_increment primary key.

    What i want is to insert a new record or update the existing one, if 'pin' column's value exist. The following statement fails.

    Code:
    cursor.execute( '''INSERT INTO counters(page, hits) VALUES(%s, %s) RETURNING (pin) 
                            ON DUPLICATE KEY UPDATE hits = hits + 1''', (htmlpage, 1) )
    Also except from the inserting/updating job, i also need 'pin' colum's value to be extracted from the above statement so to be used to subsequent statement like the following. This is not happening, hence the following statement have no way to find 'pin' column's value which is to be used as a parameter to it.

    Code:
    cursor.execute( '''UPDATE visitors SET hits = hits + 1, useros = %s, browser = %s, date = %s WHERE pin = %s AND host = %s''', (useros, browser, date, pin, host))
    Can someone correct this please and explain?
    What is now proved was once only imagined!
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,027
    Rep Power
    4210
    RETURNING is not valid syntax

    what you want is the LAST_INSERT_ID() function
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  4. #3
  5. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2003
    Location
    Thessaloniki
    Posts
    1,270
    Rep Power
    12
    Originally Posted by r937
    RETURNING is not valid syntax

    what you want is the LAST_INSERT_ID() function
    May you please transform my 2 statements into correct syntax?
    I cannot make it work although i have tried.
    What is now proved was once only imagined!
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,027
    Rep Power
    4210
    Originally Posted by Nik
    May you please transform my 2 statements into correct syntax?
    sorry, i don't do cursor.execute
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  8. #5
  9. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2003
    Location
    Thessaloniki
    Posts
    1,270
    Rep Power
    12
    Originally Posted by r937
    sorry, i don't do cursor.execute
    Just, the MySQL part, i'll do the rest.
    What is now proved was once only imagined!
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,027
    Rep Power
    4210
    Originally Posted by Nik
    Just, the MySQL part, i'll do the rest.
    sure...
    Code:
    INSERT 
      INTO counters
         ( page
         , hits ) 
    VALUES
         ( %s
         , %s )
    ON DUPLICATE KEY 
    UPDATE hits = hits + 1
    in this query, you are inserting a row based on a page value and a hits value

    the ON DUPLICATE KEY part says that you just want to update the hits if the page already exists

    the table also has a pin column which you said is an auto_increment

    so the weird part is, you said "if the pin already exists" but that doesn't make sense, you aren't inserting the value of the pin column at all, so it will always be incremented

    so that means that in order for the ON DUPLICATE KEY portion to make sense, the page column has to have a UNIQUE key on it

    anyhow, to answer your question, after the INSERT statement, you could use LAST_INSERT_ID to return the pin value of the row just inserted

    except if the ON DUPLICATE KEY portion fired, in which case it isn't a new row, and a new pin didn't actually get assigned

    so the best way to retrieve the pin is with --
    Code:
    SELECT pin
      FROM counters
     WHERE page = '%s'
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  12. #7
  13. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2003
    Location
    Thessaloniki
    Posts
    1,270
    Rep Power
    12
    Will the following statement work without using the extra select statement you mentioned?

    Code:
    UPDATE visitors,counter 
    
    SET visitors.hits=visitors.hits+1, visitors.useros=%s, visitors.browser =%s, visitors.date=%s 
    
    WHERE visitors.pin=counter.pin AND counter.page = %s AND visitors.host=%s
    What is now proved was once only imagined!
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,027
    Rep Power
    4210
    Originally Posted by Nik
    Will the following statement work ...
    what happened when you tested it?

    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  16. #9
  17. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2003
    Location
    Thessaloniki
    Posts
    1,270
    Rep Power
    12
    Originally Posted by r937
    what happened when you tested it?

    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) )
    Something is definately wrong here, its logic is not correct.....
    What is now proved was once only imagined!
  18. #10
  19. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,027
    Rep Power
    4210
    let's start our detective work by examining the table

    could you run this please --
    Code:
    SHOW CREATE TABLE counters
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  20. #11
  21. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2003
    Location
    Thessaloniki
    Posts
    1,270
    Rep Power
    12
    Good morning, yes of course!

    Here is the detailed information of table counters after executing your MySQL command via phpMyAdmin on remote webhost.

    Code:
    CREATE TABLE `counters` (
     `id` smallint(5) NOT NULL AUTO_INCREMENT,
     `page` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
     `hits` smallint(5) NOT NULL,
     PRIMARY KEY (`id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=23157 DEFAULT CHARSET=latin1
    and here is the same for table visitors:

    Code:
    CREATE TABLE `visitors` (
     `id` smallint(5) DEFAULT NULL,
     `hits` smallint(5) DEFAULT NULL,
     `host` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
     `useros` varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
     `browser` varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
     `date` datetime DEFAULT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1
    What is now proved was once only imagined!
  22. #12
  23. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,027
    Rep Power
    4210
    thank you for that

    your INSERT using ON DUPLICATE KEY will never detect a duplicate key, so you need to revise your approach right there
    r937.com | rudy.ca
    please visit Simply SQL and buy my book

IMN logo majestic logo threadwatch logo seochat tools logo