Problem with Delete

Suite and Optimus Suite 5
#1

I have a problem with deleting values from my grid. The data is loaded from two mysql tables. It is a 1:n relationship. LogCallID is the primary key in table 1 and foreign key in table 2.
This is the render sql statement to show the grid in client:

$grid->render_sql("SELECT lc.LogCallID AS ID, lc.Source, lc.Num, lc.Msg, lcd.LcdID, lcd.Application, lcd.LogLevel, lcd.Text_en_US FROM SYS_LogCalls lc JOIN SYS_LogCallDescription lcd ON lc.LogCallID = lcd.LogCallID", "ID", "Source,Num,Msg,Application,LogLevel,Text_en_US,");

LogCallID (primary key in table 1 and foreign key in table 2) and LcdID (primary key in table 2) shell not be shown in the grid. That works.
This is the Delete statement:

$grid->sql->attach("Delete","DELETE FROM SYS_LogCalls WHERE LogCallID = '{ID}'");

As soon as i put in the optional second parameter in the render_sql statement above (“ID”), the delete statement works BUT he doesn’t show multiple entries from table 2 (i.e. i habe one entry in table 1 with LogCallID ‘150’ and my entries in table two with LogCallID ‘150’). If i delete the optional second parameter in the render_sql statement he does show all entries from both tables BUT the delete statement doesn’t work. The error log complains the following:

Incorrect field name used: LogCallID

I’ve tried it with “beforDelete” both that doesn’t work either.
Also how can i find out the numbers of rows form a select statement. I need this to check, if there are multiple entries in table 2 and if yes that he only delete the entry from table 2 and not from table 1 as long as there are multiple entries in table 2.
Please help.

#2

Please help, i tried just another things with (is_select_mode()) but it still doesn’t work…

#3

When you are using

…ON lc.LogCallID = lcd.LogCallID", “ID”, "Source,Num,Msg…

ID is uses as id of the row in grid. But to be rendered correctly ID must be unique. As far as I can understand, in your case there may be few records with the same ID ( because of 1:n relation )

on of possible solution would be to use

…ON lc.LogCallID = lcd.LogCallID", “”, "ID,Source,Num,Msg…

and in client side configuration add one more column to the start of grid and mark it as hidden

As result the ID value will be stored in hidden column, and will not affect row-id, but will be available for data operations.

#4

Yes thats exactly my problem. I’ve tried your solution, but he still complains with the error message:

Incorrect field name used: ID

It doesn’t matter which column field i use, he always complains. I.e. if i use in delete statement:

$grid->sql->attach("Delete","DELETE FROM SYS_LogCalls WHERE Num = '{Num}'");

he complains:

Incorrect field name used: Num

What do i make wrong?

#5

You are using the same render_sql for both data loading and data updating, right?
Server side code resolves names of incoming data based on parameters of render_sql, so when list of fields is different for loading and saving - similar error can occurs.

If issue still occurs for you - please provide full listing of server side log for the problematic operation

#6

This is the error log:

[code]====================================
Log started, 03/08/2010 04:08:35

SELECT lc.LogCallID, lc.Source, lc.Num, lc.Msg, lcd.LcdID, lcd.LogCallID, lcd.Application, lcd.LogLevel, lcd.Text_en_US FROM SYS_LogCalls lc JOIN SYS_LogCallDescription lcd ON lc.LogCallID = lcd.LogCallID LIMIT 0,100

SELECT COUNT(*) as DHX_COUNT FROM SYS_LogCalls lc JOIN SYS_LogCallDescription lcd ON lc.LogCallID = lcd.LogCallID

Done in 0.010102033615112s

====================================
Log started, 03/08/2010 04:08:43

DataProcessor object initialized
1280846735_8_gr_id => 1280846735_8
1280846735_8_c0 => 153
1280846735_8_c1 => hgfjhgfj
1280846735_8_c2 => 1321
1280846735_8_c3 => dsfsdaf
1280846735_8_c4 => dsfdsaf
1280846735_8_c5 => 4
1280846735_8_c6 => fdgfdsg
1280846735_8_!nativeeditor_status => deleted
ids => 1280846735_8

BEGIN

Row data [1280846735_8]

Incorrect field name used: LogCallID

data

DELETE FROM SYS_LogCalls WHERE LogCallID = ‘’

COMMIT

Edit operation finished
0 => action:deleted; sid:1280846735_8; tid:1280846735_8;

Done in 0.0056369304656982s
[/code]
And these is the client side code:

[code]<?php
header(‘Content-Type: text/html; charset=UTF-8’);
?>

LogTranslation
Hier mit Maus drüber fahren zur Erklärung der Grid
Bedienung der Grid:
  • Doppelklick auf Tabellenzelle -> Feld editieren
  • Löschen einer Zeile -> Zeile markieren und auf "Eintrag löschen" klicken
  • Nach neuem Eintrag in die Grid Browser refreshen für Anzeige des neuen Eintrags
  • Über die Eingabefelder bei "Source" und "Num" kann gefiltert werden
  • Filterung (nach Alphabet) ist durch Klick auf die verschiedenen Header (Source, Num etc.) möglich
  • Wenn Num kleiner/gleich 0 oder Loglevel kleiner/gleich 0 oder größer 5: Kein DB Update -> Zeile wird rot angezeigt
  • Alle Zellen müssen ausgefüllt sein, sonst kein DB Update -> Zeile wird rot angezeigt
