SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,106
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Updating record n rows before last inserted id

    mySql + Coldfusion

    I use LAST_INSERT_ID() AS newID to determine inserted record:

    Code CFM:
    <cfquery name="getLastID" datasource="#arguments.dsn#">
        SELECT LAST_INSERT_ID() AS newID
        FROM products
    </cfquery>
    <cfset session.product = getLastID.newID />

    How do I update the id of the record, which is lets say 24 rows before this record? I tried it with newID - 24 but that will only work if that record is indeed in the database (if the record was for some reason deleted nothing is updated) For example if the last inserted id is 100, id 76 should be updated but if this id doesn't exist the one before that should be updated

  2. #2
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,820
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Rows are not ordered and there is no relationship between records based on their generated id (others may have started inserting 50 other records just before you dod and then the record 24 earlier would be in the middle of that group that you didn't even see were there when you started).

    Can you supply more information on just what it is that you want to update another record for and what the relationship is between the records.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  3. #3
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,106
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Stephen thanks for the reply This is a CMS (for a record store) where just one record at a time is added to the database managed by just one person at a time. The 24 records mentioned are marked as new arrivals isNew=1 (this is the default number of records the owner want to display as new arrivals) They are, as said, flagged as new arrival and ordered by date_added DESC. When a new record is added to the database, the isNew status for the record 24 rows before this new inserted record should be set to 0 isNew=0 no matter the id's.

    So for simplicity. Lets say the below diagram is the product table. Number 10 is the last inserted id and I would like to update the record 3 rows before this last inserted id, which is in this case id 3

    ID

    1
    2
    3 (id to be updated)
    5
    6
    10 (last_insert_id)
    How would I do so

  4. #4
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,151
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    The most robust solution would be to wrap three queries in a transaction.

    The first query would locate the 24th item.
    The second query would insert the new item
    The third unmark the the result as new from the first

    The other option which is just as robust would be to use a trigger. Whenever a new record is inserted check to see if it is a new arrival. If it is a new arrival make the 25th item not new.

    Though, why use a flag to determine what items are new? Couldn't you just display the most recent 24 items based on when they were released or something, rather than using a flag? Are you using a flag because the client wants to be able to control their "new releases"?

  5. #5
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,106
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Oddz. I Indeed could use just the last 24 records added, was it not that the results page, is a combined page, both for normal records as new arrivals. The isNew status is part of the navigation and it is not about the release date but the arrival date. A very old record can be re-released

    I like both your suggestions, but would like to go for the first option, since I'm kind of familiar with <cftransaction>. Could you give a very basic example how the transaction with those 3 queries would look like? (specially how do I get the 24th record DESC)

    Thank you in advance

  6. #6
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,106
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I wasn't thinking clearly.
    Code MySQL:
    <cfquery name="getLastID" datasource="#application.dsn#">
        SELECT *
        FROM products
        WHERE category_id = 2
        ORDER BY product_id DESC
        LIMIT 24, 1
    </cfquery>

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    fyi, LIMIT 24,1 gives you the 25th row
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,106
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks rudi, as usual you're right have corrected it already


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
  •