EzDevInfo.com

AJAX update to multiple database tables

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

editablegrid set columns for rendering a bar chart graph

I'm using editableGrid for my project and I want to create a graph, with values from only 1 column.

When using renderBarChart() function, the table renders a graph, but with all numeric columns (set in metadata as integer or double) as data. So I have graph with data which are not related at all. Is there a way, how to force the graph to load data from 1 column (eg. only price)?

EditableGrid datatypes: double

I'm using the editablegrid library to make a table editable so I can later edit and update the database I'm pulling data from. I'm having some issues with the metadata header in the jsp. I've got:

<script src="js/editablegrid-2.0.1.js"></script>
<script>
    window.onload = function() {
        editableGrid = new EditableGrid("grid");

        // we build and load the metadata in Javascript
        editableGrid.load({
            metadata : [ {
                name : "ID",
                datatype : "string",
                editable : false
            }, {
                name : "DATE",
                datatype : "date",
                editable : false
            }, {
                name : "PRICE",
                datatype : "double (m, 10)",
                editable : true
            } ]
        });

        editableGrid.attachToHTMLTable('Grid');
        editableGrid.renderGrid();
    };
</script>

This all works quite nicely, however the PRICE column that is displayed is kinda weird, it uses a comma instead of a fullstop and vice versa. So for example:

1.5 (one and a half) will be displayed as "1,5" 1,500 (one thousand five hundred) will be displayed as "1.500"

Does anyone know how to change this?

Thanks in advance!

Add scroll-bar in EditableGrid

