Deleting a row from a database table using checkbox

Question

I want to display a record from a database in a table with checkboxes in every row. That checkboxes will determine if the user wants to delete that specific row if it will be checked. I was able to display the data from the DB but when i press the delete button nothing happen. Im not sure but i think the error is in my deleting part of the code but i could be wrong. Im not sure. Anyway, here is the code, i just remove some lines

 <?php

$link = mysql_connect("localhost","root", "123");
if(!$link) {
    die('Failed to connect to server: ' . mysql_error());
}

 $db= mysql_select_db("abc");
if(!$db) {
    die("Unable to select database");
} 


$search = $_POST['search'];
$searchbox = $_POST['searchbox'];


$query = ("SELECT $search FROM table where $search = '$searchbox'"); 
  $result=mysql_query($query);

  if($result)
      {
        if(mysql_num_rows($result)<=0)
      { 
      echo "<script type='text/javascript'>alert('The entry does not exist'); location.href = 'admin_home.php';</script>";
      }
      }



switch ($search)
{
case 'studentnumber':
  $result = mysql_query("SELECT * FROM table WHERE $search = '$searchbox'");
if($result){

        echo "<table class='hovertable'>
        <tr>
        <caption>Student Records</caption>

 <th colspan='1'> Student Number</th>
 <th colspan='8'> $searchbox</th>
</tr>

      <tr>
      <th>Delete</th>
      <th>Student Number</th>
      <th>College</th>
      <th>Course</th>
      <th>Status</th>
      <th>Last Name</th>
      <th>First Name</th>
      <th>Middle Name</th>
      <th>Address</th>
      <th>Gender</th>
      <th>Civil Status</th>
      <th>Religion</th>
      <th>Email Address</th>
      <th>Month</th>
      <th>Day</th>
      <th>Year</th>
      <th>Father's Name</th>
      <th>Father's Occupation</th>
      <th>Mother's Name</th>
      <th>Mother's Name</th>
       </tr>";


       while($row = mysql_fetch_array($result))
      {
        echo "<tr onmouseover=\"this.style.backgroundColor='#ffff66';\" onmouseout=\"this.style.backgroundColor='#d4e3e5';\">";

         ?>
        <td><input name="need_delete[<? echo $rows['id']; ?>]" type="checkbox" id="checkbox[<? echo $rows['id']; ?>]" value="<? echo $rows['id']; ?>"></td>
        <?php
        echo "<td>" . $row['studentnumber'] . "</td>";
        echo "<td>" . $row['college'] . "</td>";
        echo "<td>" . $row['course'] . "</td>";
        echo "<td>" . $row['status'] . "</td>";
        echo "<td>" . $row['lname'] . "</td>";
        echo "<td>" . $row['fname'] . "</td>";
        echo "<td>" . $row['mname'] . "</td>";
        echo "<td>" . $row['address'] . "</td>";
        echo "<td>" . $row['gender'] . "</td>";
        echo "<td>" . $row['civilstatus'] . "</td>";
        echo "<td>" . $row['religion'] . "</td>";
        echo "<td>" . $row['emailaddress'] . "</td>";
        echo "<td>" . $row['month'] . "</td>";
        echo "<td>" . $row['day'] . "</td>";
        echo "<td>" . $row['year'] . "</td>";
        echo "<td>" . $row['father'] . "</td>";
        echo "<td>" . $row['fatheroccupation'] . "</td>";
        echo "<td>" . $row['mother'] . "</td>";
        echo "<td>" . $row['motheroccupation'] . "</td>";
      }

      echo "</table>"; 
      echo "</div>";
}
  break;

default:
  echo "<script type='text/javascript'>alert('An error occur'); location.href = 'admin_home.php';</script>";
}

?>
<input name="delete" type="submit" id="delete" value="Delete">
   <?php
            // Check if delete button active, start this
            if ( ! empty($_POST['delete'])) {
                foreach ($_POST['need_delete'] as $id => $value) {
                    $sql = 'DELETE FROM students` WHERE id='.(int)$id;
                    mysql_query($sql);
                }
                echo "<script type='text/javascript'>alert('Record Successfully Deleted');location.href = 'admin_home.php';</script>";
                exit();
            }
            mysql_close();
        ?>

Thanks in advance !!

v42 · Accepted Answer · 2012-01-15 15:17:57Z

1

You can use values on your checkbox, and use them as an array, so you will know which one has been checked.

<input type="checkbox" name="need_delete[$id]">

Doing this way, your $_POST['need_delete'] will receive an array with your ids as index, so you will be able to test and delete those registers.

answered Jan 15, 2012 at 15:17

v42

1,42514 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Treffynnon · Accepted Answer · 2012-01-15 15:18:24Z

1

It looks like your are attempting to access the wrong index in your $_POST array. See the following, which is taken from near the end of your PHP code.

