SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Zealot
    Join Date
    Apr 2002
    Posts
    136
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Problem regarding auto_increment (logic issue)

    In my project I wanted to display random messages across the top of the screen. I decided the best way to do this would be to make a table with rows id and content and simple build forms for adding/deleting/editing these two rows.

    First I do a SELECT COUNT to get the number of rows and then a rand with the results of the SELECT COUNT query and zero as it's arguments. Lastly I do a normal select-and-result query WHERE id='$therandomnumber.

    The problem arises when I want to delete one of the messages. It causes a gap in the ID numbers and, if the random number picks an id that was deleted then my next query will fail because it has a nonexistant id.

    My first workaround for this was to set set it up as a recursive function that would keep executing if the query returns nothing but then performance just decreases more and more as messages as deleted from the database and the function has execute more times to find a valid message.

    An addition to this I also made it possible to edit the messages id from the control panel so one could fill in the gaps where ids are missing and this worked as expected.

    The remaining problem is that, even when I edit quotes to fill in gaps the table resumes naming the ids where it left off instantly creating new gaps. Is there any way I can make mysql assign the auto_incrementing id based on the id of the previous row or would I have to take the auto_increment out of the id column and figure out some way to retrieve the latest id from the row already, increment that by one and feed it as the new id. The problem with that idea is that it appears to be what mysql already does with the auto_increment clause in the first place and it's a big pain in the *** for something that's probably obvious and right under my nose.

    Any ideas?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    ideas? always

    keep using autonumbers, allow gaps

    so far, so good, right?

    i like that because it's really simple, and you don't have excess code for unpleasant stuff like renumbering, resetting, editing entries you'd rather delete, and so on (this is not such a big deal if it's just one table, but imagine if there were other tables with foreign keys pointing back to this table? you are in renumbering hell)

    instead of SELECT COUNT() for the upper bound for the rand function, use SELECT MAX(id)

    generate your random number, call it R, and use it in the following query

    select min(id) as M
    from yourtable
    where id >= R

    then use this "lowest existing id greater than or equal to my random number" to pull the item

    select *
    from yourtable
    where id = M

    if the random number falls into a gap, this will simply retrieve the next highest id

    beauty, eh?

    the last two steps could be combined into one query, but you said it was mysql and mysql cannot handle subqueries


    rudy
    http://rudy.ca/


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
  •