title: AJAX update to multiple database tables
body: <p>I have an ajax table that reads data from two mysql tables, the tables are </p>
  
  <p>project_ownerships - id_own, project, code, own_current, own_future<br>
  projects - id, project, location, type, type2, area, transport, stage </p>
  
  <p>the data reads into the web page table fine, using a join table sql query. </p>
  
  <pre><code>$query_value = isset($_GET['value']) ? $_GET['value'] : ""; 
  $sql = "SELECT project_ownerships.*, projects.* 
         FROM project_ownerships, projects 
         WHERE project_ownerships.project = projects.project 
         AND project_ownerships.code = $query_value'";
  $result = $mysqli-&gt;query($sql);
  </code></pre>
  
  <p>however I can't get the edit update working properly. I am only able to get a <code>data - id</code> value from one db-table. </p>
  
  <p>so any updates that belong to <code>projects</code> table update fine, but any updates to <code>project_ownerships</code> do not have the correct id value and update the wrong db-record</p>
  
  <p>here's the update function. this function returns the error "column not found with index or name id_own"</p>
  
  <pre><code>    $.ajax({
      url: 'update_multi.php',
      type: 'POST',
      dataType: "html",
      data: {
          tablename: editableGrid.getColumnName(columnIndex) == "own_current" ? "project_ownerships" : "projects", 
          id: editableGrid.getColumnName(columnIndex) == "own_current" ? editableGrid.getValueAt(rowIndex, editableGrid.getColumn("id_own")) : editableGrid.getRowId(rowIndex), 
          newvalue: editableGrid.getColumnType(columnIndex) == "boolean" ? (newValue ? 1 : 0) : newValue,
          colname: editableGrid.getColumnName(columnIndex),
          coltype: editableGrid.getColumnType(columnIndex)
      },
      success: function (...,
      error: function(...,
      async: true
  });
  </code></pre>
  
  <p>here's the php update</p>
  
  <pre><code>$tablename = $mysqli-&gt;real_escape_string(strip_tags($_POST['tablename']));
  $id = $mysqli-&gt;real_escape_string(strip_tags($_POST['id']));
  $value = $mysqli-&gt;real_escape_string($_POST['newvalue']);
  $colname = $mysqli-&gt;real_escape_string(strip_tags($_POST['colname']));
  $coltype = $mysqli-&gt;real_escape_string(strip_tags($_POST['coltype']));
  
  $id_column = "";
  if ($tablename == "projects") {$id_column = "id";} else {$id_column = "id_own";}
  
  if ( $stmt = $mysqli-&gt;prepare("UPDATE ".$tablename." SET ".$colname." = ? WHERE ".$id_column." = ?")) {
  $stmt-&gt;bind_param("si",$value, $id); 
  $return = $stmt-&gt;execute();
  $stmt-&gt;close();
  }
  </code></pre>
  
  <p>basically what I am trying to do.... </p>
  
  <p>if <code>projects.id</code> is removed from the sql the update will not work</p>
  
  <p>if <code>project_ownership.id_own</code> is changed to <code>project_ownership.id</code> and <code>projects.id</code> is removed the update will work, but only with <code>project_ownership.*</code> fields</p>
  
  <p>so... I need a column in the sql query called <code>*.id</code></p>
  
  <p>separately, when an update is sent, the correct table name can be selected by </p>
  
  <pre><code>tablename: editableGrid.getColumnName(columnIndex) == "own_current" ? "project_ownerships" : "projects", 
  </code></pre>
  
  <p>so using the same logic</p>
  
  <pre><code>id: editableGrid.getColumnName(columnIndex) == "own_current" ? editableGrid.getValueAt(rowIndex, editableGrid.getColumn("id_own")) : editableGrid.getRowId(rowIndex), 
  </code></pre>
  
  <p>firstly, recognises that <code>own_current</code> is there, and passes the argument to <code>editableGrid.getValueAt(rowIndex, editableGrid.getColumn("id_own"))</code> but the error returned is "no column found with the <code>id_own</code> name"... ?</p>
  
  <p>I am really confused..</p>
  
  <p>it cannot not be there (removed from the sql) otherwise the update just freezes</p>
  
  <p>but when it is there it cannot be found...</p>
  
  <p>any help would be great</p>
  
  <p>how is it possible to define the <code>ajax - data - id</code> column or the rowIndex column ?</p>
  
  <p>I am working on the theory that</p>
  
  <p><code>editableGrid.getRowId(rowIndex)</code></p>
  
  <p>could be the something like (which it's obviously not otherwise this would work) </p>
  
  <p><code>editableGrid.getValueAt(rowIndex, editableGrid.getColumn("id_own"))</code></p>
  
  <p>but I have also tried, amongst others </p>
  
  <p><code>editableGrid.getValueAt(rowIndex, editableGrid.getColumnIndex("id_own"))</code> </p>
  
  <p>which returns "invalid column index -1"</p>
  
  <p>and </p>
  
  <p><code>editableGrid.getValueAt(rowIndex, editableGrid.getColumnName("id_own"))</code></p>
  
  <p><em>UPDATE</em></p>
  
  <p>there's a couple private functions in <code>editablegrid</code> that define what row id is. so I guess that makes it an editablegrid question and not really a javascript, ajax, or php question suitable for here</p>