Delete Records using an Array

[Salchester]

Howdy Everyone,

I'm currently using the following, in order to delete records in a database upon clicking a delete button:

<?
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
{
  die('Could not connect: ' . mysql_error());
}
$view=$_GET["view"];
$id=$_GET["id"];
switch ($view) {
case "list":

mysql_select_db("WB", $con);

$result = mysql_query("SELECT * FROM Birds");

echo "<table border='1'>
<tr>
<th>Name</th>
<th>Species</th>
<th>Sex</th>
<th>Delete</th>
</tr>";

while($row = mysql_fetch_array($result))
{
  echo "<tr>";
  echo "<td>" . $row['Name'] . "</td>";
  echo "<td>" . $row['Species'] . "</td>";
  echo "<td>" . $row['Sex'] . "</td>";
  echo "<td><input type=\"button\" value=\"Delete\" onClick=\"parent.location='Select.php?view=delete&id=" . $row['ID'] . "'\"></td>";
  echo "</tr>";
}
echo "</table>";

    break;
case "delete":
    //insert your mysql string here deleting the record.
    mysql_select_db("WB", $con);

    mysql_query("DELETE FROM Birds WHERE id='$id'");

    mysql_close($con);
    //use the header function to get you back to the previous screen.
    $url="Select.php?view=list";
    header("Location: $url");
    break;
default:
    $url="Select.php?view=list";
    header("Location: $url");
} 
?>

Instead of using a static ID to identify a record, is there anyway of calculating the number of records in the table, and using the record number as the ID in order to delete a record.

Currently, I'm using a delete button next to each button, to delete the appropriate record, how can one delete button be used along with check boxes?

Many Thanks, much appreciated!!!

[sde]

not a good idea in my opinion.. if a record got deleted by user 1 between the time that the page loaded for user 2 and when user 2 presses delete, then the numbers would be off.

[Salchester]

SDE,

So, how do records in a database usually get deleted? As the ID's for each record are going to have massive gaps in between the other records.

For example you would have the following if record ID 2,5,8 got deleted.

ID
1
3
4
6
7
9
10

How can this be stopped?

Many Thanks,

[bdl]

Originally Posted by Salchester

So, how do records in a database usually get deleted? As the ID's for each record are going to have massive gaps in between the other records.

For example you would have the following if record ID 2,5,8 got deleted.

ID
1
3
4
6
7
9
10

How can this be stopped?

Many Thanks,

It can't and it shouldn't. MySQL keeps track of the records by it's own method using your ID value. It doesn't matter if the ID values are in a nice neat sequential list.

[sde]

yeah, the purpose of an id field is just so that each record has a unique piece of data you can identify it with.

as you pointed out, the id's will have gaps when you delete them, but why do you need to number them?

is it only so you can show the users a nicely numbered list? if that is the case, then you should just number it when you print out the data to the user.. you don't have to use a database field for that.

[Salchester]

SDE,

So I don't need IDs?
How will the single delete button know which records have a check box next to them, in order to delete the appropriate record.

How can this be done?

Many Thanks,

[sde]

So I don't need IDs?

i didn't say that.

[Salchester]

Originally Posted by sde

yeah, the purpose of an id field is just so that each record has a unique piece of data you can identify it with.

as you pointed out, the id's will have gaps when you delete them, but why do you need to number them?

is it only so you can show the users a nicely numbered list? if that is the case, then you should just number it when you print out the data to the user.. you don't have to use a database field for that.

SDE,

As you mentioned above, why do I need to number them?
I'm not numbering the records to output a numbered list, so why do I need the ID's?

I'm now confused, so do I need the ID's now? :-)
But how will the code now which records to delete?

Many Thanks,

[sde]

ah ok, well since you were concerned about the IDs being in order.. i was guessing that you also wanted to number them... otherwise, who in the world would care if the IDs have gaps in them.

forget about the numbering then.. that was an example that doesn't fit your needs.

as far as creating a link to delete the file, it looks like you already have the code to do that. your link ends with ?id=...

did you write that code or did you just find the script somewhere?

[Salchester]

SDE,

Sorry, I think I'm confusing my-self, and you for that matter.

Currently I have a table that adds an ascending number to a ID column in a database, the problem is when a user deletes a record, gaps in the numbering are left.

Can the ID be set depending on the total amount of records stored in the database? Upon a record being deleted, the ID's are replaced with new ID's totaling the amount of records currently stored in the database.

For example, f i had ten records and deleted 4, the ID's for the remaining records will need to be replace with number 1-6.

Many Thanks,

[sde]

the problem is when a user deletes a record, gaps in the numbering are left.

please give me one reason why you think this is a problem? it is absolutely not a problem and this practice is probably used in nearly all db designs.

i think you just need to know that it is OK to have gaps in numbers that represent an ID field.

[Salchester]

SDE,

No, you see I will being using the IDs later to carry out other functions, therefore I need the record IDs to correspond with the amount of records there are in the database.

Many Thanks,

[sde]

mysql has functions for that.

$result = mysql_query("select count(*) from my_table");
$number_of_results = mysql_result($result, 0, 0);

[Salchester]

SDE,

So how do I delete record 2 from the total amount of records.
Assuming there is a record 2 in the table, of course.

Many Thanks,

[sde]

when you say 'record 2' .. do you mean the record with the ID 2? .. or do you mean the 2nd record in the table?