PHP/MYSQL Problems

[The-Last-Escape]

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);

 

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.

[cmgmyr]

Why would you want to change a user's unique iD?

 

Another way you can do this is to load the query into an array, clear the table, and re-enter the data into the clean table.

[The-Last-Escape]

Allright Ill give it ago

[The-Last-Escape]

Thanks 

[cmgmyr]

no problem