Post to mySQL and refresh page using AJAX

Hi all. I'm trying to create a simple HTML task manager. There are 3 forms on the page, for adding a new task, displaying the task list (while loop, recordset 'tasks') and displaying the archived tasks list (while loop, recordset 'archive'). My issue is with the 'delete' button within the tasks list. It was posting the task (by ID) to another page, which updated the 'archived' field to 'yes' then returned to index2.php, where the task had moved to the archive list. I've realised that I need to use AJAX now, to keep everything happening on the same page, but I have no experience with this. Can anyone advise me on how to go about updating the 'archived' field without leaving the page? Thanks guys, here's my code...

PHP Code:

---

<?php require_once('Connections/testdb.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  if (PHP_VERSION < 6) {
    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
  }

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
  $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "formNewTask")) {
  $insertSQL = sprintf("INSERT INTO tasks (`description`) VALUES (%s)",
                       GetSQLValueString($_POST['fieldNewTask'], "text"));

  mysql_select_db($database_testdb, $testdb);
  $Result1 = mysql_query($insertSQL, $testdb) or die(mysql_error());

  $insertGoTo = "index2.php";
  if (isset($_SERVER['QUERY_STRING'])) {
    $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
    $insertGoTo .= $_SERVER['QUERY_STRING'];
  }
  header(sprintf("Location: %s", $insertGoTo));
}

mysql_select_db($database_testdb, $testdb);
$query_tasks = "SELECT * FROM tasks WHERE archived = 'no'";
$tasks = mysql_query($query_tasks, $testdb) or die(mysql_error());
$row_tasks = mysql_fetch_assoc($tasks);
$totalRows_tasks = mysql_num_rows($tasks);

mysql_select_db($database_testdb, $testdb);
$query_archived = "SELECT * FROM tasks WHERE archived = 'yes'";
$archived = mysql_query($query_archived, $testdb) or die(mysql_error());
$row_archived = mysql_fetch_assoc($archived);
$totalRows_archived = mysql_num_rows($archived);
?>

---

HTML Code:

---

New Task<br />
<div id="newTask">
  <form id="formNewTask" name="formNewTask" method="POST" action="<?php echo $editFormAction; ?>">
    <table width="500" border="1">
        <tr>
            <td width="173"><input name="fieldNewTask" type="text" /></td>
            <td width="311"><input name="buttonNewTask" type="submit" value="add"/></td>
        </tr>
    </table>
    <input type="hidden" name="MM_insert" value="formNewTask" />
  </form>
</div>
<br />

Task List<br />
<div id="tasks">
        <form id="formTasks" name="formTasks" method="post" action="">
      <?php do { ?>
        <table width="500" border="1">
          <tr>
            <td width="170"><input name="fieldTasks" type="text" value="<?php echo $row_tasks['description']; ?>" /></td>
            <td width="314"><input name="buttonDeleteTasks" type="button" value="delete"/></td>
          </tr>
        </table>
        <?php } while ($row_tasks = mysql_fetch_assoc($tasks)); ?>
  </form>
</div>
<br />

Completed Tasks<br />
<div id="taskArchive">
 <form id="formTasksArchive" name="formTasksArchive" method="post" action="">
  <?php do { ?>
    <table width="500" border="1">
      <tr>
        <td width="170"><input name="fieldTasksArchive" type="text" value="<?php echo $row_archived['description']; ?>" /></td>
        <td width="314"><input name="fieldDateArchive" type="text" /></td>
      </tr>
    </table>
    <?php } while ($row_archived = mysql_fetch_assoc($archived)); ?>
 </form>
</div>

---

hmmm... unless I am misunderstanding, it seems you are making things too complicated. Why not use js to update the table every time the form is submitted, something like the below...?

Code:

---

<html>
<body>
New Task<br />
<div id="newTask">
  <form id="formNewTask" name="formNewTask" method="POST" action="<?php echo $editFormAction; ?>">
    <table width="500" border="1">
        <tr>
            <td width="173"><input name="fieldNewTask" type="text" /></td>
            <td width="311"><input name="buttonNewTask" type="button" onclick="addTask(this.form)" value="add"/></td>
        </tr>
    </table>
    <input type="hidden" name="MM_insert" value="formNewTask" />
  </form>
