disorganization table after deleting a row SQL PHP

Question

i have a admin page for add or remove jobs in a table. think we have 10 job in table:

 1 ... 10

yeah it is simple but what will be done if admin want remove second job of table? table will have:

1,3 ... 10

and new jobs will add as:

11 to N

i need a right way to sort values after deleting.

this is fetching values of database table to choose and then remove:

 <form name="form2" method="post" action="delete.php" > 
 <?php

   $db_host = 'localhost';
   $db_name= 'site';
   $db_table= 'job_list';
   $db_user = 'root';
   $db_pass = '';




$con = mysql_connect($db_host,$db_user,$db_pass) or die("خطا در اتصال به پايگاه داده");

mysql_query("SET NAMES 'utf8'", $con);
mysql_query("SET CHARACTER SET 'utf8'", $con);
mysql_query("SET character_set_connection = 'utf8'", $con);

$selected=mysql_select_db($db_name, $con) or die("خطا در انتخاب پايگاه داده");
mysql_query("SET CHARACTER SET  utf8");
$dbresult=mysql_query("SELECT * FROM  $db_table",$con);
echo "شغلی که می خواهید حذف کنید انتخاب نمایید: ";
echo '<br/>';

echo '<select name="delete">';

while($amch=mysql_fetch_assoc($dbresult))
{
   echo '<option value="'.$amch['job_id'].'">'.$amch['job_name'].'</option>';
}
echo '</select>'; ?> <br/>
 <input name="submit2" type="submit" value="submit2" />

</form>

and this is my delete.php:

<?php
$db_host = 'localhost';
$db_name= 'site';
$db_table= 'job_list';
$db_user = 'root';
$db_pass = '';


$con = mysql_connect($db_host,$db_user,$db_pass) or die("خطا در اتصال به پايگاه داده");

mysql_query("SET NAMES 'utf8'", $con);
mysql_query("SET CHARACTER SET 'utf8'", $con);
mysql_query("SET character_set_connection = 'utf8'", $con);

$selected=mysql_select_db($db_name, $con) or die("خطا در انتخاب پايگاه داده");
$ins = "DELETE FROM job_list 
     where job_id='" . mysql_escape_string($_POST['delete']) . "'";
     $dbresult=mysql_query($ins,$con);
echo "('" . mysql_escape_string($_POST['delete']) . "')";


?>

You really should not be writing code that relies on `mysql_` functions anymore. The MySQL extension has been deprecated for years and is about to be dropped in the upcoming PHP7 release later this year. Also see [Why shouldn't I use mysql_* functions in PHP?](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). On an up-to-date server, this code has a life span of about 2 months. — Oldskool, Nov 04 '15 at 11:06
You shouldn't be relying on consecutive pk id values..... pk id's should be unique, but need not be sequential (and commonly aren't) — Mark Baker, Nov 04 '15 at 11:09
@Pupil: can you please edit my code with datatable? i do not know how to use of datatable. — Sajad Khammar, Nov 04 '15 at 11:10
@SajadKhammar, please refer this link: http://datatables.net/examples/basic_init/zero_configuration.html — Pupil, Nov 04 '15 at 11:12
I'm not sure datatables.net is relevant here? It sounds like OP is wanting to regenerate the auto increment column so there are no gaps between ids, which is pointless frankly. Correct me if I've got the wrong end of the stick. — Jonnix, Nov 04 '15 at 11:16

score 1 · Accepted Answer · answered Nov 04 '15 at 12:29

Based on the information you've provided (that the only relevance of the numbering of jobs is within the user interface) you simply need to decouple the number on the browser from the numbering in the database. In the database use an autoincrement id.

In fact, since users never get to see the "number" associated with a job, you don't even need to bother with an auto increment value.

Unless there's some relevance to the numbering which you've not told us about.

disorganization table after deleting a row SQL PHP

1 Answers1