SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    two update statements causing diffs, on page refresh.

    Hi,

    I have two update statements.

    the first increments a col value by setting it to its value + 1. it works OK

    the second updates a record on duplicate key.

    trouble is, if the page is refreshed, the set statement increments the col value and the update on duplicate key doesn't. ( I know that's how they should work )

    But, if the page is refreshed, the value of the (not) updated record will stay at say '03' and the value+1 will keep going up with a page refresh each time it is refreshed.

    How do I facilitate the value+1 statement but prevent it from doing so siply by refreshing the page?

    here's my two statements

    Code MySQL:
    update dishes
        set display_sequence = display_sequence + 1
        where business_id = ?
        and content_category = 'Menus'
        and menu_name = 'Breakfast'
        and live_from = '2010-10-29'
        and course = 'Footer notes'
        and dish = 'Footer notes'
     
    insert into dishes 
      ( business_id, content_category, menu_name, live_from, course, dish, description, price, display_sequence)
       values ( ?, ?, ?, ?, ?, ?, ?, ?, ? )
       on duplicate key 
         update business_id = ?
       , content_category = ?
       , menu_name = ?  
       , live_from = ?
       , course = ?
       , dish = ?
       , description = ?
       , price = ?
       , display_sequence = ?

    Apologies for the ? placeholders but I don't think their presence will hamper an answer. They simple replace the values that would be inserted. it's a perl thing

    The two statement are performed inside a transaction

    I am adding a new dish to the menu with the on duplicate update statement and I am trying to increment the sequence number of the footer notes to ensure they always display last.

    bazz

  2. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,151
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    This is something you would need to do in perl, not MySQL. You could store a flag inside the session and expire it when the first transaction occurs. When someone refreshes page redirect them back to the beginning form if no session flag exists.

  3. #3
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ah great.

    Thank you oddz. I had hoped there was a mysql way but I'll do it in perl.

    bazz


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •