AJAX update to multiple database tables

Question

I have an ajax table that reads data from two mysql tables, the tables are

project_ownerships - id_own, project, code, own_current, own_future
projects - id, project, location, type, type2, area, transport, stage

the data reads into the web page table fine, using a join table sql query.

$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->query($sql);

however I can't get the edit update working properly. I am only able to get a data - id value from one db-table.

so any updates that belong to projects table update fine, but any updates to project_ownerships do not have the correct id value and update the wrong db-record

here's the update function. this function returns the error "column not found with index or name id_own"

    $.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
});

here's the php update

$tablename = $mysqli->real_escape_string(strip_tags($_POST['tablename']));
$id = $mysqli->real_escape_string(strip_tags($_POST['id']));
$value = $mysqli->real_escape_string($_POST['newvalue']);
$colname = $mysqli->real_escape_string(strip_tags($_POST['colname']));
$coltype = $mysqli->real_escape_string(strip_tags($_POST['coltype']));

$id_column = "";
if ($tablename == "projects") {$id_column = "id";} else {$id_column = "id_own";}

if ( $stmt = $mysqli->prepare("UPDATE ".$tablename." SET ".$colname." = ? WHERE ".$id_column." = ?")) {
$stmt->bind_param("si",$value, $id); 
$return = $stmt->execute();
$stmt->close();
}

basically what I am trying to do....

if projects.id is removed from the sql the update will not work

if project_ownership.id_own is changed to project_ownership.id and projects.id is removed the update will work, but only with project_ownership.* fields

so... I need a column in the sql query called *.id

separately, when an update is sent, the correct table name can be selected by

tablename: editableGrid.getColumnName(columnIndex) == "own_current" ? "project_ownerships" : "projects",

so using the same logic

id: editableGrid.getColumnName(columnIndex) == "own_current" ? editableGrid.getValueAt(rowIndex, editableGrid.getColumn("id_own")) : editableGrid.getRowId(rowIndex),

firstly, recognises that own_current is there, and passes the argument to editableGrid.getValueAt(rowIndex, editableGrid.getColumn("id_own")) but the error returned is "no column found with the id_own name"... ?

I am really confused..

it cannot not be there (removed from the sql) otherwise the update just freezes

but when it is there it cannot be found...

any help would be great

how is it possible to define the ajax - data - id column or the rowIndex column ?

I am working on the theory that

editableGrid.getRowId(rowIndex)

could be the something like (which it's obviously not otherwise this would work)

editableGrid.getValueAt(rowIndex, editableGrid.getColumn("id_own"))

but I have also tried, amongst others

editableGrid.getValueAt(rowIndex, editableGrid.getColumnIndex("id_own"))

which returns "invalid column index -1"

and

editableGrid.getValueAt(rowIndex, editableGrid.getColumnName("id_own"))

UPDATE

there's a couple private functions in editablegrid 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

you have an injection vulnerability in the first bit of code you pasted. You should escape values that you're using in query strings. Either use PDO or mysql_real_escape_string — Eva, Jul 25 '12 at 00:52
as far as troubleshooting, just alert out the value of "editablegrid" or whatever to ensure that it's passing the correct value — Eva, Jul 25 '12 at 00:55
... you can also pass the entire editableGrid object to a php page and then do a print_r on $_POST to get a better idea of the structure — Eva, Jul 25 '12 at 00:56

score 1 · Answer 1 · answered Aug 09 '12 at 11:03

1

You should rename "id" column of each table in your SQL query with AS operator so they dont conflict.

For example :

SELECT B.*,A.name,A.id as author_id FROM BOOKS B
INNER JOIN AUTHORS A ON A.id=B.authorid

answered Aug 09 '12 at 11:03

Max Abrahamsson

1,460
3
17
35

AJAX update to multiple database tables

1 Answers1