Update/Delete/Insert 2 MySQL tables?

[ThorCoder]

Hi,
i have 2 MySQL tables in my database with the following columns:
Table 1:
LogCallID(Primary Key)
Source
Num
Msg

Table2:
LcdID(Primary Key)
LogCallID(Foreign Key)
Application
LogLevel
Text_en_US

I have created the table with the render_sql method (Join Command) to show the following required data: Source, Num, Msg from table 1 and Application, LogLevel, Text_en_US from table 2. But when i try to update/delete/insert data from these 6 columns, nothing happens.
How can i update/delete/insert data in this grid with data from these 2 mysql tables?
THANKS

[Stanislav]

You need to define custom updating logic ( auto-generated routines will not work for join queries )

docs.dhtmlx.com/doku.php?id=dhtm … ex_updates

[ThorCoder]

Thanks but what does the second line in the example mean:
$gridConn->render_sql(" … ",“id”,“price,name”); ?

[Stanislav]

" … " - can be any sql

So, you can just put custom update logic before your render_sql command.

[ThorCoder]

I’ve tried the following code…The first one works, but the second one doesn’t. It seems that there is something wrong with the Update statement:
$grid->render_sql(“SELECT lc.Source, lc.Num, lc.Msg, 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”); This works

$grid->render_sql(“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 = SYS_LogCallDescription.LogCallID”, “LogCallID”, “Source,Num,Msg,Application,LogLevel,Text_en_US”);

Any suggestions how to fix it?

[Stanislav]

Try to write it as

$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 = SYS_LogCallDescription.LogCallID");

$grid->render_sql("SELECT lc.Source, lc.Num, lc.Msg, 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"); This works

And isn’t you need to use {id} in your update SQL ?

[ThorCoder]

Yes, there is something wrong with the update statement. How can i find out which row has to be updated when i update data in a row?

[Stanislav]

In simple case you can use {id} in your query - it will be replaced with ID of row.

In more complex case, you need to use afterUpdate server side event, which gives access to all data of the updated row, including id and userdata ( if any was set )

[ThorCoder]

In simple case you can use {id} in your query - it will be replaced with ID of row.

In more complex case, you need to use afterUpdate server side event, which gives access to all data of the updated row, including id and userdata ( if any was set )

What kind of event do i have to take to update both tables (afterUpdate or beforeUpdate)? I think i don’t understand the difference between those two events. The Update statement above doesn’t work by the way. I’ve tried a lot but when when i try to update a field the whole row in the table is marked red.
Also i tried to insert values into both tables with insert, but it doesn’t work. I posted it in another post (Insert into 2 tables). For example i’ve tried it with LAST_INSERT_ID() in the insert statement for table two, but this doesn’t work. Do you have an idea how i have to write the code for inserting the 6 columns (Source, Num, Msg in table 1 / Application, LogLevel, Text_en_US in table2)?

[ThorCoder]

Can anyone help?

[Stanislav]

afterUpdate or beforeUpdate
doesn’t actually matter, afterUpdate is a bit more reliable ( when it occurs, default operations is already executed )

I think i don’t understand the difference between those two events

• server receive call from client side component
• beforeUpdate event triggered
• automatic saving routine executed
• afterUpdate event triggered
• response sent back to client side

as for insert you can use afterInsert event and $action->get_new_id(); to get the new ID value, which was created during inserting data in primary table.

[ThorCoder]

Ok thanks for you answer.
The update thing is working fine right now, so there is only one problem (insert) left.
Do you mean that i can use one afterInsert function for both insert statements (table 1 and 2)?

[ThorCoder]

I wrote a function but it didn’t work. The row in the table is marked red when i try to add a new row. Here is the code:

[code]<?php
require_once("…/config.php");
$res=mysql_connect($mysql_server,$mysql_user,$mysql_pass);
mysql_select_db($mysql_db);

