This is a draft copy of how Tripolar AJAX works.

Single Database Table, Single Row:

This is the most simple form. It is used when your HTML table displays a single set of fields updating a single row in the given database table.

EXAMPLE HTML:

<form method="post">
<input type="hidden" id="table" value="assembly.id.1">
 <table>
  <tr bgcolor="#aaaaaa">
   <th>assembly number</th>
   <th>description</th>
   <th>qty</th>
  </tr>
  <tr>
   <td><input type="text" id="assembly.assemblynumber" attrib="unique" value="1111"></td>
   <td><input type="text" id="assembly.assemblydescription" value="bbbb"></td>
   <td><input type="text" id="assembly.assemblyqty" value="7"></td>
  </tr>
 </table>
</form>

WHAT YOU SEE IN THE BROWSER:

assembly number description qty

Description:

  1. Changes update the database table named 'assembly'.
  2. Changes update the record "WHERE ID='1'" within that table.
  3. Hidden field with id="table" contains "tablename.idcolname.rowid".
  4. Input tag "id" contains "tablename.columnname".
  5. Optional attribute "unique" identifies unique column for the table.

Validations:

  1. Rowid value cannot be empty; it must contain 0 or greater.
  2. You can have no more than 1 unique field per table.
  3. If "unique" exists, you cannot insert secondary columns until the unique field has data.
  4. The appropriate SQL error will be returned if the columnname is invalid.

Single Database Table, Multiple Rows:

This is used when you have multiple rows of HTML data updating multiple rows within a single database table.

EXAMPLE HTML:

<form method="post">
<input type="hidden" id="table" value="assemblydetail.id.?">
 <table>
  <tr bgcolor="#aaaaaa">
   <th>item</th>
   <th>description</th>
   <th>qty</th>
  </tr>
  <tr>
   <td><input type="text" id="assemblydetail.itemnumber.1" attrib="unique" value="1" size="2"></td>
   <td><input type="text" id="assemblydetail.itemdescription.1" value="bbbb"></td>
   <td><input type="text" id="assemblydetail.itemqty.1" value="5" size="2"></td>
  </tr>
  <tr>
   <td><input type="text" id="assemblydetail.itemnumber.2" attrib="unique" value="2" size="2"></td>
   <td><input type="text" id="assemblydetail.itemdescription.2" value="dddd"></td>
   <td><input type="text" id="assemblydetail.itemqty.2" value="10" size="2"></td>
  </tr>
  <tr>
   <td><input type="text" id="assemblydetail.itemnumber.0" attrib="unique" value="" size="2"></td>
   <td><input type="text" id="assemblydetail.itemdescription.0" value=""></td>
   <td><input type="text" id="assemblydetail.itemqty.0" value="" size="2"></td>
  </tr>
 </table>
</form>

WHAT YOU SEE IN THE BROWSER:

item description qty

Description:

  1. The "?" represents multiple values identified on a per-row basis.
  2. Hidden field with id="table" contains "tablename.idcolname.?".
  3. Input tag "id" contains "tablename.columnname.rowid".
  4. Changes update the database table named 'assemblydetail'.
  5. Changes in row 1 or 2 'UPDATE' the records "WHERE ID='1'" or '2' within that table.
  6. Changes in row 3 will 'INSERT' a new record into that table.
  7. Optional attribute "unique" identifies unique column for the table.

Validations:

  1. Rowid value cannot be empty; it must contain 0 or greater.
  2. You can have no more than 1 unique field per table.
  3. If "unique" exists, you cannot insert secondary columns until the unique field has data.

Database Table Has Parent Join:

This is used when your database table is joined to a parent. That is, new inserts require reference to a parent table. There are two forms of this relationship: Dependent, and Independent. Note that parent tables can receive updates, but not inserts.
  1. Dependent Child: the join key is in the parent table.
  2. Independent Child: the join key is in the child table.

EXAMPLE HTML:

<form method="post">
<!-- 'dependent' example: -->
<input type="hidden" id="parent" value="assembly.id.1.assemblydetailid.?">
<input type="hidden" id="table" value="assemblydetail.id.?">
<!-- 'independent' example:
<input type="hidden" id="parent" value="assembly.id.1">
<input type="hidden" id="table" value="assemblydetail.id.?.assemblyid.1"> -->
 <table>
  <tr bgcolor="#aaaaaa">
   <th>assembly number</th>
   <th>description</th>
  </tr>
  <tr>
   <td><input type="text" id="assembly.assemblynumber" value="1111"></td>
   <td><input type="text" id="assembly.assemblydescription" value="qqqq" size="50"></td>
  </tr>
 </table>
 <br>
 <table>
  <tr bgcolor="#aaaaaa">
   <th>item</th>
   <th>description</th>
   <th>qty</th>
  </tr>
  <tr>
   <td><input type="text" id="assemblydetail.itemnumber.1" attrib="unique" value="1" size="2"></td>
   <td><input type="text" id="assemblydetail.itemdescription.1" value="bbbb"></td>
   <td><input type="text" id="assemblydetail.itemqty.1" value="5" size="2"></td>
  </tr>
  <tr>
   <td><input type="text" id="assemblydetail.itemnumber.2" attrib="unique" value="2" size="2"></td>
   <td><input type="text" id="assemblydetail.itemdescription.2" value="dddd"></td>
   <td><input type="text" id="assemblydetail.itemqty.2" value="10" size="2"></td>
  </tr>
  <tr>
   <td><input type="text" id="assemblydetail.itemnumber.0" attrib="unique" value="" size="2"></td>
   <td><input type="text" id="assemblydetail.itemdescription.0" value=""></td>
   <td><input type="text" id="assemblydetail.itemqty.0" value="" size="2"></td>
  </tr>
 </table>
