Can't figure out how to update a database after deleteing multiple entries.

Question

Jfunch 0 Newbie Poster

13 Years Ago

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">&nbsp;</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>

php

4 Contributors
3 Replies
159 Views
3 Days Discussion Span
Latest Post 13 Years Ago Latest Post by diafol

chrishea 182 Nearly a Posting Virtuoso

13 Years Ago

I haven't examined your code so I can't tell you how to 'fix' it.

The key question is why you want to do this. There is no harm done if there are gaps in the sequence but you will potentially be doing a lot of extra updating to renumber everything in order to avoid it. I presume that you are trying to avoid some sort of problem in another process by eliminating the gaps but I think that you need to re-examine that (other process) and see if you can change it to cope with the gaps. With a small database, the extra overhead may not be significant but with a database of any size it could be.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

ddymacek 15 Posting Whiz · Answer 1 · 2011-12-13T01:12:24+00:00

Your logic is flawed... and it appears that the id's on delete would not work properly, for instance if you have 5 records and 3 and 5 are chosen to be
deleted, by your logic, 5 would not be deleted as it is now id 4 because you would delete 3 and move 4 to 3, 5, to 4, and you would still retain record 5's data
only in record 4's slot and 5 would not be deleted.

chrishea is probably right in his reply, is this really a big deal?...
first remove the call to update that is in that loop (line 33 above).

That said, here is what I would do... outside of your loop, say on line 36 of your code above, prior to your redirect call
make a call to reset_ids() or some such function name. in that function I would make a simple counter and reset all of the ids
on my database.

<?php
function reset_ids() {
	$sql="SELECT id FROM jobs order by id asc";
	$result=$db->query($sql);
	$cnt = 0;
	while($rows = $result->fetchArray(SQLITE3_ASSOC)) {
		// increment your counter, 
		$cnt++;  // first time it = 1, next time it = 2 etc....
		$db->exec("update jobs set id = '".$cnt."' where id = ".$rows['id']);		
	}	
}
// or starting on line 36 of your code, do this:
	$sql="SELECT id FROM jobs order by id asc";
	$result=$db->query($sql);
	$cnt = 0;
	while($rows = $result->fetchArray(SQLITE3_ASSOC)) {
		// increment your counter, 
		$cnt++;  // first time it = 1, next time it = 2 etc....
		$db->exec("update jobs set id = '".$cnt."' where id = ".$rows['id']);		
	}	
	
	// untested code, I don't have sqlite, you may need to ' ' around $rows['id'] not sure of the structure of your table but it appears
	// as you are treating that field as text and not an int or numerical. because when you delete or were calling your update it was like this: = '$val'" which to me indicates text.
	// anyway, good luck with that

diafol · Answer 2 · 2011-12-13T01:57:18+00:00

renumbering an id is not good as already mentioned. You may be using the id in a relationship with another table. Renumbering could cause all sorts of nonsense. Avoid it.