Sam Sam - 1 year ago 112
MySQL Question

How to remove a query from an HTML table and database?

I have a database with 2 tables:

1st table "data" with columns(name , phone , personid)

2nd table "links" with columns (linkid , link , personid)

Personid is the foreign key that connects the two tables with a "one to many" relationship and it's "CASCADE" when DELETE or UPDATE ,
So one person could have more than 1 link.

The HTMl table looks like that:

name phone links
jim 432443 link1
link2
link3
.....
_______________________
john 54545 link1
_______________________
... ..... .....


The code that show database contains on an html table:

$state = $connect->prepare("SELECT data.personid, name, phone, link FROM data JOIN links ON data.personid = links.personid");
$state->execute();
$results = $state->fetchAll(PDO::FETCH_ASSOC);
$data = [];

foreach($results as $result) {

$data[$result['personid']] = [
'name' => $result['name'],
'phone' => $result['phone'],
'links' => [],
];


$data[$result['personid']]['links'][] = $result['link'];
}

<table>

<thead>
<tr>
<th>Name</th>
<th>Phone</th>
<th>Links</th>
</tr>
</thead>
<tbody>
<?php
foreach ($data as $row) {
echo "<tr>";
echo "<th>".$row['name']."</th>";
echo "<td>".$row['phone']."</td>";
echo "<td>".implode('<br/>', $row['links'])."</td>";
echo "<td>";
echo "</td>";
echo "</tr>";
}
?>
</table>


I want to add a delete functionality to delete some of these data , Like a checkBox next to each query and a button called "Delete Selected" for example, I know how to add the checkBox and the button but I don't know the code to achieve this funcunality .

Answer Source

hope it works for add please check the beloe code.

On html include below:

<form action="action.php" method="POST">
<button type="submit">delete</button>
<table>

 <thead>
    <tr>
        <th><input type="checkbox" name="check_select" readonly="readonly" onclick="toggleselect(this,'delete_data');"></th>
        <th>Name</th>
        <th>Phone</th>
        <th>Links</th>
        </tr>
 </thead>
 <tbody>
<?php
      foreach ($data as $row) {
                    echo "<tr>";
                    echo '<input type="checkbox" class="check_box" name="checkbox_delete_data[]" value="'.$row['primary_id_of_your_table'].'">';
                    echo "<td>".$row['name']."</td>";
                    echo "<td>".$row['phone']."</td>";
                    echo "<td>".implode('<br/>', $row['links'])."</td>";
                    echo "<td>";
                    echo "</td>";
                    echo "</tr>";
         }
     ?>

</table>
</form>

On Javascript:

function toggleselect(source,chkbox_name) 
    {
      checkboxes = document.getElementsByName('checkbox_'+chkbox_name+'[]');
      for(var i=0, n=checkboxes.length;i<n;i++) {
        checkboxes[i].checked = source.checked;
      }
    }

now you can access the selected check box in your action.php file and use it to delete with query.

for e.g

<?php
if (isset($_POST["submit"])) {
    $selected_check_boxes = implode(',', $_POST["checkbox_delete_data"]);
    $qury= $connect->prepare("Delete from table where table.primary id IN ($selected_check_boxes)");
$qury->execute();
}

?>
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download