I have this code that creates gets the data from a sqlite3 database and prints it out in a table with checkboxes next to each row to check to delete that row. After deleting a row I want to update the data base so that the id of the rows if I delete a row other than the last it updates it to close the gap. So if there was row 1234 and I deleted 3 I want to update the database so that the id of row 4 becomes row 3 so it will stay 123. The problem is if I i delete multiple rows. I have the update in the loop to pick up which rows have been checked, so it screws up the consecutive deletes. When I remove the update query from the loop it deletes the right rows but then it leaves the gaps in the row id's. Then when I put the update query outside of the loop it doesn't pick up the $var(which is the row id) in the list used in the loop. I was hoping someone could help me with how to fix this. My code is below.
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<link rel="stylesheet" href="../green.css" type="text/css" title="green"/>
<title>myGreen Home</title>
</head>
<body>
<?php
// Connect to server and select databse.
/*try {
$db = new PDO('sqlite:jobs.db');
} catch (Exception $e) {
die ($e);
}*/
$db = new SQLite3('jobs.db');
if($_POST['delete']){
$i = 0;
while(list($key, $val) = each($_POST['checkbox'])) {
//$sql = "DELETE FROM $tbl_name WHERE id='$val'";
// print($val);
$db->exec("DELETE FROM jobs WHERE id='$val'");
$db->exec("UPDATE jobs SET id = (id-1) WHERE id >= '$val'");
$i += $db->changes();
}
// if successful redirect to delete_multiple.php
if($i > 0){
//$db->exec("UPDATE jobs SET id = (id-1) WHERE id >= '$update'");
header('Location: index.html');
}
}
?>
<table width="400" border="0" cellspacing="1" cellpadding="0">
<tr>
<td><form name="form1" method="post" action="">
<table width="400" border="0" cellpadding="3" cellspacing="1" bgcolor="#CCCCCC">
<tr>
<td bgcolor="#FFFFFF"> </td>
<td colspan="4" bgcolor="#FFFFFF"><strong>Delete Jobs</strong>
</td>
</tr>
<tr>
<td align="center" bgcolor="#FFFFFF">#</td>
<td align="center" bgcolor="#FFFFFF"><strong>Id</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>Job Title</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>Employer</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>Location</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>Email</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>Category</strong></td>
<td align="center" bgcolor="#FFFFFF"><strong>Date</strong></td>
</tr>
<?php
$sql="SELECT * FROM jobs";
$result=$db->query($sql);
//$result = $db->query("SELECT * FROM jobs");
while($rows = $result->fetchArray(SQLITE3_ASSOC))
{
?>
<tr>
<td align="center" bgcolor="#FFFFFF"><input name="checkbox[<?php echo $rows['id']; ?>]" type="checkbox" value="<? echo $rows['id']; ?>"></td>
<td bgcolor="#FFFFFF"><? echo $rows['id']; ?></td>
<td bgcolor="#FFFFFF"><? echo $rows['jobtitle']; ?></td>
<td bgcolor="#FFFFFF"><? echo $rows['employer']; ?></td>
<td bgcolor="#FFFFFF"><? echo $rows['location']; ?></td>
<td bgcolor="#FFFFFF"><? echo $rows['email']; ?></td>
<td bgcolor="#FFFFFF"><? echo $rows['category']; ?></td>
<td bgcolor="#FFFFFF"><? echo $rows['date']; ?></td>
</tr>
<?php
}
$db->close();
?>
<tr>
<td colspan="5" align="center" bgcolor="#FFFFFF"><input name="delete" type="submit" value="Delete" ></td>
</tr>
</table>
</form>
</td>
</tr>
</table>
</body>
</html>