value not editable when using anothe table than "demo"

[RaeAdvent]

Hi, first thing first... You got a very nice script for table management.

I recently read you documentation about this editable grid, and downloaded the files needed to test it.

Now i'm trying to use another table beside the provided table in the .rar archive.

Everything went normal, no connection issues. All data in mysql db loaded successfully. But when i try to change the value, it doesn't change at all, the value jusy returned back to it's original value.

I've tried to edit the update.php and demo.js according to my own table structure, like the "id" column are changed to "code" so when the data changes, it changes the value according to the "code" value in that row. But it seems not working at all, is there any solution about using this script for another database and table?

Thanks,

Rae.

[jybeaujean]

Could you send me your code ? I'll try to help you.

[RaeAdvent]

many thanks for your fast response...

well, heres my code:

Config.php

<?php $config = array( "db_name" => "vuu_db", "db_user" => "root", "db_password" => "", "db_host" => "localhost" );

error_reporting(E_ALL); ini_set('display_errors', '1');

?>

loaddata.php

<?php

require_once('config.php');
require_once('EditableGrid.php');

function fetch_pairs($mysqli,$query){ if (!($res = $mysqli->query($query)))return FALSE; $rows = array(); while ($row = $res->fetch_assoc()) { $first = true; $key = $value = null; foreach ($row as $val) { if ($first) { $key = $val; $first = false; } else { $value = $val; break; } } $rows[$key] = $value; } return $rows; }

$mysqli = mysqli_init(); $mysqli->options(MYSQLI_OPT_CONNECT_TIMEOUT, 5); $mysqli->>real_connect($config['db_host'],$config['db_user'],$config['db_password'],$config['db_name']);

$grid = new EditableGrid();

$grid->addColumn('code', 'code', 'interger', NULL, false); $grid->addColumn('nama', 'Nama', 'string');
$grid->addColumn('agama', 'Agama', 'string');
$grid->addColumn('alamat', 'Alamat', 'string' );
$grid->addColumn('tempat_lhr', 'Tempat Lahir', 'string');
$grid->addColumn('tgl_lhr', 'Tanggal Lahir', 'date');

$mydb_tablename = (isset($_GET['db_tablename'])) ? >stripslashes($_GET['db_tablename']) : 'tbl_data';

$result = $mysqli->query('SELECT *, date_format(tgl_lhr, "%d/%m/%Y") as >tgl_lhr FROM tbl_data LIMIT 100'); $mysqli->close();

$grid->renderXML($result);

update.php

<?php require_once('config.php');

$mysqli = mysqli_init(); $mysqli->options(MYSQLI_OPT_CONNECT_TIMEOUT, 5); $mysqli->>real_connect($config['db_host'],$config['db_user'],$config['db_password'],$config['db_name']);

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

// Here, this is a little tips to manage date format before update the table if ($coltype == 'date') { if ($value === "") $value = NULL; else { $date_info = date_parse_from_format('d/m/Y', $value); $value = "{$date_info['year']}-{$date_info['month']}-{$date_info['day']}"; } }

// i changed the query syntax from "id" to "code" $return=false; if ( $stmt = $mysqli->prepare("UPDATE ".$tablename." SET ".$colname." = ? WHERE code = ?")) { $stmt->bind_param("si",$value, $id); $return = $stmt->execute(); $stmt->close();

}
$mysqli->close();

echo $return ? "ok" : "error";

that's all the code that i modified so i can use it to another table beside "demo" table

everything is okay, data loaded, but i cannot edit the table contents, it always return to default. i noticed when the update process are successfull, the row blinked in green color, but mine is always blinked red.

for more information, this is my table structure :

tbl_data

Column	Type	Null
code	int(11)	No
nama	varchar(50)	No
agama	varchar(50)	No
alamat	varchar(200)	No
tempat_lhr	varchar(50)	No
tgl_lhr	date	No

and finally, the result :

*sorry i blurred the addresss and birthday.

i downloaded the editablegrid 2.0.1 version in download section

could it be related to demo.js and editablegrid.php?

thank you for your time :)

Updated

with a little help from your documentation, i finally found the solution for my problems.

to put it simple, just go to demo.js

and find this line :

function DatabaseGrid() { this.editableGrid = new EditableGrid("demo", { enableSort: true, tableLoaded: function() { datagrid.initializeGrid(this); }, modelChanged: function(rowIndex, columnIndex, oldValue, newValue, row) { updateCellValue(this, rowIndex, columnIndex, oldValue, newValue, row); } }); this.fetchGrid();

}

focus on this line :