if ( ! empty($_POST['delete'])) {

Should be

if ( ! empty($_POST['need_delete'])) {

answered Jan 15, 2012 at 15:18

Treffynnon

21.6k6 gold badges68 silver badges99 bronze badges

Comments

topherg · Accepted Answer · 2012-01-15 15:24:22Z

I did something similar to this for displaying as well as appending/deleting records. The method I used was to loop through all the post vars in the PHP script, and searching for all information based on the first part of the key (if it matches something, use the number after that as an object id). Then loop through the produced array of the data that will be appended, what is deleted, what is edited and what is ignored.

If the object already exists as is and the checkbox isnt set, ignore If the object already exists and data is different and the checkbox isn't set, update If the object already exists and the checkbox is set, delete If the object doesn't exist and the checkbox isn't set, create new row If the object doesn't exist and the checkbox is set, ignore

Hope that helps

Have some code

PHP Block:

$mysqlData = array();     // Items to Update
$mysqlNewData = array();  // Items to Create
$Data = $_POST;

unset($Data['stuff']);

foreach($Data as $k => $v)
{
    $type = substr($k, 0, 2);

    if ($type == "nw")
    {
        $type = substr($k, 2, 2);
        $ID = substr($k, 4);

        $mysqlSubData = $mysqlNewData[$ID];
        if (gettype($mysqlSubData) != "array")
            $mysqlSubData = array();

        if ($type == "sd")
            $mysqlSubData['startdate'] = $v;
        if ($type == "ed")
            $mysqlSubData['enddate'] = $v;
        if ($type == "dl")
            if ($v == "on")
                $mysqlSubData['delete'] = true;

        $mysqlNewData[$ID] = $mysqlSubData;
    } else {
        $ID = substr($k, 2);
        $mysqlSubData = $mysqlData[$ID];
        if (gettype($mysqlSubData) != "array")
            $mysqlSubData = array();

        if ($type == "sd")
            $mysqlSubData['startdate'] = $v;
        if ($type == "ed")
            $mysqlSubData['enddate'] = $v;
        if ($type == "dl")
            if ($v == "on")
                $mysqlSubData['delete'] = true;

        $mysqlData[$ID] = $mysqlSubData;
    }
}

Table Block:

<? $occurences = $mysql->getEntries("occurences", "`TargetID`='{$Target['ID']}'"); ?>

<table cellpadding="0" cellspacing="5" border="0" width="900" id="timetable">
<tr>
<td>Delete</td>
<td>Start Date</td>
<td>End Date</td>
</tr>

<? foreach($occurences as $occurence) { ?>
<? $ID = $occurence['ID']; ?>
<tr>
<td>
<input type="checkbox" name="dl<?=$ID?>" value="on" />
</td>
<td>
    <input type="text" name="sd<?=$ID?>" id="sd<?=$ID?>" value="<?=$occurence['startdate']?>" style="width: 100px" />
    <a href="javascript:;" onclick="javascript:NewCssCal('sd<?=$ID?>','yyyyMMdd','arrow')"><img src="/scripts/cal.gif" width="16" height="16" border="0" alt="Pick a date"></a>
</td>
<td>
    <input type="text" name="ed<?=$ID?>" id="ed<?=$ID?>" value="<?=$occurence['enddate']?>" style="width: 100px" />
    <a href="javascript:;" onclick="javascript:NewCssCal('ed<?=$ID?>','yyyyMMdd','arrow')"><img src="/scripts/cal.gif" width="16" height="16" border="0" alt="Pick a date"></a>
</td>
<td>
</td>
</tr>
<? } ?>

</table>

<a href="javascript:;" onclick="AddOccurence();">Add Another</a>

<div style="text-align: center">
<input type="image" src="/images/button_save.png" value="Save" border="0" />
<a href="/"><img src="/images/button_cancel.png" alt="Cancel" border="0" /></a>
</div>

</form>

<script type="text/javascript">
var TableRowExtras = 0;

function AddOccurence()
{
TableRowExtras++;

var da = new Date();

var t = document.getElementById('timetable');
var row = t.insertRow(t.rows.length);

var cell = row.insertCell(0);
var element = document.createElement('input');
element.name = 'nwdl' + TableRowExtras;
element.type = 'checkbox';
element.value = 'on';
cell.appendChild(element);

cell = row.insertCell(1);
element = document.createElement('input');
element.type = 'text';
element.name = 'nwsd' + TableRowExtras;
element.id = 'nwsd' + TableRowExtras;
element.value = da.getFullYear() + '-' + (da.getMonth() + 1) + '-' + da.getDate();
element.style.width = '100px';
cell.appendChild(element);

cell.innerHTML = cell.innerHTML + " ";

element = document.createElement('a');
element.href = "javascript:NewCssCal('nwsd" + TableRowExtras + "','yyyyMMdd','arrow')";

var element2 = document.createElement('img');
element2.src = '/scripts/cal.gif';
element2.width = '16';
element2.height = '16';
element2.border = '0';
element2.alt = 'Pick a date';
element.appendChild(element2);
cell.appendChild(element);

cell = row.insertCell(2);
element = document.createElement('input');
element.type = 'text';
element.name = 'nwed' + TableRowExtras;
element.id = 'nwed' + TableRowExtras;
element.style.width = '100px';
cell.appendChild(element);

cell.innerHTML = cell.innerHTML + " ";

element = document.createElement('a');
element.href = "javascript:NewCssCal('nwed" + TableRowExtras + "','yyyyMMdd','arrow')";

element2 = document.createElement('img');
element2.src = '/scripts/cal.gif';
element2.width = '16';
element2.height = '16';
element2.border = '0';
element2.alt = 'Pick a date';
element.appendChild(element2);
cell.appendChild(element);

}
</script>

Naveen Kumar · Accepted Answer · 2012-01-18 05:59:07Z

1

Write a JavaScript function when a particular check box is checked or unchecked and store the ids in a comma separated list as a hidden variable then after the page is posted u can get id's of fields you want to delete and split the variable using "Explode" to remove comma.

answered Jan 18, 2012 at 5:59

Naveen Kumar

4,6011 gold badge20 silver badges39 bronze badges

Collectives™ on Stack Overflow

Deleting a row from a database table using checkbox

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related