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

[MuerteTG]

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

[mordred]

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.

[MuerteTG]

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!

[duniyadnd]

Just curious MuerteTG, what did you find to solve that problem? Because I am going through that same issue at the moment.

Duniyadnd

[MuerteTG]

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!