deleting rows from an HTML table

Hi all,

I am not sure how much work this would be to accomplish, but right now I have a PHP script that is pulling users from a MySQL table and printing it in an HTML table.  I currently have a button at the end of each row that I want to make a delete button.  Essentially, administrative users should be able to view a complete list of all of the members and if desired, click the button on one of the rows to delete a member from the table.

I could send a POST back to the server with the id of the row selected, delete the row in MySQL, and then refresh the page.  However, it would be more desirable to just click the button and have the table automatically updated through ajax.  This is basically the same way that gmail behaves.  If I click on a message and select the delete button then the selected row(s) is/are deleted but the whole page doesn't refresh.

I'm not really sure how to do this however -- it seems like I would need to pull the entire HTML for the table through ajax.  Any ideas on how to accomplish this?

This isnt as hard as you think, i'll give you a site below for reference, however you probably got to restructure you output code just a little.  Mostly turn the link into an javascript FUNCTION.

However in order to know where to delete the data from, there should be an ID name which lets the javascript which field on the table to delete, but also lets the mysql database which field to delete as well.

Here is the link http://www.dynamicdrive.com/forums/archive/index.php/t-4377.html

Also some research into DOM structure might be helpful if you havent done so before.

Cool thanks!  I'll check it out.  I see that there is a defect in the code though -- it only wants to delete the last row added.  But I'm sure I can tweak things and get it to work.  I'll try anyway!

So I figured out how to do it, though some may claim it's a bit of a hack.  Basically what I wound up doing is having AJAX populate a div tag in a "parent" HTML document (see the stateChanged() function and you will see that it specifically writes the data to the "myTable" div tag).  The idea is that the entire HTML content (the table and everything) is printed by a script that is invoked within the parent document.  Certainly if someone had more content to write to the page then this form and table then that could be static content contained outside of the myTable div tag -- and therefore only this small portion of the page's content would be reloaded.

Initially, the div tag contains a single space character, and I use the body onload option to populate the page initially.  Whenever a button is pushed, it invokes the AJAX code to reload the page -- the showTable function is called and I pass the child page (getTable.php) the appropriate arguments.  Depending on what arguments are passed to getTable.php it will either add a row to the table, delete it, or "do nothing".  In all three cases it prints the table afterwards into the myTable div tag, replacing what was previously there.

One thing I learned from this whole experience (which I think is pretty neat) was that the "child" page can invoke scripts that are written in the parent page as if the entire document was all one large, single HTML file.  Nifty!

This particular implementation is for adding and deleting "users" from a MySQL database.  The code simply adds or deletes users (rows) from the database.  The PHP scripts I have show the table and displays a form that allows one to add new rows to it.

This is just one implementation -- I'm sure there are others and I'm sure that there is a better way to implement parts or all of this.  If anyone has any suggestions, I would love to know.  I particularly believe that the HTTP GET request is not implemented well.  The AJAX code invokes a PHP script and passes it parameters by appending to the end of the string such as:

getTable.php?action=delete?newusername=12

Normally when I do a GET or POST through a form then I can use $_GET or $_POST in the backend script to retrieve the values.  Inside of getTable.php I have to do a bunch of substrings in order to parse the string...using $_GET doesn't seem to work for some reason (I'm not savvy enough yet to know why).

So here is the "parent" page that I wrote.  Note that I have trimmed down some of the code because I have some session data going on, checking for admin rights (only admins can add/delete users), and so on.  I wouldn't recommend using this for some sort of secure user administration, but I do think it's a good example:

<html>
<head>

<script type="text/javascript">
var xmlhttp;

function showTable(str,user,newpw1,newpw2,admin)
{
  xmlhttp=GetXmlHttpObject();
  if (xmlhttp==null)
  {
    alert ("Sorry but your browser does not support AJAX or Javascript has been disabled");
    return;
  }

  // we always want to call getTable
  var url="getTable.php";
  url=url+"?action="+str;

  if (str=="add")
  {
    // we're adding so append new user info in GET request
    url=url+"?newusername="+user;
    url=url+"?newpassword="+newpw1;
    url=url+"?newpassword2="+newpw2;
    url=url+"?admin="+admin;
  }
  else if (str=="delete")
  {
    var doAction = confirm("Are you sure you want to delete id #"+user+"?");
    if (doAction == true)
    {
      // we're deleting so append userID in the GET request
      url=url+"?newusername="+user;
    }
    else
    {
      // not deleting so reset the url
      url = "getTable.php?action=nothing"
    }
  }

  // execute the fully formed request
  xmlhttp.onreadystatechange=stateChanged;
  xmlhttp.open("GET",url,true);
  xmlhttp.send(null);
}