</div>
<br />

Task List<br />
<div id="tasks">
        <form id="formTasks" name="formTasks" method="post" action="">
      <?php do { ?>
        <table id="taskstab" width="500" border="1">
          <tr>
            <td width="170"><input name="fieldTasks" type="text" value="<?php echo $row_tasks['description']; ?>" /></td>
            <td width="314"><input name="buttonDeleteTasks" type="button" value="delete"/></td>
          </tr>
        </table>
        <?php } while ($row_tasks = mysql_fetch_assoc($tasks)); ?>
  </form>
</div>
<br />

Completed Tasks<br />
<div id="taskArchive">
 <form id="formTasksArchive" name="formTasksArchive" method="post" action="">
  <?php do { ?>
    <table width="500" border="1">
      <tr>
        <td width="170"><input name="fieldTasksArchive" type="text" value="<?php echo $row_archived['description']; ?>" /></td>
        <td width="314"><input name="fieldDateArchive" type="text" /></td>
      </tr>
    </table>
    <?php } while ($row_archived = mysql_fetch_assoc($archived)); ?>
 </form>
</div>
<script>
function addTask(frm){
var thetab=document.getElementById("taskstab");
var row=thetab.insertRow(thetab.rows.length);
row.innerHTML='<td width="170"><input name="fieldTasksArchive" type="text" value="'+frm.fieldNewTask.value+'" /></td>'
        +'<td width="314"><input name="buttonDeleteTasks" type="button" value="delete"/></td>'
frm.submit();
}
</script>


</body>
</html>

---

oh, sorry - ha,ha. I totally misunderstood your question. Which is the "archived" field?

Sorry, I missed some info out when I simplified the code.

In the first form there was a hidden field <input name="archivedNew" type="hidden" id="archivedNew" value="no" />

So when the task is created 'no' goes into the column 'archived', then is pulled in via recordset 'tasks'

in the second form I had a hidden field <input name="archiveStatus" type="hidden" id="archiveStatus "value="yes" />

so when delete button is clicked, it updated 'archived' to 'yes' and pulled the record via recordset 'archived'

thanks :)

confused, sorry. can you post your html with the hidden fields included?

Yeah here's the code. Only difference is there's a hidden field in first and second form, which is used to update the 'archived' column from No to Yes.

<body>

New Task<br />
<div id="newTask">
<form id="formNewTask" name="formNewTask" method="POST" action="<?php echo $editFormAction; ?>">
<table width="500" border="1">
<tr>
<td width="173"><input name="fieldNewTask" type="text" /></td>
<td width="311"><input name="buttonNewTask" type="submit" value="add"/></td>
</tr>
</table>
<input name="archivedNew" type="hidden" id="archivedNew" value="no" />
<input type="hidden" name="MM_insert" value="formNewTask" />
</form>
</div>
<br />

Task List<br />
<div id="tasks">
<form id="formTasks" name="formTasks" method="post" action="">
<?php do { ?>
<table width="500" border="1">
<tr>
<td width="170"><input name="fieldTasks" type="text" value="<?php echo $row_tasks['description']; ?>" /></td>
<td width="314"><input name="buttonDeleteTasks" type="button" value="delete"/></td>
</tr>
</table>
<input name="archiveStatus" type="hidden" id="archiveStatus" value="yes" />
<?php } while ($row_tasks = mysql_fetch_assoc($tasks)); ?>
</form>
</div>
<br />

Completed Tasks<br />
<div id="taskArchive">
<form id="formTasksArchive" name="formTasksArchive" method="post" action="">
<?php do { ?>
<table width="500" border="1">
<tr>
<td width="170"><input name="fieldTasksArchive" type="text" value="<?php echo $row_archived['description']; ?>" /></td>
<td width="314"><input name="fieldDateArchive" type="text" /></td>
</tr>
</table>
<?php } while ($row_archived = mysql_fetch_assoc($archived)); ?>
</form>
</div>

</body>

sorry, man... I'm still struggling. You add a task, it goes from the first form to the second form. If you delete a task from the second form it should go to the third form?

And if you add a task, its value should change from no to yes? If it gets created on the spot, why doesn'ty it just get created with a value "yes"?