require("../../codebase/grid_connector.php");
$grid = new GridConnector($res);
$grid->enable_log("../../logfiles/error.log",true);
$grid->dynamic_loading(100);
    $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 = '{LogCallID}'");
    function my_insert($action)
    {
        $logcallid = $action->get_id();
        $source = $action->get_value("Source");
        $num = $action->get_value("Num");
        $msg = $action->get_value("Msg");

        $grid->sql->query("INSERT INTO SYS_LogCalls (LogCallID, Source, Num, Msg) VALUES ('{$logcallid}', {$source}', '{$num}'), '{$msg}'");

        $newid = $action->get_new_id();
        $lcdid = $action->get_id();
        $application = $action->get_value("Application");
        $loglevel = $action->get_value("LogLevel");
        $text = $action->get_value("Text_en_US");

        $grid->sql->query("INSERT INTO SYS_LogCallDescription (LcdID, LogCallID, Application, LogLevel, Text_en_US) VALUES ('{$lcdid}', '{$newid}', '{$application}', '{$loglevel}', '{$text}')");
      
        $action->success();
    }
    $grid->event->attach("afterInsert", "my_insert");
    $grid->render_sql("SELECT lc.LogCallID, lc.Source, lc.Num, lc.Msg, 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]
What’s wrong with this?

[ThorCoder]

I’ve tried the whole day to get an insert into the 2 tables of the database but it didn’t work. I think there are some issues/problems withe the following things:

1. The Select statetment:

$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,LcdID,LogCallID,Application,LogLevel,Text_en_US");

The problem here is that i on client side in the table only 6 of the 9 columns must be shown and editable (Source, Num, Msg of table 1 and Application, LogLevel, Text_en_US from table 2).
Is it possible that LogCallID (primary key in table 1, foreign key in table 2) and LcdID (primary key in table 2) are not shown in table on client side?
How do i have to make the insert function for this?
Here is my code that doesn’t work:

[code]function insert1($action)
{
$id = $action->get_id();
$source = $action->get_value(“Source”);
$num = $action->get_value(“Num”);
$msg = $action->get_value(“Msg”);

        mysql_query("INSERT INTO SYS_LogCalls (LogCallID, Source, Num, Msg) VALUES ('{$id}', '{$source}', '{$num}', '{$msg}')");

        $action->success();
    }$grid->event->attach("beforeInsert","insert1");[/code]

Do not wonder why i only make an insert for table 1 because i wanted to try if he does insert in 1 table at all.
How do i have to make a function for inserting the six needed columns (Source, Num, Msg table 1 / Application, LogLevel, Text_en_US table2) into the database?
How is it possible to insert the values of LogCallID and LcdID into the tables when writing the insert statement? I’ve tried a lot of things but he didn’t insert a value for the keys (auto increment).
That is what the logfile says when i tried to insert values by trying two methods (beforeInsert,afterInsert):

[code]====================================
Log started, 22/07/2010 04:07:30

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.0044801235198975s

====================================
Log started, 22/07/2010 04:07:37

DataProcessor object initialized
1279807297068_gr_id => 1279807297068
1279807297068_c0 =>
1279807297068_c1 =>
1279807297068_c2 =>
1279807297068_c3 =>
1279807297068_c4 =>
1279807297068_c5 =>
1279807297068_c6 =>
1279807297068_c7 =>
1279807297068_c8 =>
1279807297068_!nativeeditor_status => inserted
ids => 1279807297068

BEGIN

Row data [1279807297068]
=> 1279807297068
LogCallID =>
Source =>
Num =>
Msg =>
LcdID =>
Application =>
LogLevel =>
Text_en_US =>
!nativeeditor_status => inserted

INSERT INTO SYS_LogCalls lc JOIN SYS_LogCallDescription lcd ON lc.LogCallID = lcd.LogCallID(LogCallID,Source,Num,Msg,LcdID,LogCallID,Application,LogLevel,Text_en_US) VALUES (’’,’’,’’,’’,’’,’’,’’,’’,’’)

ROLLBACK

Edit operation finished
0 => action:error; sid:1279807297068; tid:1279807297068;

Done in 0.0064880847930908s

====================================
Log started, 22/07/2010 04:07:40

DataProcessor object initialized
1279807297068_gr_id => 1279807297068
1279807297068_c0 =>
1279807297068_c1 => dsafdsaf
1279807297068_c2 =>
1279807297068_c3 =>
1279807297068_c4 =>
1279807297068_c5 =>
1279807297068_c6 =>
1279807297068_c7 =>
1279807297068_c8 =>
1279807297068_!nativeeditor_status => inserted
ids => 1279807297068

BEGIN

Row data [1279807297068]
=> 1279807297068
LogCallID =>
Source => dsafdsaf
Num =>
Msg =>
LcdID =>
Application =>
LogLevel =>
Text_en_US =>
!nativeeditor_status => inserted

INSERT INTO SYS_LogCalls lc JOIN SYS_LogCallDescription lcd ON lc.LogCallID = lcd.LogCallID(LogCallID,Source,Num,Msg,LcdID,LogCallID,Application,LogLevel,Text_en_US) VALUES (’’,‘dsafdsaf’,’’,’’,’’,’’,’’,’’,’’)

ROLLBACK

Edit operation finished
0 => action:error; sid:1279807297068; tid:1279807297068;

Done in 0.003385066986084s

====================================
Log started, 22/07/2010 04:07:49

DataProcessor object initialized
1279807297068_gr_id => 1279807297068
1279807297068_c0 =>
1279807297068_c1 => dsafdsaf
1279807297068_c2 => 123546
1279807297068_c3 =>
1279807297068_c4 =>
1279807297068_c5 =>
1279807297068_c6 =>
1279807297068_c7 =>
1279807297068_c8 =>
1279807297068_!nativeeditor_status => inserted
ids => 1279807297068

BEGIN

Row data [1279807297068]
=> 1279807297068
LogCallID =>
Source => dsafdsaf
Num => 123546
Msg =>
LcdID =>
Application =>
LogLevel =>
Text_en_US =>
!nativeeditor_status => inserted

INSERT INTO SYS_LogCalls lc JOIN SYS_LogCallDescription lcd ON lc.LogCallID = lcd.LogCallID(LogCallID,Source,Num,Msg,LcdID,LogCallID,Application,LogLevel,Text_en_US) VALUES (’’,‘dsafdsaf’,‘123546’,’’,’’,’’,’’,’’,’’)

ROLLBACK

Edit operation finished
0 => action:error; sid:1279807297068; tid:1279807297068;

Done in 0.0037100315093994s

====================================
Log started, 22/07/2010 04:07:52

DataProcessor object initialized
1279807297068_gr_id => 1279807297068
1279807297068_c0 =>
1279807297068_c1 => dsafdsaf
1279807297068_c2 => 123546
1279807297068_c3 => sdafdsafsdaf
1279807297068_c4 =>
1279807297068_c5 =>
1279807297068_c6 =>
1279807297068_c7 =>
1279807297068_c8 =>
1279807297068_!nativeeditor_status => inserted
ids => 1279807297068

BEGIN

Row data [1279807297068]
=> 1279807297068
LogCallID =>
Source => dsafdsaf
Num => 123546
Msg => sdafdsafsdaf
LcdID =>
Application =>
LogLevel =>
Text_en_US =>
!nativeeditor_status => inserted

INSERT INTO SYS_LogCalls lc JOIN SYS_LogCallDescription lcd ON lc.LogCallID = lcd.LogCallID(LogCallID,Source,Num,Msg,LcdID,LogCallID,Application,LogLevel,Text_en_US) VALUES (’’,‘dsafdsaf’,‘123546’,‘sdafdsafsdaf’,’’,’’,’’,’’,’’)

ROLLBACK

Edit operation finished
0 => action:error; sid:1279807297068; tid:1279807297068;

Done in 0.0025389194488525s

====================================
Log started, 22/07/2010 04:07:18

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.0014598369598389s

====================================
Log started, 22/07/2010 04:07:19

DataProcessor object initialized
1279807459444_gr_id => 1279807459444
1279807459444_c0 =>
1279807459444_c1 =>
1279807459444_c2 =>
1279807459444_c3 =>
1279807459444_c4 =>
1279807459444_c5 =>
1279807459444_c6 =>
1279807459444_c7 =>
1279807459444_c8 =>
1279807459444_!nativeeditor_status => inserted
ids => 1279807459444

BEGIN

Row data [1279807459444]
=> 1279807459444
LogCallID =>
Source =>
Num =>
Msg =>
LcdID =>
Application =>
LogLevel =>
Text_en_US =>
!nativeeditor_status => inserted

Event code for insert processed

COMMIT

Edit operation finished
0 => action:inserted; sid:1279807459444; tid:1279807459444;

Done in 0.0691978931427s

====================================
Log started, 22/07/2010 04:07:23

DataProcessor object initialized
1279807459444_gr_id => 1279807459444
1279807459444_c0 =>
1279807459444_c1 => dsafdsf
1279807459444_c2 =>
1279807459444_c3 =>
1279807459444_c4 =>
1279807459444_c5 =>
1279807459444_c6 =>
1279807459444_c7 =>
1279807459444_c8 =>
1279807459444_!nativeeditor_status => updated
ids => 1279807459444

BEGIN

Row data [1279807459444]
=> 1279807459444
LogCallID =>
Source => dsafdsf
Num =>
Msg =>
LcdID =>
Application =>
LogLevel =>
Text_en_US =>
!nativeeditor_status => updated

UPDATE SYS_LogCalls lc JOIN SYS_LogCallDescription lcd ON lc.LogCallID = lcd.LogCallID SET LogCallID=’’,Source=‘dsafdsf’,Num=’’,Msg=’’,LcdID=’’,LogCallID=’’,Application=’’,LogLevel=’’,Text_en_US=’’ WHERE =‘1279807459444’

ROLLBACK

Edit operation finished
0 => action:error; sid:1279807459444; tid:1279807459444;

Done in 0.00341796875s

====================================
Log started, 22/07/2010 04:07:25

DataProcessor object initialized
1279807459444_gr_id => 1279807459444
1279807459444_c0 =>
1279807459444_c1 => dsafdsf
1279807459444_c2 => 13132312
1279807459444_c3 =>
1279807459444_c4 =>
1279807459444_c5 =>
1279807459444_c6 =>
1279807459444_c7 =>
1279807459444_c8 =>
1279807459444_!nativeeditor_status => updated
ids => 1279807459444

BEGIN

Row data [1279807459444]
=> 1279807459444
LogCallID =>
Source => dsafdsf
Num => 13132312
Msg =>
LcdID =>
Application =>
LogLevel =>
Text_en_US =>
!nativeeditor_status => updated

UPDATE SYS_LogCalls lc JOIN SYS_LogCallDescription lcd ON lc.LogCallID = lcd.LogCallID SET LogCallID=’’,Source=‘dsafdsf’,Num=‘13132312’,Msg=’’,LcdID=’’,LogCallID=’’,Application=’’,LogLevel=’’,Text_en_US=’’ WHERE =‘1279807459444’

ROLLBACK

Edit operation finished
0 => action:error; sid:1279807459444; tid:1279807459444;

Done in 0.0016419887542725s

====================================
Log started, 22/07/2010 04:07:27

DataProcessor object initialized
1279807459444_gr_id => 1279807459444
1279807459444_c0 =>
1279807459444_c1 => dsafdsf
1279807459444_c2 => 13132312
1279807459444_c3 => dfsdsafds
1279807459444_c4 =>
1279807459444_c5 =>
1279807459444_c6 =>
1279807459444_c7 =>
1279807459444_c8 =>
1279807459444_!nativeeditor_status => updated
ids => 1279807459444

BEGIN

Row data [1279807459444]
=> 1279807459444
LogCallID =>
Source => dsafdsf
Num => 13132312
Msg => dfsdsafds
LcdID =>
Application =>
LogLevel =>
Text_en_US =>
!nativeeditor_status => updated

UPDATE SYS_LogCalls lc JOIN SYS_LogCallDescription lcd ON lc.LogCallID = lcd.LogCallID SET LogCallID=’’,Source=‘dsafdsf’,Num=‘13132312’,Msg=‘dfsdsafds’,LcdID=’’,LogCallID=’’,Application=’’,LogLevel=’’,Text_en_US=’’ WHERE =‘1279807459444’

ROLLBACK

Edit operation finished
0 => action:error; sid:1279807459444; tid:1279807459444;

Done in 0.0097308158874512s
[/code]
I would be very greatful if you could help me with this!