this.editableGrid = new EditableGrid("demo", {

change the "demo" table to your own table, in my case, i change the table name into "tbl_data"

so the code line should be like this :

function DatabaseGrid() { this.editableGrid = new EditableGrid("tbl_data", { //change the table name to your table enableSort: true, tableLoaded: function() { datagrid.initializeGrid(this); }, modelChanged: function(rowIndex, columnIndex, oldValue, newValue, row) { updateCellValue(this, rowIndex, columnIndex, oldValue, newValue, row); } }); this.fetchGrid();

}

the editable grid should work flawlessly ;)

[jybeaujean]

No error in error_log ? If the cell is not updated, you should have an error.

[RaeAdvent]

nothing at all, now i realize there is a certain line i need to change according to my table in order to make the update process succeed.

i just have to change the primary key name, in your example, it's "id" is the primary key, but in my case, "code" is my primary key.

to put it simply, i just have to change a few lines that have the primary key "id" to my primary key "code" in update.php , demo.js and editablegrid.php

one more question if i may...

how to attach the editable grid in php pages instead?

i made a simple web application to manage a car club members

when i try to attach the js file straight into the php files, the table not shown. just a filter input form that exist(it's html tag obviously, so it must be showed)

[jybeaujean]

I'm not sure, but I have an idea when i'm reading this line :

id = $mysqli->real_escape_string(strip_tags($_POST['code']));

To be sure, print $_POST in update.php

error_log(print_r($_POST));

And at the end, print also the query

error_log("UPDATE ".$tablename." SET ".$colname." = ? WHERE code = ?")

Check the javascript function updateCellValue. The param "id" is sent to the php. Have you also changed there ?

[RaeAdvent]

no error occured, even after i add the print log you provided above.

yes, i changed all the "id" key to code,

in the _editablegrid.php : _

private function getRowXML($DOMDocument, $row, $customRowAttributes, $encodeCustomAttributes)
    {
        $rowNode = $DOMDocument->createElement('row');
        $rowNode->setAttribute('code', $this->_getRowField($row, 'code'));
        if ($customRowAttributes) foreach ($customRowAttributes as $name => $field) $rowNode->setAttribute($name, $encodeCustomAttributes ? base64_encode($this->_getRowField($row, $field)) : $this->_getRowField($row, $field));

        foreach ($this->columns as $name => $info) {
            $field = $info['field'];
            $rowNode->appendChild($columnNode = $DOMDocument->createElement('column'));
            if ($this->writeColumnNames) $columnNode->setAttribute('name', $name);
            $columnNode->appendChild($DOMDocument->createCDATASection($this->_getRowField($row, $field)));
        }

        return $rowNode;
    }

and this

private function getRowPOJO($row, $customRowAttributes, $encodeCustomAttributes)
    {
        $data = array("id" => $this->_getRowField($row, 'code'), "values" => array());
        if ($customRowAttributes) foreach ($customRowAttributes as $name => $field) $data[$name] = $encodeCustomAttributes ? base64_encode($this->_getRowField($row, $field)) : $this->_getRowField($row, $field);

        foreach ($this->columns as $name => $info) {
            $field = $info['field'];
            if ($this->writeColumnNames) $data["values"][$name] = $this->_getRowField($row, $field);
            else $data["values"][] = $this->_getRowField($row, $field);
        }

        return $data;
    }

for update process in _demo.js : _

function updateCellValue(editableGrid, rowIndex, columnIndex, oldValue, newValue, row, onResponse)
{      
    $.ajax({
        url: 'update.php',
        type: 'POST',
        dataType: "html",
            data: {
            tablename : editableGrid.name,
            code: editableGrid.getRowId(rowIndex), 
            newvalue: editableGrid.getColumnType(columnIndex) == "boolean" ? (newValue ? 1 : 0) : newValue, 
            colname: editableGrid.getColumnName(columnIndex),
            coltype: editableGrid.getColumnType(columnIndex)            
        },
        success: function (response) 
        { 
            // reset old value if failed then highlight row
            var success = onResponse ? onResponse(response) : (response == "ok" || !isNaN(parseInt(response))); // by default, a sucessfull reponse can be "ok" or a database id 
            if (!success) editableGrid.setValueAt(rowIndex, columnIndex, oldValue);
            highlight(row.id, success ? "ok" : "error"); 
        },
        error: function(XMLHttpRequest, textStatus, exception) { alert("Ajax failure\n" + errortext); },
        async: true
    });

}

and last _update.php : _

<?php

require_once('config.php');         

$mysqli = mysqli_init();
$mysqli->options(MYSQLI_OPT_CONNECT_TIMEOUT, 5);
$mysqli->real_connect($config['db_host'],$config['db_user'],$config['db_password'],$config['db_name']); 

// Get all parameters provided by the javascript
$colname = $mysqli->real_escape_string(strip_tags($_POST['colname']));
$code = $mysqli->real_escape_string(strip_tags($_POST['code']));
$coltype = $mysqli->real_escape_string(strip_tags($_POST['coltype']));
$value = $mysqli->real_escape_string(strip_tags($_POST['newvalue']));
$tablename = $mysqli->real_escape_string(strip_tags($_POST['tablename']));

// Here, this is a little tips to manage date format before update the table
if ($coltype == 'date') {
   if ($value === "") 
     $value = NULL;
   else {
      $date_info = date_parse_from_format('d/m/Y', $value);
      $value = "{$date_info['year']}-{$date_info['month']}-{$date_info['day']}";
   }
}                      

// This very generic. So this script can be used to update several tables.
$return=false;
if ( $stmt = $mysqli->prepare("UPDATE ".$tablename." SET ".$colname." = ? WHERE code = ?")) {
    $stmt->bind_param("si",$value, $code;
    $return = $stmt->execute();
    $stmt->close();

}             
$mysqli->close();        

echo $return ? "ok" : "error";