Anyway. I recreated the above using javascript only (without the yes/no thing, which I sense is what your question is actually about). Maybe you can have a look at it and see if we're on the same page...

Code:

---

<!DOCTYPE html>
<html>
<body>
New Task<br />
<div id="newTask">
<form id="formNewTask" name="formNewTask" method="POST" action="<?php echo $editFormAction; ?>">
<table width="500" border="1">
<tr>
<td width="173"><input name="fieldNewTask" type="text" /></td>
<td width="311"><input name="buttonNewTask" type="button" onclick="addTask(this.form)" value="add"/></td>
</tr>
</table>
<input name="archivedNew" id="archivedNew" value="no" />
<input type="hidden" name="MM_insert" value="formNewTask" />
</form>
</div>
<br />

Task List<br />
<div id="tasks">
<form id="formTasks" name="formTasks" method="post" action="">
<?php do { ?>
<table id="taskstab" width="500" border="1">
<tr>
<td width="170"><input name="fieldTasks" type="text" value="<?php echo $row_tasks['description']; ?>" /></td>
<td width="314"><input name="buttonDeleteTasks" type="button" onclick="moveIt(this)" value="delete"/></td>
</tr>
</table>
<input name="archiveStatus" id="archiveStatus" value="yes" />
<?php } while ($row_tasks = mysql_fetch_assoc($tasks)); ?>
</form>
</div>
<br />

Completed Tasks<br />
<div id="taskArchive">
<form id="formTasksArchive" name="formTasksArchive" method="post" action="">
<?php do { ?>
<table id="comp" width="500" border="1">
<tr>
<td width="170"><input name="fieldTasksArchive" type="text" value="<?php echo $row_archived['description']; ?>" /></td>
<td width="314"><input name="fieldDateArchive" type="text" /></td>
</tr>
</table>
<?php } while ($row_archived = mysql_fetch_assoc($archived)); ?>
</form>
</div>
<script>
function addTask(frm){
var thetab=document.getElementById("taskstab");
var row=thetab.insertRow(thetab.rows.length);
var cell=document.createElement("td");
cell.width=170;
var inp=document.createElement("input");
inp.name="fieldTasksArchive";
inp.type="text";
inp.value=frm.fieldNewTask.value;
cell.appendChild(inp);
row.appendChild(cell);
var cell=document.createElement("td");
cell.width=314;
var inp=document.createElement("input");
inp.name="fieldDateArchive";
inp.type="button";
inp.value="delete";
inp.onclick=function(){moveIt(this)}
cell.appendChild(inp);
row.appendChild(cell);
//frm.submit()
}

function moveIt(btn){
var prev=document.body.previousElementSibling?"previousElementSibling":"previousSibling";
var thefield=btn.parentNode[prev].children[0];
var theval=thefield.value;
var thetab=document.getElementById("comp");
var row=thetab.insertRow(thetab.rows.length);
var cell=document.createElement("td");
cell.width=170;
var inp=document.createElement("input");
inp.name="fieldTasksArchive";
inp.type="text";
inp.value=theval;
cell.appendChild(inp);
row.appendChild(cell);
var cell=document.createElement("td");
cell.width=314;
var inp=document.createElement("input");
inp.name="fieldDateArchive";
inp.type="text";
cell.appendChild(inp);
row.appendChild(cell);
thefield.value="";        //to just clear the task list input box value
document.getElementById("taskstab").deleteRow(btn.parentNode.parentNode.rowIndex)        //to completely remove the row
}
</script>


</body>
</html>

---

Sorry, maybe I'm overcomplicating things

form1 inserts new tasks into the database, with 'archived' column set to 'no', hence hidden field. Form2 displays all records who's 'archived' value is 'no' (so all active tasks). If a task is deleted from form2, I need the 'archived' field in the database to update to 'yes', hence second hidden field. Then form3 (which actually doesn't need to be a form) displays all archived tasks (so all tasks who's 'archived' status has been set to 'yes').

cheers

Maybe this helps...

Attachment 15085
Attachment 15087

Actually, your last example works great. I can remove the stuff I didn't need. Nice one dude, thanks for your help :)

ha! cool. I'm still completely lost, but I'm glad it all worked out...