</form>

WHAT YOU SEE IN THE BROWSER:

assembly number description

item description qty

Description:

  1. The "?" represents multiple values identified on a per-row basis.
  2. Changes in the first table update the database table 'assembly'.
  3. Changes in the second table update the database table 'assemblydetail'.
  4. Changes in 2nd table, row 1 or 2 'UPDATE' the records "WHERE ID='1'" or '=2'.
  5. (Dependent) Changes in row 3 will 'INSERT' a new assembly record with "(ASSEMBLYDETAILID) VALUE ('?')", where "?" is the current rowid.
  6. (Dependent) Hidden field with id="parent" contains "tablename.idcolname.parentrowid.joincolname.?".
  7. (Dependent) Hidden field with id="table" contains "tablename.idcolname.?".
  8. (Independent) Changes in row 3 will 'INSERT' a new assemblydetail record with "(ASSEMBLYID) VALUE ('1')".
  9. (Independent) Hidden field with id="parent" contains "tablename.idcolname.parentrowid".
  10. (Independent) Hidden field with id="table" contains "tablename.idcolname.?.joincolname.parentrowid".
  11. Parent input tag "id" contains "tablename.columnname".
  12. Child input tag "id" contains "tablename.columnname.rowid".
  13. Optional attribute "unique" identifies unique column for the table.

Validations:

  1. "parent" value must contain "tablename.idcol.parentrowid". (".joincolname.?" is optional.)
  2. Parentrowid must be greater than 0. 0 is not allowed for parent records.
  3. Because "parent" exists, "table" value must contain "tablename.idcol.?" or "tablename.idcolname.joincolname.parentrowid".
  4. Rowid value cannot be empty; it must contain 0 or greater.
  5. You can have no more than 1 unique field per table.
  6. If "unique" exists, you cannot insert secondary columns until the unique field has data.

Database Table Has Child Join:

This is used when your database table is joined to a child. That is, new inserts will have key reference to a child table. Note that both tables can receive updates or inserts.
  1. Dependent Child: the join key is in the parent table.
  2. Independent Child: the join key is in the child table.

EXAMPLE HTML:

<form method="post">
<input type="hidden" id="table" value="assemblydetail.id.?.partid.?">
<input type="hidden" id="child" value="part.id.?">
<!-- independent example
<input type="hidden" id="table" value="assemblydetail.id.?">
<input type="hidden" id="child" value="part.id.?.assemblydetailid.?"> -->
 <table>
  <tr bgcolor="#aaaaaa">
   <th>item</th>
   <th>qty</th>
   <th>part number</th>
   <th>part description</th>
  </tr>
  <tr>
   <td><input type="text" name="assemblydetail.itemnumber.1" attrib="unique" value="1" size="2"></td>
   <td><input type="text" name="assemblydetail.itemqty.1" value="5" size="2"></td>
   <td><input type="text" name="part.partnumber.1.1" attrib="unique" value="cccc"></td>
   <td><input type="text" name="part.partdescription.1.1" value="dddd"></td>
  </tr>
  <tr>
   <td><input type="text" name="assemblydetail.itemnumber.2" attrib="unique" value="2" size="2"></td>
   <td><input type="text" name="assemblydetail.itemqty.2" value="10" size="2"></td>
   <td><input type="text" name="part.partnumber.2.3" attrib="unique" value="gggg"></td>
   <td><input type="text" name="part.partdescription.2.3" value="hhhh"></td>
  </tr>
  <tr>
   <td><input type="text" name="assemblydetail.itemnumber.0" attrib="unique" value="" size="2"></td>
   <td><input type="text" name="assemblydetail.itemqty.0" value="" size="2"></td>
   <td><input type="text" name="part.partnumber.0.0" attrib="unique" value=""></td>
   <td><input type="text" name="part.partdescription.0.0" value=""></td>
  </tr>
 </table>
</form>

WHAT YOU SEE IN THE BROWSER:

item qty part number part description

