Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New to the CF scene
    Join Date
    Sep 2002
    Location
    California
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unhappy AUTO_INCREMENT fields.... I NEED HELP!!!!

    Okay.... so I wanted to use an auto increment field in the guest book that I am writing. Now keep in mind that I am a beginer buit I thought it would go something like:



    $x = "1";

    $result = mysql_query("SELECT * FROM $tablename WHERE row_id=$x",$db);

    while ($result > ""){

    PRINT THE STUFF

    $x++;

    }

    The first thing is I DON'T EVEN KNOW IF THAT WILL WORK... but that's not the big problem..

    The actual problem is that the auto increment tables just continue adding even after a record has been deleted...

    FOR EXAMPLE:

    You have 3 records: Record 1, Record 2, Record 3...

    Now if you delete Record 2, it would just read Record 1, Record 3

    Or is you deleted Record 3 and added another it would say Record 1, Record 2, Record 4....

    How can I keep all records with the format 1 to whatever?

    I'D REALLY APPRECIATE SOME HELP!!!!!

    Thanks is advance,
    Ryan

  • #2
    Senior Coder
    Join Date
    Jun 2002
    Location
    frankfurt, german banana republic
    Posts
    1,848
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Simple answer: You don't want that.

    The AUTO_INCREMENT feature is explicitly designed to work this way. It guarantees that every new record gets a previously un-used id, a truely new one. If it were designed to update all data sets to ensure that the ID values are in sequences that have no leaps between them, you'd have to reassign ID values. That is not only error prone, but it may break a relation to a record set. For example, if this ID is stored in another table. After re-indexing, the ID would be lost or pointing to a different record set!

    Believe me, the fact that always new id values are created through AUTO_INCREMENT is *not* preceived as a problem. It only looks strange initially, but if you think it over against what I described above, you'll see its benefits.

  • #3
    New to the CF scene
    Join Date
    Sep 2002
    Location
    California
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thank ya!

    Yeah as soon as I posted the message and actually read what I was trying to do and felt silly. I was going about it all wrong. I went to www.php.net and read most of the MySQL functions and generated new code that just spits out whats in the database.

    Thanks for your help!

  • #4
    Regular Coder
    Join Date
    Jun 2002
    Location
    Depends on the time of year
    Posts
    478
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Just curious MuerteTG, what did you find to solve that problem? Because I am going through that same issue at the moment.

    Duniyadnd

  • #5
    New to the CF scene
    Join Date
    Sep 2002
    Location
    California
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Smile Here's the code...

    Here ya go:


    $link = mysql_connect("server", "username", "password")
    or die("Could not connect");
    mysql_select_db("database") or die("Could not select database");

    /* Performing SQL query */
    $query = "SELECT * FROM guestbook";
    $result = mysql_query($query) or die("Query failed");

    if(!$result || $result <= ""){

    print "no items";
    }
    else{
    /* Printing results in HTML */
    print "<table width='500' border='0' bgcolor='#000040' cellspacing='2' cellpadding='2'>\n";
    while ($row = mysql_fetch_array($result)) {
    print "\t<tr>\n";

    printf ("
    <td bgcolor='#000040' align='left' valign='center'>
    <font class='title'>%s</td><td bgcolor='#000040' align='right' valign='center'> <a class='titlelink' href='mailto:%s'>Email</a> <a class='titlelink' href='%s'>Web</a></font></td></tr><tr><td colspan='2' align='left' valign='center' bgcolor='#999999'>
    <font class='windowbody'><br>Message: %s<br><br></td>",$row["name"], $row["email"], $row["url"], $row["message"]);
    print "\t</tr>\n";
    }

    print "</table>";

    /* Free resultset */
    mysql_free_result($result);

    /* Closing connection */
    mysql_close($link);


    Hope you fix it!


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •