Form with table/spreadsheet inputs?

[DKY]

I'm curious if it's possible to have a form with a table spreadsheet type of layout. I'm trying to reproduce this form that I have in Microsoft Access and wanted to see if anyone had any ideas, this form allows you to enter data and then if you close out the form and come back it still has your data in it that you entered in last time. Attached is a few screenshots. Thanks in advance!

[ATTACH]4126[/ATTACH]

as you can see in a partially filled out screen shot one can fill in information and doesn't need to submit for each row.

[ATTACH]4127[/ATTACH]

[ixalmida]

Reproducing the form is doable (example here or here). Or did I not understand the question?

[DKY]

Maybe I'm just not understanding the links that you've provided but I'm looking for a tabular form that one can fill out and submit.

[ixalmida]

It's hard to find a single tutorial that takes you from start to end; most are in pieces. That said, it shouldn't be too hard to imagine how the process would go:
- Style a table and form in CSS to mimic your Access form
- Create a container page
- Create an inner (Ajax) input page with one table row of text boxes (or use PHP to populate based on existing values)
- Create a Javascript function to add rows (note differences across browsers)
- Create an AJAX function to save a row (to whatever you use for temp storage...SQL, XML file, JML file, etc.)
- Create an AJAX function to delete a row
- Create a submit page to commit data to permanent storage (table, file, etc.) from temp storage

There may be some other considerations, but it looks something like that.

If you're looking for something ready-made, I've run across a few in the past. I believe there's an Adobe product that can do it (Flash-based?), and I've seen some Javascript/frames-based/AJAX solutions you can purchase, but they pretty much amount to the same thing as I outlined above.

[DKY]

okay, step one

<table>
<tr>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr><tr>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr><tr>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr><tr>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr><tr>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr><tr>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr>
</table>

I get lost after that, what is a container page?

[johanafm]

DKY;10961170 wrote:

<annoying stuff - snipped>
I get lost after that, what is a container page?

All the rest. It's what you get when you do what you should, or when you pay someone to do it for you.

[DKY]

People have different learning styles, I learn by asking questions. Personally I believe that this is the point of a forum board. Am I missing something?

[johanafm]

I just interpreted the huge amount of empty tds as spam, since they do little on their own, and after the first set provides no additional information. The thing he calls "container page" is what contains the table.

[ixalmida]

I meant that the table would be contained in an IFRAME or DIV. The "container" page isn't strictly necessary, but it would help anchor the page so the process is seamless to the user.

[DKY]

So I know how to create a while statement in mysql/php that will create input boxes and tables based upon the data in the table but how do I create additional fields so that they never run out? does that make sense?

[ixalmida]

You'd need a Javascript "addRow()" function. In it, you would have a ready-made row that you can append to the table. Again, this can be tricky across browser, so do some research.

[DKY]

and this function will add a row whenever a user ends another row? I think the example was in one of the links but it was just code so I couldn't see functionality. Thanks!

[ixalmida]

Sort of. You'd have to tie the Javascript function to an event, but the event could be pressing a button or typing something into an empty cell. It is really up to you.

[DKY]

Okay, I'll play with it and see what happens. Thanks for the direction!

[ixalmida]