Description:

  1. The "?" represents multiple values identified on a per-row basis.
  2. Changes update the database tables named 'assemblydetail' and 'part'.
  3. Changes in the first 2 cells of row 1 'UPDATE' assemblydetail "WHERE assemblydetail.ID='1'".
  4. Changes in the next 2 cells of row 1 'UPDATE' part "WHERE part.ID='1'".
  5. Changes in row 2 work the same as row 1.
  6. Changes in row 3 'INSERT' two new records, one for each table.
  7. (Dependent) The SQL INSERT includes column 'assemblydetail.partid' as needed.
  8. (Dependent) Hidden field with id="table" contains "tablename.idcolname.?.joincolname.?".
  9. (Dependent) Hidden field with id="child" contains "tablename.idcolname.?".
  10. (Independent) The SQL INSERT includes column 'part.assemblydetailid' as needed.
  11. (Independent) Hidden field with id="table" contains "tablename.idcolname".
  12. (Independent) Hidden field with id="child" contains "tablename.idcolname.?.joincolname.?".
  13. If the child is dependent, joincolname is the name of a column in "table".
  14. If the child is independent, joincolname is the name of a column in "child".
  15. Ordinary input tag "id" contains "tablename.columnname.rowid".
  16. Child input tag "id" contains "tablename.columnname.rowid.childrowid".
  17. Optional attribute "unique" identifies unique column for the table.

Validations:

  1. For new rows, a child cannot be inserted until its parent has data.
  2. Rowid value cannot be empty; it must contain 0 or greater.
  3. Childrowid value cannot be empty; it must contain 0 or greater.
  4. You can have no more than 1 unique field per database table.
  5. If unique exists, you cannot insert secondary columns until the unique field has data.

Database Table has Parent and Child Joins:

This is the most complex relationship. This is used when you have fields updating any of three tables. Parent table record must already exist. "Table" is a child of "Parent", and "Child" is a child of "table". In other words, the relationship goes like this: Parent [is the parent of] Table [is the parent of] Child.
  1. Dependent Child: the join key is in the parent table.
  2. Independent Child: the join key is in the child table.
Note: For simplicity's sake, the example shows both Parent and Child in Dependent relationships. Independent relationships work as described above.

EXAMPLE HTML:

<form method="post">
<input type="hidden" id="parent" value="assembly.id.1.assemblydetailid.?">
<input type="hidden" id="table" value="assemblydetail.id.?.partid.?">
<input type="hidden" id="child" value="part.id.?">
 <table>
  <tr bgcolor="#aaaaaa">
   <th>item</th>
   <th>qty</th>
   <th>part number</th>
   <th>part description</th>
  </tr>
  <tr>
   <td><input type="text" name="assemblydetail.itemnumber.1" attrib="unique" value="1" size="2"></td>
   <td><input type="text" name="assemblydetail.itemqty.1" value="5" size="2"></td>
   <td><input type="text" name="part.partnumber.1.1" attrib="unique" value="cccc"></td>
   <td><input type="text" name="part.partdescription.1.1" value="dddd"></td>
  </tr>
  <tr>
   <td><input type="text" name="assemblydetail.itemnumber.2" attrib="unique" value="2" size="2"></td>
   <td><input type="text" name="assemblydetail.itemqty.2" value="10" size="2"></td>
   <td><input type="text" name="part.partnumber.2.3" attrib="unique" value="gggg"></td>
   <td><input type="text" name="part.partdescription.2.3" value="hhhh"></td>
  </tr>
  <tr>
   <td><input type="text" name="assemblydetail.itemnumber.0" attrib="unique" value="" size="2"></td>
   <td><input type="text" name="assemblydetail.itemqty.0" value="" size="2"></td>
   <td><input type="text" name="part.partnumber.0.0" attrib="unique" value=""></td>
   <td><input type="text" name="part.partdescription.0.0" value=""></td>
  </tr>
 </table>
</form>

WHAT YOU SEE IN THE BROWSER:

item qty part number part description

Description:

  1. The "?" represents multiple values identified on a per-row basis.
  2. Changes update the database tables named 'assembly', 'assemblydetail', and 'part'.
  3. Changes in the first 2 cells of row 1 'UPDATE' assemblydetail "WHERE assemblydetail.ID='1'".
  4. Changes in the next 2 cells of row 1 'UPDATE' part "WHERE part.ID='1'".
  5. Changes in row 2 work the same as row 1.
  6. Changes in row 3 'INSERT' as many as three new records, one for each table.
  7. The SQL INSERT includes column 'assembly.assemblydetailid' as needed.
  8. The SQL INSERT includes column 'assemblydetail.partid' as needed.
  9. Hidden field with id="parent" contains "tablename.idcolname.parentrowid.joincolname.?".
  10. Hidden field with id="table" contains "tablename.idcolname.?.joincolname.?".
  11. Hidden field with id="child" contains "tablename.idcolname.?".
  12. Joincolname is the name of a column in the preceding parent table.
  13. Ordinary input tag "id" contains "tablename.columnname.rowid".
  14. Child input tag "id" contains "tablename.columnname.rowid.childrowid".
  15. Optional attribute "unique" identifies unique column for the table.

Validations:

The appropriate validations for "parent" and "child" above are used.

Miscellaneous Notes

  • When a unique field changes, the page updates and cursor moves into next field.
  • Changes update the database as soon as INPUT focus blurs.
  • Although the form method is 'post', the form will not actually post because form INPUTs have no 'name' attributes.
  • The form has no 'submit' button because cells update automatically via AJAX when focus changes.
  • If the row is new (i.e. SQL 'INSERT'), rowid is set to 0.
  • If the row is already in database (i.e. SQL 'UPDATE'), rowid is set to >0.