Two tables update function

QuestionsTwo tables update function
Gary Brett asked 10 years ago

HI Abu, made a post regarding this but cannot find it now so hope you don't mind if I post again?

After you kindly demoed how to insert data into two tables I found I couldn't then update the grid, it would take the data but lose on refresh. I added an update function but this doesn't allow edit/update either, can you spot where I am going wrong if you have time?

http://pastebin.com/UYnncWAP

Many thanks

Gary

9 Answers
Abu Ghufran answered 10 years ago

The update query has different syntax. I've updated link.
Added client_id & gender hidden column to update first table.

http://pastebin.com/u6QFeZd9

Diff: http://www.mergely.com/bTaJctVF/ (set options > ignore white space … to see exact differences)

Abu Ghufran answered 10 years ago

Try printing sql inside function and check if it is correct. It will display the query in error popup.

$sql = "update ….. ";
phpgrid_error($sql);

By code i think it should be:

WHERE id = '{$data[params][id]}'
(as your second table primary key is id)

You can always email me grid code + db sql dump to verify changes.

Gary Brett answered 10 years ago

Thanks Abu, that is fantastic, works lovely on the demo, on my own grid though it will now only update the 1 table, tbl_clients. tbl_lead wont update, I think I have gone through every bit of the demo code to ensure its correct yet still it wont update?

Will key trying to resolve but can you spot where the issue may be? I have a feeling its in the tbl_lead WHERE clause in update function but have tried many things?

http://pastebin.com/a6j4rV4R

Thank you, hopefully this will be last question for now 🙂

Gary Brett answered 10 years ago

Hi Abu, sorry another update but I have sort of fixed it, I had the where statement wrong but it will only update when you use inline editing..If I select row and choose edit from bottom menu it says record updated but doesn't update it!

Changing comms = '{$data[params][comms]}'WHERE id = '{$data[id]}'");

to

comms = '{$data[params][comms]}' WHERE client_id = '{$data[params][client_id]}'");

Thank you again

Gary Brett answered 10 years ago

Hi Abu, have tried that statement and a few others, have sent you db & php via email.

Kind Regards

Gary

Abu Ghufran answered 10 years ago

I've emailed you the fix.

You need to make first column as 'editable' => true.

$col = array();
$col["title"] = "client_id"; // caption of column
$col["name"] = "client_id"; // grid column name
$col["editable"] = true; // <<<<<<<<<<<<<<<<<<<<<<<<<<<
$col["hidden"] = true; // hide on grid
$col["width"] = "10";
$cols[] = $col;

Will not be displayed but submitted.

Gary Brett answered 10 years ago

Hi Abu, thank you again, that seems to have worked but oddly enough only when I set the 1st tables primary key value..

I think it should be WHERE id = '{$data[params][id]}' but that doesn't allow update, changing to WHERE client_id = '{$data[params][client_id]}' does allow so will do further testing to see if this affects tables anywhere else.

Thanks again

Gary

Abu Ghufran answered 10 years ago

Change 1: Move id column as first column.

$col = array();
$col["title"] = "id"; // caption of column
$col["name"] = "id"; // grid column name
$col["editable"] = true;
$col["hidden"] = true; // hide on grid
$col["width"] = "10";
$cols[] = $col;

// caption of column
$col = array();
$col["title"] = "client_id"; // caption of column
$col["name"] = "client_id"; // grid column name
$col["editable"] = true;
$col["hidden"] = true; // hide on grid
$col["width"] = "10";
$cols[] = $col;

Change 2: for leads table … set: WHERE id = '{$data[params][id]}'");

Emailing you updated file.

Gary Brett answered 10 years ago

Wow, that worked, does it make a difference in which order the columns are placed on the page? I never knew that.

Thanks once more Abu

Your Answer

20 + 4 =

Login with your Social Id:

OR, enter

Attach code here and paste link in question.
Attach screenshot here and paste link in question.



How useful was this discussion?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate it.

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?