How do I delete multiple records in my database

Question

I wanna create a button which deletes all the records that needs to be deleted. I have three tables in my database person, address and cv... address_id has a relation with person_address in person table and cv_id has a relation with person_cv in person table. When the delete button is pressed everything connected to the person should be deleted. I tried lots of things like:

 echo "<td><a href='delete.php?id=<?" . $row['id'] . "'>delete</a></td>";

With a external delete.php but it's not working at all... link in the link the tutorial I used for delete.php

My detail page where the delete button should be in after CV:

<?php
$servername = "localhost";
$username = "root";
$password = "usbw";
$dbname = "persons";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
} 

$sql = "SELECT person_firstname, person_lastname, 
           person_email, person_phonenumber,  
           address_street,address_housenumber, 
           address_city,address_state,address_zipcode, cv_path
    FROM person 
       inner join address on address.address_id = person.person_address 
       inner join cv on cv.cv_id = person.person_cv";

$result = $conn->query($sql);
if ($result->num_rows > 0) {

  echo "<table border=1>
  <tr>
  <th>Voornaam</th>
  <th>Achternaam</th>
  <th>Straat</th>
  <th>Huisnummer</th>
  <th>Postcode</th>
  <th>Stad</th>
  <th>Provincie</th>
  <th>Email</th>
  <th>Mobiel</th>
  <th>CV</th>
  </tr>";


while($row = $result->fetch_assoc()) {
    echo "<tr>";
    echo "<td>" . $row["person_firstname"] . "</td>";
    echo "<td>" . $row["person_lastname"] . "</td>";
    echo "<td>" . $row["address_street"] . "</td>";
    echo "<td>" . $row["address_housenumber"] . "</td>";
    echo "<td>" . $row["address_zipcode"] . "</td>";
    echo "<td>" . $row["address_city"] . "</td>";
    echo "<td>" . $row["address_state"] . "</td>";
    echo "<td>" . $row["person_email"] . "</td>";
    echo "<td>" . $row["person_phonenumber"] . "</td>";
    echo "<td><a href='http://localhost:8080/website/" . $row['cv_path'] . "'>cv file</a></td>";
    echo "</tr>";
}
}
else {
echo "Er is niks in het database gevonden";
}
$conn->close();
?> 

Delete.php:

<?php

$host="localhost"; // Host name 
$username="root"; // Mysql username 
$password="usbw"; // Mysql password 
$db_name="persons"; // Database name 
$tbl_name="person"; // Table name 

// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

// get value of id that sent from address bar 
$id=$_GET['person_id'];

// Delete data in mysql from row that has this id 
$sql="DELETE FROM $tbl_name WHERE person_id='$id'";
$result=mysql_query($sql);

// if successfully deleted
if($result){
echo "Deleted Successfully";
echo "<BR>";
echo "<a href='admin.php'>Back to main page</a>";
}

else {
echo "ERROR";
}
?> 

<?php
// close connection 
mysql_close();
?>

Answer 1

There are two ways to do it:

1. Manually delete from all the 3 tables. Below is the query for that:

   delete prsn, addr, cv from person prsn join address addr on p.person_address = addr.address_id join cv cv on prsn.person_cv = cv.cv_id where prsn.id = ?

2. Create foreign key constraint in such a way that child record automatically gets deleted once parent record is deleted. E.g. recreate address table like this:

   create table address( ... FOREIGN KEY (address_id) REFERENCES person(person_Address) ON DELETE CASCADE );

and then, we just need to execute the below query:

delete from person where id = ?

Answer 2

I see from the screenshot that you are already using InnoDB tables so it should not be too hard to set some foreign key dependencies on the address and cv tables using person_id as the referenced column.

As an example of using Foreign keys to achive the rippled delete that seems to be causing issues here are three tables that have a foreign key dependency set - that way you could delete from the person table ~ using the id of the person and all records from the related tables would also be deleted.

