SitePoint Sponsor

User Tag List

Results 1 to 19 of 19
  1. #1
    SitePoint Zealot Jppr's Avatar
    Join Date
    Dec 2000
    Location
    Belgium
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    I work with MySql, and I have a field auID, which has option auto_increment. This works ok, when I add new entries.
    But when I delete an entry (say nr. 4), and then add a new one MySql starts counting from 5, instead of 4 again.

    What do I have to do in order to let MySql count right?
    Thanks

    Thomas

  2. #2
    midnight coder
    Join Date
    Dec 2000
    Location
    The flat edge of the world
    Posts
    838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That's suppose to happen, think of the IDs as "disposible", use it once, and throw it away.

    The reason being, say in a news submittion script, author Bob has the ID 2, and in another table, the news content links to ID 2, which tells the news that it was posted by Bob, ok?

    Well if you remove Bob from the db, and added a new user Pete, and like you said, was given the ID 2 again, does that mean the all the news previously submitted by Bob, now belongs to Pete?

  3. #3
    SitePoint Zealot Jppr's Avatar
    Join Date
    Dec 2000
    Location
    Belgium
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the quick reply.

    Now suppose that I know for sure that Bob don't has any articles to his name yet, do I need to manually change the values for auID ?

    other question: in the management system I made with PHP for the authors, I need something to turn a "" (empty) value to a NULL value, so that MySql can throw an error message if that field is required.
    Any ideas?

    Thomas

  4. #4
    midnight coder
    Join Date
    Dec 2000
    Location
    The flat edge of the world
    Posts
    838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you really want to change the auID, I guess you would have to do it manually, using the update function.

    Code:
    UPDATE [dbname] SET auID='4' WHERE name='Pete'
    If a field is required, you set that field to NOT NULL, so if that field is empty MySQL will complain.

    If it's in a form, you can verify that the field has something in it before submitting.

    Code:
    if ($submit)
    {
       if (!$fieldname)
       {
       echo "<p>Hey, you skipped a few fields, don't be so lazy</p>"
       }
    
    else
       {
       // Process Form
       }
    }

  5. #5
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    an auto_increment field is and always should be a simple unique number that corresponds to the record, it shouldn't matter if you skip some id in the sequence. It SHOULD NOT be used as a sequential number of records for counting the total number of records there are functions for that like count(*).
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  6. #6
    SitePoint Zealot Jppr's Avatar
    Join Date
    Dec 2000
    Location
    Belgium
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is there no special function to change empty "" to unknown NULL then (in php)

    Or $null or something?

  7. #7
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Not sure I follow you your php script should dictate was is required or not. You should be doing your error checking you script not letting mysql give the error if smething is required.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  8. #8
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    I think what Jppr wants is for his PHP script to insert a NULL entry when the user doesn't enter anything in the text field (producing a variable with value "").

    Here's how I do it:

    Code:
    // Add quotes or set to NULL.
    $var = ($var == "") ? "NULL" : "'$var'";
    
    ...
    
    // SQL Query
    $sql = "... SET ColName=$var, ...";
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  9. #9
    AdSpeed.com Son Nguyen's Avatar
    Join Date
    Aug 2000
    Location
    Silicon Valley
    Posts
    2,241
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just a question about this auto_increment:
    what if the field keeps increasing and doesn't fit into that field's type? (say TINYINT for some index over 256)
    Would the number be cut off? And would it ruin what the auto_increment field should be?
    - Son Nguyen
    AdSpeed.com - Ad Serving and Ad Management Made Easy

  10. #10
    midnight coder
    Join Date
    Dec 2000
    Location
    The flat edge of the world
    Posts
    838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If the number doesn't fit, MySQL automatically change the type to the next one up, eg, INT.

    Same applies the other way round, say you set a type to be VARCHAR(20), and all your data are under 4 characters, it'll automatically change to CHAR.

  11. #11
    AdSpeed.com Son Nguyen's Avatar
    Join Date
    Aug 2000
    Location
    Silicon Valley
    Posts
    2,241
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't think it will automatically push to the next bigger type. Since when I assign a field INT, then I put a big number (out of its range), it got cut off!
    But with CHAR and VARCHAR, I agree that how it should work!
    - Son Nguyen
    AdSpeed.com - Ad Serving and Ad Management Made Easy

  12. #12
    SitePoint Zealot Jppr's Avatar
    Join Date
    Dec 2000
    Location
    Belgium
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for all of your replies.

    I included a security for empty fields now.

    About the auto_increment issue: I can manually set the id to something, but what if I want to reset the auto_increment value.

    I found in the manual that myisamchk has an option set-auto-increment[=value] but I'm not sure how this works.
    Alternatively, is that auto_increment value stored somewhere in a db or something? (in "mysql" maybe?)

    Thomas

  13. #13
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Why would you want to reset the auto increment value? For a good database design, no useful information should be stored in the autoincrementing column. It should serve only as a unique identifier for each item in the table. Thus, it shouldn't matter what the actual value is, as long as it's unique.
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  14. #14
    AdSpeed.com Son Nguyen's Avatar
    Join Date
    Aug 2000
    Location
    Silicon Valley
    Posts
    2,241
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Kevin,
    As you said, does that column help with indexing? Not really in my understanding, since you said that field shouldn't contain any special data we want at all, so when fetching data, we probably don't need it.

    So what are the advantages to have a unique ID for every row?
    Thanks
    - Son Nguyen
    AdSpeed.com - Ad Serving and Ad Management Made Easy

  15. #15
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    First of all, it provides a unique identifier for each row (avoids problems like two people named "John Smith" being indistinguishable in your database). The ID should also be used when associating that row with related information in other tables. This is all covered in the chapter on database design in my PHP/MySQL tutorial.
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  16. #16
    SitePoint Zealot Jppr's Avatar
    Join Date
    Dec 2000
    Location
    Belgium
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well,

    I did read your tutorials Kevin, and I did learn a lot of it.

    Indeed, the column with auto_increment is used as identifier for articles, authors and categories. It's just a number.
    As you know I'm building a management system to manage all the articles etc. But to check if everything works the way I want, I inserted some test values, and afterwards deleted them.

    Now the management system works, I insert for example two authors, but they already start with id 9 en 10. That's what I'm bothered about, because I used tinyint, and it can have 255 in unsigned mode. Suppose I delete a lot of entries then the id's will rise high and I would have to change the field type.

    So I wanted something to reset it.

    An option I found is to use
    shell> mysql < tablebackup.sql

    and inside tablebackup the queries to drop the table, and then re-create and re-populate it. But I think that's a bit drastically, because suppose I forgot to backup before I dropped the table...

    Thomas

  17. #17
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Indeed, if MySQL gets to the highest allowable value in a column you won't be able to insert any more items in the table (MySQL will complain of duplicate values).

    My first recommendation would be to change the column type. If you're worried about running out of IDs, then you're definitely using too small a column type. TINYINT UNSIGNED (256 keys) requires 1 byte of storage, SMALLINT UNSIGNED (65536 keys) requires 2 bytes of storage.

    Are you really going to waste time worrying about this problem for the sake of 1 byte per author?

    If you can bare 4 bytes of storage, you can use INT UNSIGNED and have 4294967296 keys!

    Changing the column type non-destructively is easy to do with an ALTER TABLE command -- let me know if you need help with this.

    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  18. #18
    midnight coder
    Join Date
    Dec 2000
    Location
    The flat edge of the world
    Posts
    838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If the ID was real money, then I'll be worry.

    But it's only a number, it's disposible, it doesn't even get display on the webpage, so you really shouldn't care if a few numbers doesn't get use. You're not wasting anything.

    Just alter that column to SMALLINT, or INT, and you'll be set.

  19. #19
    SitePoint Zealot Jppr's Avatar
    Join Date
    Dec 2000
    Location
    Belgium
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay, I'll take that extra byte then

    Thanks for the replies everybody.

    Cheers,
    Thomas


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
  •