I am using EditableGrid(http://www.editablegrid.net) and wanted to know that is there a way to insert a scroll-bar within the grid.

I don't want to use pagination, I just want to add a scroll-bar, so that I can scroll down/up to view the records.

Say, I set the pagesize of the grid to 20, and if there are 40th records/rows to be displayed, then a vertical scroll-bar appears to the right of the grid. If it is '<= 20' then it should not show the scroll-bar.

EditableGrid Only editing top line

I am using EditableGrid (http://www.editablegrid.net/) Which creates some nice looking tables, however every time I try to edit any row other than the first it pulls the first row data into the row.

Here is an example of what is happening:

https://mylinguistica.com/Documentation/editablegrid-mysql-example/

I am using the example code found here:

http://www.editablegrid.net/en/faq

And otherwise everything is working great. Any Idea what could be causing this?

Editablegrid - JSON init + hide columns

I have a php file that generates the following valid json string:

{
    "metadata": [
        {
            "name": "change_id",
            "label": "changeId",
            "datatype": "string",
            "editable": false,
            "hide": true
        },
        {
            "name": "change_parent_id",
            "label": "changeParentId",
            "datatype": "string",
            "editable": false,
            "hide": true
        },
        {
            "name": "change_date",
            "label": "Date",
            "datatype": "string",
            "editable": false,
            "hide": true
        },
        {
            "name": "change_user",
            "label": "User",
            "datatype": "string",
            "editable": false,
            "hide": true
        }
    ],
    "data": [
        {
            "id": 1,
            "values": [
                "test",
                "test",
                "test",
                "test"             
            ]
        }
    ]
}

I would like to hide the 2 first columns, what would be the property to set in the metadata object?

How to make columns not sortable in EditableGrid javascript library?

I would like to deny the columns sorting feature fired by clicking on the column header, but I can't find the property to set for this behaviour.

Editablegrid mysql binding not updating

I am using editablegrid with mysql. It binds and display data to my grid. However when I try to edit or update the grid it fails to do so.

Below is the some of the code of my loaddata,

$grid->addColumn('CertificateNo', 'CertificateNo', 'integer', NULL, false); 
$grid->addColumn('ID', 'ID', 'integer');
$grid->addColumn('QuizNo', 'Test No', 'integer');  
$grid->addColumn('Received', 'Received', 'boolean');  
$grid->addColumn('DateReceived', 'Date Received', 'datetime'); 

Below is code for the update script:

    // Get all parameters provided by the javascript
$colname = $mysqli->real_escape_string(strip_tags($_POST['colname']));
$id = $mysqli->real_escape_string(strip_tags($_POST['id']));
$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']));
    / This very generic. So this script can be used to update several tables.
$return=false;
if ( $stmt = $mysqli->prepare("UPDATE ".$tablename." SET ".$colname." = ? WHERE id = ?")) {
    $stmt->bind_param("si",$value, $id);
    $return = $stmt->execute();
    $stmt->close();

Below is the part of javascript which passes the value to my update.php script.

    function updateCellValue(editableGrid, rowIndex, columnIndex, oldValue, newValue, row, onResponse)
{      
    $.ajax({
        url: 'update.php',
        type: 'POST',
        dataType: "html",
        data: {
            tablename : editableGrid.name,
            id: 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
    });

This template came with a javascript editablegrid-2.0.1. I noticed the problem has to do with primary key. In the demo which can be found from www.editablegrid.net the table has primary key ID whereas mine has CertificateNo but ID in my table is not a primary key.

So I changed the

$grid->addColumn('ID', 'ID', 'integer', NULL, false);

to

$grid->addColumn('CertificateNo', 'CertificateNo', 'integer', NULL, false); 

Now I can not update the grid.

Editablegrids HTML

am having a problem with editable grid from mySql DB. It loads the data well but when I need to edit they pick only data from the first row only. it doesn't load the other cell data on the update text boxes.

Any help will be appreciated.am useing this http://www.editablegrid.net/en modules

EditableGrid+mysql with add/delete

Soo.. i got an example of EditableGrid (http://www.editablegrid.net/) a pretty cool grid that lets me load data from mysql table to a html grid with sorting, inline editing + mysql database updates. I added filtering functionality from another example but am struggling with adding delete row and add row functionality.

I found an example of how to delete by adding a new column and i could call an update function from there.. but i'm struggling with syntax and where to place it .. javascript has me bretty boggled so far.. http://www.editablegrid.net/editablegrid/examples/simple/index.html

The output is loaded on to a div in a simple html file and works very well.. nice and fast and updated great. I just want to add 'new' and 'delete' functions to the table.

Any assistance appreciated.

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

DatabaseGrid.prototype.fetchGrid = function()  {
        // call a PHP script to get the data
        this.editableGrid.loadXML("loaddata.php");
};

DatabaseGrid.prototype.initializeGrid = function(grid) {
        grid.renderGrid("tablecontent", "testgrid", "tableid");
        tableRendered = function() { this.updatePaginator(); };
        _$('filter').onkeyup = function() { grid.filter(_$('filter').value); };
 };

the output from loaddata.php is such:

<table>
<metadata>
<column name="customer_id" label="UID" datatype="string" editable="true"></column>
<column name="customer" label="Customer" datatype="string" editable="true"></column>
<column name="lastchange_by" label="Editor" datatype="string" editable="true"></column>
<column name="ts" label="Timestamp" datatype="date" editable="true"></column>
</metadata>
<data>
<row id="1">
<column name="customer_id">
<![CDATA[ 5000 ]]>
</column>
<column name="customer">
<![CDATA[ Foo ]]>
</column>
<column name="lastchange_by">
<![CDATA[ Bar ]]>
</column>
<column name="ts">
<![CDATA[ 2013-10-17 00:00:00 ]]>
</column>
</row>
</data>
</table>

multiple mysql linked grids with EditableGrid

I am working through the EditableGrid demos modifying the code to understand it and reach a working example of multiple grids with the write-to-db example. I am a non-programmer and vba-hack.

Creating multiple grids in non-db examples is simple enough. However trying to replicate the code/structure for multiple grids I think prototype and this objects are might be confusing me

multiple grids without the db-link/inline-edit:

        window.onload = function() {
            editableGrid = new EditableGrid("DemoGridMinimal"); 
            editableGrid.tableLoaded = function() { this.renderGrid("tablecontent", "testgrid"); };
            editableGrid.loadXML("grid.xml");
            editableGrid = new EditableGrid("DemoGridMinimal2"); 
            editableGrid.tableLoaded = function() { this.renderGrid("tablecontent2", "testgrid"); };
            editableGrid.loadXML("grid.xml");
        }

single grid with the db link and inline edit: (a little modified from the example)

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

DatabaseGrid.prototype.initializeGrid = function(grid) { grid.renderGrid("tablecontent", "testgrid"); };  
DatabaseGrid.prototype.fetchGrid = function() { this.editableGrid.loadXML("loaddata.php"); }; 

window.onload = function() { 
    datagrid = new DatabaseGrid();
};

if it helps put overall context, the end result is to have several grids on a single page, populated with single sql query, defined by a html form input {accounting sql injection}, with each grid showing different fields from the db

could someone be kind enough to briefly explain the context of prototype and this in the example here

EditableGrid - show search filter & pagination using javascript

I am using EditableGrid to create a grid dynamically using Javascript.

I am able to display the grid properly. But can anyone please tell me, how do i add the search filter(search box) & pagination?

EditableGrid - get new value of via jQuery

I am using editableGrid table, and I need to copy edited content of <td> elements into hidden <input>s.

I'm trying to get those via jQuery .text() method, but it returns values before change. When I edit a <td> again, it returns previously entered text etc. Simple example:

<table id="htmlgrid" class="testgrid">
<tr id="0">
    <td>Fridge 123</td>
    <input type="hidden" name="[0][name]" value="Fridge 123">
    <td>7.000 CZK</td>
    <input type="hidden" name="[0][price]" value="7000">
</tr>
</table>

Now, what I want to do, is change a name "Fridge 123" to "Fridge 456" and copy this new name into input:<input type="hidden" name="[0][name]" value="Fridge 456">

Using this jQuery code

$("#htmlgrid td").change(function() {
    console.log($(this).text());
});

I get a value of Fridge 123, which is wrong. How can this be done, to get newly-entered values?

Grid not rendering in firefox

I am working with EditableGrid to generate and manipulate column models dynamically. Everything has gone according to the plan, except for just one compatibility issue with Firefox (..yes not IE -_-). I understand this is some sort of Closure issue maybe? I cannot seem to work a way around this. This is where something goes wrong:

EditableGrid.prototype.initializeGrid = function () {
    with (this) {
        //apply cell validators and inforenderers in headers
        var regex = [];
        for (var count = 0; count < selectedColumnNames.length; ++count) {
            var columnObj = findSelectedColumnObject(selectedColumnNames[count]);

            //check if regex is provided
            if (!(columnObj[0].validationRegex == "")) {
                // add a cell validator
                var expression = new RegExp(columnObj[0].validationRegex);
                regex[columnObj[0].name] = new RegExp(columnObj[0].validationRegex);
                var valObj = GetValidatorObject(expression);
                addCellValidator(columnObj[0].name, valObj);
            }
        }

        function GetValidatorObject(regObj){
            var obj = {
                isValid: function (value) {
                    return value == "" || (regObj.test(value));
                }
            };
            return new CellValidator(obj);
        }
}

The exception it throws is:

ReferenceError: GetValidatorObject is not defined [var valObj = GetValidatorObject(expression);]

Any ideas?