vinpkl Posted April 17, 2009 Share Posted April 17, 2009 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 Quote Link to comment Share on other sites More sharing options...
Andy-H Posted April 17, 2009 Share Posted April 17, 2009 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. Quote Link to comment Share on other sites More sharing options...
vinpkl Posted April 17, 2009 Author Share Posted April 17, 2009 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 Quote Link to comment Share on other sites More sharing options...
Andy-H Posted April 17, 2009 Share Posted April 17, 2009 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? Quote Link to comment Share on other sites More sharing options...
vinpkl Posted April 17, 2009 Author Share Posted April 17, 2009 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 Quote Link to comment Share on other sites More sharing options...
Andy-H Posted April 17, 2009 Share Posted April 17, 2009 $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 Quote Link to comment Share on other sites More sharing options...
vinpkl Posted April 17, 2009 Author Share Posted April 17, 2009 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.