Just to get you started in the right direction, here's some code I use to add rows to a table/form:

  function addRow()
    {
    var tbl = document.getElementById('table_id');
    var lastRow = tbl.rows.length;

// Add row...
var row = tbl.insertRow(-1);

// Equip type..
var cellA = row.insertCell(0);
var eqSel = document.createElement('select');
selBox.name = 'select_box_name[]';";

<?php
// Get drop-down values from db...
$query = "SELECT field_id, field_name FROM MyTable";
$result = mysql_query($query, $link);
$x = 0;
while($row = mysql_fetch_assoc($result))
  {
  extract($row);
  echo "    sexBox.options[".$x."] = new Option('$field_name', '$field_id');\n";
  $x++;
  }
?>

cellA.appendChild(eqSel);

// Field 2...
var cellB = row.insertCell(1);
var field2 = document.createElement('input');
field2.type = 'text';
field2.name = 'field2[]';
field2.size = '20';
field2.maxlength = '20';
cellB.appendChild(field2);

// etc...

[DKY]

Hey, I'm back and trying to take another stab at developing this. I've got the column adding thing down now with a link that says 'add column' at the bottom of the page but I'm trying to make this page a page where if the user closed out their window they don't have to worry about whether or not they saved/submitted the data.

How do I make it seem as if they're working with a live spreadsheet? My problem is that there is no primary key within the information that they're submitting. I do have an auto_increment value on the MySQL table but how do I call it back the second I create it?

[ixalmida]

You can't reliably update a table that doesn't have a primary key, so there needs to be one, so I'm assuming there is. In your form, you would generally place a hidden (or not hidden, in some cases) ID field for each row. When the user edits a cell of the table (actually a form field within a cell in the table), then you can take advantage of the onblur() event and update the table via an Ajax function.

The key to all of this is to make sure you understand how to grab row values. The cell's value is easy - you just pass "this" to the Javascript function (i.e., onblur="myFunction('this')"). The value of a text field is simply "this.value". The value of a drop-down box is "this.options[this.selectedIndex].value".

The most elegant solution to access your hidden index would be to use the rowIndex property of the table row. Bear in mind that the form field is in a cell within the row, therefore you need to refer to the field's parentNode's (cell) parentNode (row).

Try this example and see what happens:

<html>
<head>
<script type="text/javascript">
  function alertRowIndex(obj)
  {
    // Get row index
    var i = obj.parentNode.parentNode.rowIndex;
    var myIndex = document.getElementsByName('myindex').item(i);
    alert(myIndex.value);
  }
</script>
</head>

<body>
<table border="1">
<tr>
  <td>
    <input type='hidden' name='myindex' value='You clicked 1' />
    <input type='button' name='btn1' value='click me1' onClick='alertRowIndex(this)' />
  </td>
</tr>
<tr>
  <td>
    <input type='hidden' name='myindex' value='You clicked 2' />
    <input type='button' name='btn1' value='click me2'
onClick='alertRowIndex(this)' />
  </td>
</tr>
</table>

</body>
</html>

You should be able to adapt this example to fit your needs.

[DKY]

Ahh, good direction. I'll play with it and let you know. Right now I'm struggling with several things, the AJAX especially as I'm using AJAX right now to fill in the first cell with the results from the MySQL db where the part=the value entered in the third cell. I've got that working great but now can't figure out how to return another field from my table and populate the second cell... Additionally how would I do this when it comes to the second row since the javascript that I'm using to add rows is recreating all the cell/input names... UGH! I don't know if this is something that you can help with or if this is strictly a PHP forum... Let me know if you want me to post my code thus far. Thanks for all your help!

[ixalmida]

When I've had several Ajax tasks to perform, I've found it best to "chain" Ajax functions so you don't have one function firing before the previous is done. This is especially important when a second function depends on the result of the first.

<script language="javascript">
function myFunction(obj)
{
  var result;
  try
    {
     result = doTask1(obj);
     }
  catch(err) 
    {
    return err.description;
    }
}

function doTask1(obj)
{
  var myId = obj.value;
  var url = "task1.php?id=" + myId;
  ajaxObj.open('get', url, true);
  ajaxObj.setRequestHeader('Content-type', 'application/x-www-form-urlencoded');
  ajaxObj.onreadystatechange = function ()
   {
    if(ajaxObj.readyState == 4 || ajaxObj.readyState == 'complete')
      {
      doTask2(obj);
      }
    }
ajaxObj.send(null);
}

function doTask2(obj)
{
  var myId = obj.value;
  var url = "task2.php?id=" + myId;
  ajaxObj.open('get', url, true);
  ajaxObj.setRequestHeader('Content-type', 'application/x-www-form-urlencoded');
  ajaxObj.onreadystatechange = function ()
   {
    if(ajaxObj.readyState == 4 || ajaxObj.readyState == 'complete')
      {
      return "Success";
      }
    }
ajaxObj.send(null);
}

On the other hand, it is probably best to fit all of your processing into a single Ajax script and return values in XML/JML/whatever. But that's another discussion.

** Note: the above code is not "ready to use", as I left out how to get the "ajaxObj" and didn't really flesh out the error handling. Use only as an example.

[DKY]

Okay so how do I tell this to take the row index into account

function showSTD(str)
{
if (str=="")
  {
  document.getElementById("txtHint").innerHTML="";
  return;
  } 
if (window.XMLHttpRequest)
  {// code for IE7+, Firefox, Chrome, Opera, Safari
  xmlhttp=new XMLHttpRequest();
  }
else
  {// code for IE6, IE5
  xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
  }
xmlhttp.onreadystatechange=function()
  {
  if (xmlhttp.readyState==4 && xmlhttp.status==200)
    {
    document.getElementById("txtHint").innerHTML=xmlhttp.responseText;
    if(xmlhttp.readyState == 4 || xmlhttp.readyState == 'complete')
      {
      showSST(str);
      }
    }
  }
xmlhttp.open("GET","AJ_PartSTD.php?q="+str,true);
xmlhttp.send();
}

function showSST(str)
{
if (str=="")
  {
  document.getElementById("txtHint2").innerHTML="";
  return;
  } 
if (window.XMLHttpRequest)
  {// code for IE7+, Firefox, Chrome, Opera, Safari
  xmlhttp=new XMLHttpRequest();
  }
else
  {// code for IE6, IE5
  xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
  }
xmlhttp.onreadystatechange=function()
  {
  if (xmlhttp.readyState==4 && xmlhttp.status==200)
    {
    document.getElementById("txtHint2").innerHTML=xmlhttp.responseText;
    }
  }
xmlhttp.open("GET","AJ_PartSST.php?q="+str,true);
xmlhttp.send();
}

Because it works on my inputs in my first row only

ECHO "<TR><TD WIDTH='" . $STD_ColWidth . "' style='border-left: solid #000000 1px; border-bottom: solid #000000 1px; text-align:center; background-color:#E0E0E0'><div id='txtHint' style='font-family:Tahoma,Arial,Helvetica,Geneva,sans-serif; font-size:small; text-align:center; color:#616161;'>&nbsp;</div></TD>\n";
			ECHO "<TD WIDTH='" . $SST_ColWidth . "' style='border-left: solid #000000 1px; border-bottom: solid #000000 1px; text-align:center; background-color:#E0E0E0'><div id='txtHint2' style='font-family:Tahoma,Arial,Helvetica,Geneva,sans-serif; font-size:small; text-align:center; color:#616161;'>&nbsp;</div></TD>\n";
			ECHO "<TD WIDTH='" . $NAV_PN_ColWidth . "' style='border-left: solid #000000 1px; border-bottom: solid #000000 1px; text-align:center;'><input name='nav_pn' type='text' value='".$RowNAV_PN."' size='12' style='font-family:Tahoma,Arial,Helvetica,Geneva,sans-serif; font-size:small; text-align:center; border: 0 none;' onchange='showSTD(this.value)' onblur='this.value=this.value.toUpperCase()'/></TD>\n";