Php / MySql problem

[DEadLOck]

So sup,

Here is my issue... My login script of course.

I have a table like so called users...

|ID|USERNAME|PASSWORD|USERLEVEL|EMAIL|
|1 | farslasher | md5pass | 1 |a@a.com|
|2 | rawr | md5pass | 1 |a@a.com|
|3 | monkeym | md5pass | 1 |a@a.com|
|4 | pistachio | md5pass | 1 |a@a.com|
|5 | trianglema| md5pass | 1 |a@a.com|

My login script has an admin section which is able to delete a user, so for example,
I delete username = 'rawr'(Row 2). My new output is:

|ID|USERNAME|PASSWORD|USERLEVEL|EMAIL|
|1 | farslasher | md5pass | 1 |a@a.com|
|3 | monkeym | md5pass | 1 |a@a.com| // 2 was deleted
|4 | pistachio | md5pass | 1 |a@a.com|
|5 | trianglema| md5pass | 1 |a@a.com|

then at the same time I update the table with this code.

[code]    
$getid = "SELECT id FROM ".TBL_USERS." WHERE username = '$subuser'";
$result = $database->query($getid);
$num = mysql_numrows($result);
$i=0;
while ($i < $num) {
$id = mysql_result($result,$i,"id");
$i++;}
$adjust = "UPDATE users SET id=id-1 WHERE id > '$id'";
$database->query($adjust);
// Done with that Id number stuff
$q = "DELETE FROM ".TBL_USERS." WHERE username = '$subuser'";
$database->query($q);
[/code]

What the above codes to better explain is... $getid grabs the id of the user I selected to delete from table users (defined as TBL_USERS).

$database->query is the database information and mysql_query()

$num the if statement takes the query result from $getid and stores it to variable $id.

$adjust updates the tables users so if i delete user that has an id of 3... it makes all users after 3... starting at 4, -1 to the id so the order is still correct.

Then $q deletes the user. All is good

Here is the new output of the table:

|ID|USERNAME|PASSWORD|USERLEVEL|EMAIL|
|1 | farslasher | md5pass | 1 |a@a.com|
|2 | monkeym | md5pass | 1 |a@a.com|
|3 | pistachio | md5pass | 1 |a@a.com|
|4 | trianglema| md5pass | 1 |a@a.com|

The issue you ask, Now if someone goes to register, they are counted as id 6.. Meaning this is the result after a new person registers:

|ID|USERNAME|PASSWORD|USERLEVEL|EMAIL|
|1 | farslasher | md5pass | 1 |a@a.com|
|2 | monkeym | md5pass | 1 |a@a.com|
|3 | pistachio | md5pass | 1 |a@a.com|
|4 | trianglema| md5pass | 1 |a@a.com|
|6 | newregist |newmd5 | 1 |n@n.com|

the newly added row is counted as 6, since it auto increments.

What i wanted to happen is that the new field be 5... likely nothing happened out of the ordinary.
The reason by the way that i need this is i have a member list which shows the members of my site ordered by the user id, it would be odd if it was ordered as, 1,2,3,6,7,8,9,10,13,14,15,17. you get the point.

Here is the users table i created:

CREATE TABLE users (
id mediumint(10) NOT NULL auto_increment,
username varchar(30),
password varchar(32),
userid varchar(32),
userlevel tinyint(1) unsigned not null,
email varchar(100),
timestamp int(11) unsigned not null,
primary key (id,username)
);

much help would be appreciated on this. thanks.

[Ferenc]

[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]The reason by the way that i need this is i have a member list which shows the members of my site ordered by the user id, it would be odd if it was ordered as, 1,2,3,6,7,8,9,10,13,14,15,17. you get the point.[/quote]
This would be perfectly normal, since you would have deleted the 5th, 11th, 12th... members.

it would be a big pain to assign id's to the user, and may not be accurate (for sign up order.)

For the most part, the ID would be used for reference in queries and such. reorganizing them each time a user is deleted could be done, but unwise.

If you want to assign a order # (1,2,3,4,5,6,7,8,9)

[code]

$list_id = 1;

// your query;

//your loop{

  echo "$list_id $username;

  $list_id++;
// EOF loop }
[/code]

[DEadLOck]

I have a better way of doing this, took a while to think of..

[code]$q = "SELECT id FROM users ORDER BY id DESC LIMIT 1"; // grabs the last id in the table.. only grabs one id
$result = mysql_query($q);
echo $id = mysql_result($result,"id"); // variable $id now is the last id in the table
$a = "ALTER TABLE users auto_increment = '$id'"; // alters table to make the next inputed field the next id
$done = mysql_query($a);[/code]

This works if i change the $id to a number like so it works. and the next inputed field would be 501
$a = "ALTER TABLE users auto_increment = 500";



Any ideas on why my code didnt work?

[DEadLOck]

Solved it... seemed it wasnt to hard...

// Get the last id number from the table, and fix the auto_increment to correct the order
$fix = "ALTER TABLE ".TBL_USERS." AUTO_INCREMENT = 0";
$database->query($fix);
// Done Fixing auto_increment number

This will make the auto increment level 0 or the highest id number in the database.