strange behavior it's adding the record instead of updating

[rami1973]

I have this table CREATE TABLE sra_tr.tr_users_forms_authorities ( user_id varchar(40) NOT NULL, formid int(5) NOT NULL, formauthorityid int(5) NOT NULL, enabled varchar(1) NOT NULL DEFAULT 'N' ) ENGINE=InnoDB COLLATE=latin1_swedish_ci

to enable inline editint I set primary key ALTER TABLE tr_users_forms_authorities ADD PRIMARY KEY (user_id,formid,formauthorityid) ajax.php

$user_id=$_REQUEST['user_id'];

$grid = new Grid("tr_users_forms_authorities", array( "save"=>true, "where"=>"user_id = '$user_id'"

html

user_id	formid	formauthorityid	enabled

---

The Problems:

1-before adding the primary key the query for user_id='admin' it was showing all records with user_id='admin' after adding these primarykye it's showing the last record only one record. 2-if you try to update this record it's trying to add new one: Duplicate entry 'admin-3-1' for key 'PRIMARY'.

Thank you for your help in advance

[optikalefx]

So this is a bit of a limitation with my grid for now. It doesn't support multi-column index primary keys. Only primary keys based on 1 column.

Is it possible to build your table to have 1 column be your primary key? Instead of a combination of all of them?

Thanks!

[rami1973]

With my litle knowledge I'm trying to make it work with complex PK's I'm checking grid.php I noteced: function load() { $post = $this->_safeMysql();

    // setup variables from properties
    $joins = $this->joins;
    $fields = $this->fields;
    $where = $this->where;
    $table = $this->table;

    // we need to break this up for use
    $colsArray = explode(",",$post['cols']);
that $post['cols'] have not defined before

Notice: Undefined index: cols in C:\wamp\www\sr_trf\OpenJS-Grid-master\rmk_imp\grid.php on line 237 Unknown column 'tr_all_emp.' in 'field list'.

if you notice also it's giving an empty column:

'tr_all_emp.' ' so in order it's using empty column : SELECT tr_all_emp.EMPLOYID,tr_all_emp.ENAME FROM tr_all_emp ORDER BY `` asc the grid is working but I don't know if this have affect on another thing in the grid class

[optikalefx]

Well, the cols is a POST sent from the grid, you should check the ajax and see if its sending columns, to fix this, you might have to trace the grid loading. Pretty much all parts of the PHP deal with that primary key. Including using it as the row id/

[rami1973]

Now complex PK's working for update I do these modification : function getPrimaryKey($table=NULL,$col_name=NULL) { if(!$table) $table = $this->table;

    $primaryKey = mysql_query("SHOW KEYS FROM `$table` WHERE Key_name = 'PRIMARY' AND Column_name = '$col_name'");
    $primaryKey = mysql_fetch_assoc($primaryKey);
    return $primaryKey['Column_name'];

}

function save() { $saveArray = $this->getSaveArray();

    // we need a primary key for editing
    //$primaryKey = $this->getPrimaryKey();

    // die here if a primary is not found
    //if(empty($primaryKey)) die("Primary Key for table {$this->table} Not set! For inline editing you must have a primary key on your table.");

    // go through each row and perform an update
    foreach($saveArray as $rowId=>$row) {
        $setArray = array();
        $setArray2 = array();
        foreach($row as $key=>$value) {
            // don't update this row if you have security set
            // idea from youtube user jfuruskog
            if(!is_array($this->security) || in_array($key,$this->security)) {
                // dont save fields that weren't saveable. i.e. joined fields
                if(in_array($key,$_POST['saveable'])) {
                    $key =  mysql_real_escape_string($key);
                    //$col_name=$key;
                    $t=$this->table;
                    $value =  mysql_real_escape_string($value);
                    $setArray[] = "`$key`='$value'";
                    $pk= $this->getPrimaryKey($table=$t,$col_name=$key);

                    if($pk){
                    $setArray2[] = " `$key`='$value' and ";
                    }
                }
            }   
        }

        //$sql = "UPDATE {$this->table} SET ".implode(",",$setArray)." WHERE `$primaryKey` = '$rowId'";
        $sql = "UPDATE {$this->table} SET ".implode(",",$setArray)." WHERE  ".implode(" ",$setArray2)."  1=1";

        $res = mysql_query($sql);

        // die with messages if fail
        $this->dieOnError($sql);
    }
    return (bool) $res;
}

[bmanikandan1212]

Hi Ram, Even after the change as you said above, still I get the same error as bellow.

Error : Notice: Undefined index: cols in C:\xampp\htdocs\Regression\lib\openjs\grid.php on line 247 Unknown column 'script_details.' in 'field list'

Kindly can you please clear this.

[sraIt]

as optikalefx commented 2 months ago

Well, the cols is a POST sent from the grid, you should check the ajax and see if its sending columns, to fix this, you might have to trace the grid loading. Pretty much all parts of the PHP deal with that primary key. Including using it as the row id/

mentioned when it's called from grid.js there no errors.