updating product id serial wise

[vinpkl]

hi all

 

i have product id like

1
2
3
4
5

 

if i delete product no. 5, then the next product will be added at no. 6 not 5.

 

if i delete product no. 3, then i want that product no.4 should become product no.3 automatically.

 

how can i update the id so that everytime a product is deleted or added the product id series remain serial wise.

 

vineet

 

 

[Andy-H]

Any code you can show us?

 

I would suggest that when you are deleting the data you should first select the product ID from your database and save it to a variable, after that is done use a query to decrement all of the ID's which were higher that the variable.

[vinpkl]

hi andy

 

here is the code i m using

$pid=$_REQUEST['pid'];
if(isset($_REQUEST['pid']))
{
$qry="delete from product_table where product_id=$pid";
mysql_query($qry);
}

 

so according to you i should decrement all higher product id's by 1.

 

can u give me some syntex example or it wil be something like

$pid - 1

 

 

vineet

[Andy-H]

if (isSet($_REQUEST['pid']))
{
$pid = intVal($_REQUEST['pid']);

$qry = "SELECT pid FROM product_table WHERE product_id = $pid LIMIT 1";
$res = mysql_query($qry);

if (mysql_num_rows($res) == 1)
{
$row = mysql_fetch_row($res);
$idNum = $row[0];

mysql_query("DELETE FROM product_table WHERE product_id = $idNum");
mysql_query("UPDATE product_table SET product_id = product_id-1 WHERE product_id > $idNum");

echo 'Product sucessfully deleted.';

}
}

 

Something like that?

[vinpkl]

hi andy

 

thanks. this solutions works perfect for me for my product table.

 

i have another table in which all id's are messed up like

23
27
28
34
35 

 

is there any solution for these kind of situations. how can i make them start again from 1 and make them serial wise.

 

vineet

[Andy-H]

$n = 1;
$query = "SELECT * FROM table";
$result = mysql_query($query);

$num = mysql_num_rows($result);

while ($row = mysql_fetch_assoc($result) && $n < $num)
{
$q = "SELECT * FROM table WHERE id = $n LIMIT 1";
$r = mysql_query($q);

if (mysql_num_rows($r) == 1)
$n++;
}else{

mysql_query("UPDATE table SET id = $n WHERE id > $n LIMIT 1");
$n++;
}

}

echo number_format($n) . ' rows updated.';

 

Try that mate, I think it should do the trick, if the table has alot of records I wouldnt run it too often and when adding data if your using an auto increment I think it might just mess up again anyway. :S 

[vinpkl]

hi andy

 

yes, it does works but simultaneously messes up with auto increment.

 

anyway this can be helpful some or the other time where there are not many records.

 

thanks.

 

vineet