mysq update table

[dev.vini]

help me to autodecrement a field in a table when delete any entry from table
example:
!--------------------------
!id name userno
!--------------------------
! 1 harry 101
!--------------------------
! 2 abc 102
!--------------------------
! 3 xyz 103
!--------------------------
! 4 xyz 104
!-------------------------
if we delete 3rd entry from table than 104 will automaticaly decrement in 103 how is this possible pls help me

[hielo]

First make a DELETE query. It it succeeds, then make an UPDATE query - ex:

$id=3;
mysql_connect("localhost","username","password") or die(mysql_error());
mysql_select_db("dbname") or die(mysql_error());
mysql_query("DELETE FROM tableName WHERE `id`=$id") or die(mysql_error());
mysql_query("UPDATE tableName SET `userno`=`userno`-1 WHERE `id` > $id") or die(mysql_error());

[pritaeas]

Already answered: http://www.daniweb.com/forums/thread310054.html

[dev.vini]

this one is diffrent pls chek it manualy nd reply i am waiting pls hurry

[pritaeas]

There is no columnno field, do you mean id ? If so, what type of field is it ? If it is not a PK then the other thread applies. If it is a PK then I wouldn't recommend decrementing it. If it is an autoinc field then it is not possible.

[dev.vini]

!---+---------------+--------+
!id ! name | userno |
!---+---------------+--------+
! 1 | harry | 101 |
!---+---------------+--------+
! 2 | abc | 102 |
!---+---------------+--------
! 3 | xyz | 103
!-------------------+-------
! 4 | xyz | 104
!-------------------+------
sorry its not column no its a userno field if we delete multiple entries from table in random than how will the userno come in sequence like 1..2..3.. so on

[pritaeas]

See the other thread...

[peter_budo]

Threads merged, double removed!