create table if not exists `person` (
  `id` int(10) unsigned not null auto_increment,
  `name` varchar(50) not null,
  primary key (`id`)
) engine=innodb auto_increment=2 default charset=latin1;

create table if not exists `address` (
  `id` int(10) unsigned not null auto_increment,
  `person_id` int(10) unsigned not null default '0',
  `address` varchar(50) not null default '0',
  primary key (`id`),
  unique key `person_id` (`person_id`),
  constraint `fk_user_address` foreign key (`person_id`) references `person` (`id`) on delete cascade on update cascade
) engine=innodb auto_increment=2 default charset=latin1;

create table if not exists `cv` (
  `id` int(10) unsigned not null auto_increment,
  `person_id` int(10) unsigned not null default '0',
  `cv` varchar(50) not null default '0',
  primary key (`id`),
  key `person_id` (`person_id`),
  constraint `fk_user_cv` foreign key (`person_id`) references `person` (`id`) on delete cascade on update cascade
) engine=innodb auto_increment=2 default charset=latin1;

/* dummy data */
insert into `person` (`id`, `name`) values (1, 'bobby');
insert into `address` (`id`, `person_id`, `address`) values (1, 1, '99 riverside drive, dundee');
insert into `cv` (`id`, `person_id`, `cv`) values (1, 1, 'bobby_cv.docx');


/* this is how the tables look */    
person table
+----+-------+
| id | name  |
+----+-------+
|  1 | Bobby |
+----+-------+

address table
+----+---------------+----------------------------+
| id | person_id(fk) | address                    |
+----+---------------+----------------------------+
|  1 |         1     | 99 Riverside Drive, Dundee |
+----+---------------+----------------------------+

cv table
+----+---------------+---------------+
| id | person_id(fk) | cv            |
+----+---------------+---------------+
|  1 |         1     | bobby_cv.docx |
+----+---------------+---------------+

To delete all records associated with Bobby in this case the sql could simply be:

delete from `person` where `id`=1;

Without using Foreign keys like this you would need to construct individual sql statements for each table from which you wish to delete the user and his/her associated records.

ie:

delete from person where person_id=X;
delete from address where person_id=X;
delete from cv where person_id=X;

---

update

Apologies for delay responding - sleep called.

In your sql SELECT statement, used to display the users with associated details from other tables, you do not select the person_id which is the key ( pun intended ) element required to make the delete statement work.

$sql = "SELECT person_id, person_firstname, person_lastname, 
       person_email, person_phonenumber,  
       address_street,address_housenumber, 
       address_city,address_state,address_zipcode, cv_path
FROM person 
   inner join address on address.address_id = person.person_address 
   inner join cv on cv.cv_id = person.person_cv";

So then, in the while loop

while($row = $result->fetch_assoc()) {
    $person_id=$row['person_id'];
    /* etc - other content as before */

    /* note the parameter used is "ID" */
    echo "<a href='delete.php?id={$person_id}'>Delete</a>";
}

Then in delete.php provided the FK dependencies have been set ( and this does all depend how you have done those )

/* options used to ensure supplied id is within acceptable bounds */
$options = array(
    'options' => array(
        'default' => 0,
        'min_range' =>  1,
        'max_range' =>  10000000 /* maximum value for ID */
    )
);

/* process GET parameters - notably "ID" */
$id=filter_input( INPUT_GET, 'id', FILTER_SANITIZE_NUMBER_INT );
$id=(int)filter_var( $id, FILTER_VALIDATE_INT, $options );

/* construct & execute sql */
if( is_int( $id ) ){
    $sql="delete from `person` where `id`='{$id}';";

    $result=mysql_query( $sql );
}

One issue not addressed is that of sql injection. The original code and what I have detailed here both use the now deprecated mysql_* family of functions and are susceptible to sql injection. Once you have figured this delete issue it would be prudent to migrate your code to use mysqli so that you can utilise prepared statements