如何在我们有重复记录的MYSQL表中自动递增字段

Question

I have 2 tables:

Table1:**  NAME (id, Slno, name, phone, city, state, user, rid) 
Table2:**  NAMES2 (id, name, phone, city)

In Table1 there are duplicate records, so In Table1 the 'rid' field is capturing Table2.id, to have the difference in data.

I want to update 'Slno' field with increment starting from 1 to n for every login user (fieldname is user)

I have mysql and php file where i have the below statement which is not working.

my PHP file:

$usr=$_SESSION['user']; 
$j=0; 

mysql_query("UPDATE NAME, NAMES2 SET NAME.SLno='$j++' WHERE NAME.rid=NAMES2.id && NAME.user='$usr'");

Answer 1

If i have correctly understand, you need somthing like that:

  $j=0;
  $query=mysql_query("SELECT id FROM NAMES2");
  while($res=mysql_fetch_array($query))
  {
  ++$j;
  mysql_query("UPDATE NAME SET SIno='$j' WHERE rid='$res[id]'");
  }