function stateChanged()
{
  if (xmlhttp.readyState==4)
  {
    document.getElementById("myTable").innerHTML=xmlhttp.responseText;
  }
}


function GetXmlHttpObject()
{
  if (window.XMLHttpRequest)
  {
    // code for IE7+, Firefox, Chrome, Opera, Safari
    return new XMLHttpRequest();
  }
  if (window.ActiveXObject)
  {
    // code for IE6, IE5
    return new ActiveXObject("Microsoft.XMLHTTP");
  }
  return null;
}
</script>



</head>
<body onload="showTable('first',null,null,null,null)">
<div id="myTable"> </div>
</body>
</html> 

And then here is the "child" script, getTable.php:

<?php
ob_start();
$host="localhost"; // Host name
$username="root"; // Mysql username
$password="xxx"; // Mysql password
$db_name="test"; // Database name
$tbl_name="users"; // Table name

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

// get the value of action
$action = $_GET['action'];

if (substr($action,0,3) == "add")
{
  // get the rest of the values -- start by stripping off "add?"
  $action = substr($action, 4);

  // newusername is first so strip it and then get the first "?"
  $action = substr($action, 12);   // newusername= is 12 chars
  $pos = strpos($action, "?");
  $newusername = substr($action, 0, $pos);
  $action = substr($action, ++$pos);

  // now get the newpassword value
  $action = substr($action, 12);   // newpassword= is 12 chars
  $pos = strpos($action, "?");
  $newpassword = substr($action, 0, $pos);
  $action = substr($action, ++$pos);

  // now get the newpassword2 value
  $action = substr($action, 13);   // newpassword2= is 13 chars
  $pos = strpos($action, "?");
  $newpassword2 = substr($action, 0, $pos);
  $action = substr($action, ++$pos);

  // now get the admin value
  $action = substr($action, 6);
  $admin = $action;


  // To protect MySQL injection
  $newusername = stripslashes($newusername);
  $newpassword = stripslashes($newpassword);
  $newpassword2 = stripslashes($newpassword2);
  $newusername = mysql_real_escape_string($newusername);
  $newpassword = mysql_real_escape_string($newpassword);
  $newpassword2 = mysql_real_escape_string($newpassword2);

  
  // check if the username already exists
  $sql="SELECT * FROM $tbl_name WHERE username='$newusername'";
  $result=mysql_query($sql);

  // mysql_num_row counts table rows
  $count=mysql_num_rows($result);

  // If result matched $myusername table rows must be 1 row
  if($count==1)
  {
    echo "<center>";
    echo "<p><font color=\"red\">User already exists</p></font>";
    echo "</center>";
  }
  else if ( $newpassword == $newpassword2)
  {
    $encrypted_mypassword = md5($newpassword);

    // check for admin authority
    if ($admin == "true")
    {
       $sql="INSERT INTO $tbl_name (username, password, isadmin) VALUES('$newusername', '$encrypted_mypassword', 1)";
    }
    else
    {
       $sql="INSERT INTO $tbl_name (username, password, isadmin) VALUES('$newusername', '$encrypted_mypassword', 0)";
    }
    mysql_query($sql);
  }
  else if ($newpassword != $newpassword2)
  {
    echo "<center>";
    echo "<p><font color=\"red\">Passwords do not match</font></p>";
    echo "</center>";
  }
}
else if (substr($action, 0, 6) == "delete")
{
  // get the correct user to be deleted using substr
  // delete?newusername= is 19 characters
  $delUser = substr($action, 19);

  // protect against MySQL injection
  $delUser = stripslashes($delUser);
  $delUser = mysql_real_escape_string($delUser);

  $sql="DELETE FROM members WHERE id=" . $delUser;
  mysql_query($sql);
}

// if the value of action was anything else then ignore it
// and print the table without adding or deleting

$sql = "SELECT * FROM members ORDER BY id";
$result = mysql_query($sql);