[/code] This is my server side code: [code]<?php header('content-type: text/html; charset=utf-8'); require_once("config.php"); $res=mysql_connect($mysql_server,$mysql_user,$mysql_pass); mysql_select_db($mysql_db); mysql_query("SET CHARACTER SET 'utf8'"); 
require("grid_connector.php");
$grid = new GridConnector($res);
    $grid->sql->set_transaction_mode("global");
$grid->enable_log("error.log", true);
$grid->dynamic_loading(100);
    $grid->set_encoding("utf-8");
    /*function validate1($action)
    {
       if ($action->get_value("Source")=='' || $action->get_value("Num")=='' || $action->get_value("Msg")=='' || $action->get_value("Application")=='' || $action->get_value("LogLevel")=='' || $action->get_value("Text_en_US")=='')
       {
           $action->invalid();
       }
       $num = $action->get_value("Num");
       $loglevel = $action->get_value("LogLevel");
       if(!is_numeric($num) || !is_numeric($loglevel))
       {
           $action->invalid();
       }
       if($num <= 0)
       {
           $action->invalid();
       }
       if($loglevel <=0 || $loglevel > 4)
       {
           $action->invalid();
       }
    }
     function customDelete($action){
         $id = $action->get_vaalue("LogCallID");
         mysqlquery("DELETE FROM SYS_LogCalls WHERE LogCallID = '{$id}'");
         //mysql_query("UPDATE SYS_LogCalls, SYS_LogCallDescription SET Source='{Source}', Num='{Num}', Msg='{Msg}', Application='{Application}', LogLevel='{LogLevel}', Text_en_US='{Text_en_US}' WHERE SYS_LogCalls.LogCallID = '{ID}' AND SYS_LogCallDescription.LogCallID = '{ID}'");
         $action->success();
    }*/
    //$grid->sql->attach("Delete","DELETE FROM SYS_LogCalls WHERE ID = '{ID}'");

    //$grid->event->attach("beforeDelete", "customDelete");
    $grid->sql->attach("Delete","DELETE FROM SYS_LogCalls WHERE LogCallID = '{LogCallID}'");
    //$grid->sql->attach("Update","UPDATE SYS_LogCalls, SYS_LogCallDescription SET Source='{Source}', Num='{Num}', Msg='{Msg}', Application='{Application}', LogLevel='{LogLevel}', Text_en_US='{Text_en_US}' WHERE SYS_LogCalls.LogCallID = '{LogCallID}' AND SYS_LogCallDescription.LogCallID = '{ID}'");
    //$grid->sql->attach("Delete","DELETE FROM SYS_LogCalls WHERE Num = '{Num}'");
    //$grid->render_sql("SELECT lc.LogCallID AS ID, lc.Source, lc.Num, lc.Msg, lcd.LcdID, lcd.LogCallID, lcd.Application, lcd.LogLevel, lcd.Text_en_US FROM SYS_LogCalls lc JOIN SYS_LogCallDescription lcd ON lc.LogCallID = lcd.LogCallID", "", "ID,Source,Num,Msg,Application,LogLevel,Text_en_US", "","");
    $grid->render_sql("SELECT lc.LogCallID, lc.Source, lc.Num, lc.Msg, lcd.LcdID, lcd.LogCallID, lcd.Application, lcd.LogLevel, lcd.Text_en_US FROM SYS_LogCalls lc JOIN SYS_LogCallDescription lcd ON lc.LogCallID = lcd.LogCallID", "", "LogCallID,Source,Num,Msg,Application,LogLevel,Text_en_US");
    ?>[/code]

I’ve tried it with $grid->attach->sql and beforeUpdate but both things didn’t work…It seems that the server side don’t receive any value from the grid or don’t receive the columns. I don’t use different names for columns in select and update statement.
Well, the problem is that if i take the optional second parameter away from sql->render (LogCallID) all values from both tables will be shown correctly but the error with the incorrect field names occurs. When i put in the optional parameter in render->sql the delete statement works correctly but the grid doesn’t show the multiple entries from table two (the table where multiple entries are allowed)…

#7

Well, after trying a lot of things i’ve solved the problem. In the render_sql statement i put in “LcdID” as the second optional parameter (identifier). “LcdID” is the primary key of table two (where multiple entries are possible). I thought that i always have to use “LogCallID” which is primary key in table one and foreign key in table two as identifier but than the grid didn’t show the multiple entries of table two (see my problems above).
But one question left. Now i have to check if there are multiple entries in table two. If yes than he only has to delete the corresponding entry from table two and not all entries that belongs together in both tables. So i have to check if there are mutiple LogCallID’s in table two with the same LogCallID number. How can i check this? Is there a function (num_rows) for the grid?