SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Enthusiast
    Join Date
    Feb 2005
    Posts
    94
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Last entry recall

    I am using

    Code:
    $link = mysql_connect('*login info*');
    if (!$link) {
       die('Could not connect: ' . mysql_error());
    }
    mysql_select_db('database');
    
    mysql_query("INSERT INTO db (phrase) values ('test')");
    printf("Last inserted record has id %d\n", mysql_insert_id());
    
    mysql_query("DELETE FROM db WHERE id=" .mysql_insert_id() ."")
    to pull the last id number out of my database, and using the last line to delete the test value it inserts. That works fine, and the number that it pulls is correct the first time, but if I refresh it to make sure that it is still running correctly, the number will increase, yet when I check the database there are no new values. Any ideas on what is going on?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by j0hnb
    ... but if I refresh it to make sure that it is still running correctly, the number will increase, yet when I check the database there are no new values. Any ideas on what is going on?
    mysql has no concept of "refresh"

    what do you mean by "there are no new values"? it looks like you always delete what you just inserted!!!
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Feb 2005
    Posts
    94
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The code I found to tell me the last value inserted a value, so I told it to delete the value after that. That's why that is in there.

    I don't want to insert a value I just want to know the value of the last item placed in the table so I can use it as part of the script I am working on.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by j0hnb
    I don't want to insert a value I just want to know the value of the last item placed in the table so I can use it as part of the script I am working on.
    see, that doesn't make sense to me, but maybe it's just me

    when "the last item placed in the table" is actually placed in the table, that's when you grab the value

    why would you wait until some other time?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by j0hnb
    but if I refresh it to make sure that it is still running correctly, the number will increase
    yes, the value is incremented because the auto_increment value is stored separately from the column. that way if you insert something and then delete it, the number is not reused. for example, if you insert rows and get id's 2, 3, and 4, then delete id's 34 and 4, then next id assigned on insert will be 5.
    Quote Originally Posted by j0hnb
    ...I just want to know the value of the last item placed in the table so I can use it as part of the script I am working on
    then why not use last_insert_id() when inserting that row, instead of trying to find it after the fact?

  6. #6
    SitePoint Enthusiast
    Join Date
    Feb 2005
    Posts
    94
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Alright let me try to explain this a little better so you can understand where I am at. I could be completely wrong with the way I am trying to build this, but we'll see.

    The short of it is I have a php script that calculated codes in order. Since this is a very long program and doesn't have to be run all at once. I was making it so that, if it is stoped, it can be picked up at a later time, by jumping to the end, pulling the last value and starting where it left off. I could simple tell php to export this data to a different table, or even a file and run it from that, I was just attempting to make it all in one set. So since this is ment to be run multiple times, and pick it's self up where it left off, I was thinking of making it in this manner.

    I really just started laying this out and programming it yesterday, so I have no doubt that I will be changing the way that it is run. As I am new to MySQL, I understand all the concepts and how it works, but with the specifics and the best way to do certian things, I have a lot to go.

    The more I think about it I think it would be better to just store the last value in another table, but what happens if the script is cut off before it finishes and is then unable to write the stop location. Well what ya think? I hope that clears up my general idea.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    the whole point about an auto_increment is that it's a primary key, which means that each value is distinct

    it doesn't matter what the actual values are

    if you are relying on the actual values for something, then perhaps you should not be using auto_increment for whatever purpose you have
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Enthusiast
    Join Date
    Feb 2005
    Posts
    94
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The auto_increment is more for keeping track of the which number. Each numers is distinct since each entry is a hash value of a different phrase.


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
  •