SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Zealot
    Join Date
    Aug 2003
    Location
    Singapore
    Posts
    105
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Altering table with php

    Code:
    ALTER TABLE tablename AUTO_INCREMENT=1
    How do I use the above code (in php) so that whenever I'm inserting data into the mysql table, the code will check for the next lowest number (so that the id will not be messed up by deletion)?

  2. #2
    SitePoint Enthusiast
    Join Date
    Dec 2004
    Location
    Johannesburg
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I first thought something like this would work:

    PHP Code:
    $result mysql_query('SELECT * FROM table WHERE 1');
    $num_rows mysql_num_rows($result);
    mysql_query('ALTER TABLE tablename AUTO_INCREMENT=$num_rows'); 
    But it won't because say there are 5 entries in the table:
    1
    2
    3
    4
    5

    And 3 is deleted then you have:
    1
    2
    4
    5

    Using the above code the next id value will be 5 which will then overwrite the existing entry. Any time a row is deleted its going to mess up the id. You will have to make a script that renumbers each id after a deletion.

    Can you explain why it is important for the id not to be messed up?

  3. #3
    SitePoint Member
    Join Date
    Dec 2004
    Location
    Gainesville, Fl
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the help, i can use this too!

  4. #4
    SitePoint Zealot
    Join Date
    Aug 2003
    Location
    Singapore
    Posts
    105
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by 9802008
    Can you explain why it is important for the id not to be messed up?
    I'm trying to display a certain number of posts (or you could call news entries?) from input. I'm using the id to display this, like WHERE id <= 10.

    But I've been thinking whether I could use a combination of date and time functions to generate numbers in ascending order, so I could set a limit to the number of entries to show. Is it possible?

    In the case of the id I could just specify to a certain limit, say 10. But since deletion screws the table then I have to rely on another formula.

  5. #5
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,633
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Check out the MySQL LIMIT command, it is exactly what you want.


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
  •