// write a link back to the login_success page:
?>

<form>
<table width="300" border="0" align="center" cellpadding="0" cellspacing="1" bgcolor="#CCCCCC">
<tr>
<td>
<table width="100%" border="0" cellpadding="3" cellspacing="1" bgcolor="#FFFFFF">
<tr>
<td colspan="3"><strong>Add a user</strong></td>
</tr>
<tr>
<td width="78">Username</td>
<td width="6">:</td>
<td width="294"><input name="newusername" type="text" id="newusername" value=""></td>
</tr>
<tr>
<td>Password</td>
<td>:</td>
<td><input type="password" name="newpassword" id="newpassword" value=""></td>
</tr>
<tr>
<td>Repeat Password</td>
<td>:</td>
<td><input type="password" name="newpassword2" id="newpassword2" value=""></td>
</tr>
<tr>
<td>Create as admin?</td>
<td> </td>
<td><input type="checkbox" name="admin" value=""></td>
</tr>
<tr>
<td> </td>
<td> </td>
<td><input type="button" name="submit" value="Add User" onClick="showTable('add',this.form.newusername.value,this.form.newpassword.value,this.form.newpassword2.value,this.form.admin.checked);"></td>
</tr>
</table>
</td>
</tr>
</table>
<br><br>

<?php
// write the rest of the form
echo "<center>\n";

// draw the table
echo "<table border=1>\n";
echo "<tr bgcolor=\"#000000\">\n";
echo "<th><font color=\"#ffffff\">id</font></th>\n";
echo "<th><font color=\"#ffffff\">username</font></th>\n";
echo "<th><font color=\"#ffffff\">password</font></th>\n";
echo "<th><font color=\"#ffffff\">isadmin</font></th>\n";
echo "<td><font color=\"#ffffff\"> </font></th>\n";
echo "</tr>\n";
$rownum = 0;
while ($row = mysql_fetch_array($result))
{
  if ($rownum % 2 == 0)
  {  $color="#ffffff";
  }
  else
  {  $color="#cfd9c3";
  }
  echo "<tr bgcolor=\"$color\">\n";
  echo "<td>" . $row['id'] . "</td>\n";
  echo "<td>" . $row['username'] . "</td>\n";
  echo "<td>" . $row['password'] . "</td>\n";
  echo "<td>" . $row['isadmin'] . "</td>\n";
  echo "<td><input type=\"button\" value=\"Delete User\" onClick=\"showTable('delete'," . $row['id'] . ", null, null, null);\"></td>\n";
  echo "</tr>";

  $rownum++;
}
echo "</table>\n";
echo "</form>\n";
echo "</center>\n";

ob_end_flush();
?>

Oh -- and I almost forgot.  My table in MySQL was created using something like this:

CREATE TABLE users (

id integer not null auto_increment,

username varchar(32) unique,

password varchar(64),

isadmin tinyint,

primary key(id)

);

Man I can't believe I didn't notice this before...I feel really silly now.

I looked at my script again and the reason I couldn't use $_GET to obtain the values inside of the string was because I appended question marks instead of ampersands.  D'oh!  I changed up my Javascript like so:

  // we always want to call getTable
  var url="getTable.php";
  url=url+"?action="+str;

  if (str=="add")
  {
    // we're adding so append new user info in GET request
    url=url+"&newusername="+user;
    url=url+"&newpassword="+newpw1;
    url=url+"&newpassword2="+newpw2;
    url=url+"&admin="+admin;
  }
  else if (str=="delete")
  {
    var doAction = confirm("Are you sure you want to delete id #"+user+"?");
    if (doAction == true)
    {
      // we're deleting so append userID in the GET request
      url=url+"&userid="+user;
    }
    else
    {
      // not deleting so reset the url
      url = "getTable.php?action=nothing"
    }
  }

and it made the PHP script on the backend much simpler too:

if ($action == "add")
{
  // get the rest of the values
  $newusername = $_GET['newusername'];
  $newpassword = $_GET['newpassword'];
  $newpassword2 = $_GET['newpassword2'];
  $admin = $_GET['admin'];
  ...
}
else if ($action == "delete")
{
  // get the user id to be deleted
  $delUser = $_GET['userid'];
  ...
}

Hooray!  No more ridiculous substring nonsense.