2. PHPRunner General questions
  3. [SOLVED]  Updating table through joined tables (query) doesn't work
This topic is locked
[SOLVED]

 Updating table through joined tables (query) doesn't work

 		11/6/2013 3:54:55 PM
PHPRunner General questions
W
 wijninga author

Hi all,
I have got a problem with updating a joined query. I have two tables Partnumbers (Parent) and Stock (Child). The join looks like this:
SELECT

Partnumbers.Partnumber,

Partnumbers.PartnumberAlt,

Partnumbers.PartDescription,

Partnumbers.ForDevice,

Stock.StockLocation,

Stock.Amount

FROM Partnumbers

INNER JOIN Stock ON Partnumbers.Part_ID = Stock.Part_ID
Now, I added the following code to the 'Edit page' 'Before record updated' event:
global $dal;

$tblDetail = $dal->Table("Stock");

$tblDetail->Value["Amount"] = $values["Amount"];

$tblDetail->Param["Part_ID"] = $values["Part_ID"];

$tblDetail->Update();

unset($values["Amount"]);
global $dal;

$tblDetail = $dal->Table("Stock");

$tblDetail->Value["StockLocation"] = $values["StockLocation"];

$tblDetail->Param["Part_ID"] = $values["Part_ID"];

$tblDetail->Update();

unset($values["StockLocation"]);
However, when I change the stock amount (Stock.Amount), the edit screen says 'Record updated', I don't get any error message, but the record doesn't get updated.
What am I doing wrong?
C
 		cgphp 11/6/2013

Put the code in the "After record updated", in the "Before record updated" the Part_ID value doesn't exist because the record hasn't been created yet.
Sergey Kornilov admin 11/6/2013

What Cristian says plus instead of $values["Part_ID"] you need to use $keys["Part_ID"] (assuming that Part_ID is a key column).
W
 		wijninga author 11/6/2013

I'm afraid that doesn't help. If I remove the code from 'Before Record updated' and just add the code to 'After Record updated', I get the error message '<<< Record was NOT edited >>> Unknown column 'StockLocation' in 'field list'.
If I add the code to both 'Before Record updated' and 'After Record updated', it doesn't give an error message, just the message 'Record updated', but the record doesn't get updated at all.
W
 		wijninga author 11/6/2013



What Cristian says plus instead of $values["Part_ID"] you need to use $keys["Part_ID"] (assuming that Part_ID is a key column).


Well, Part_ID is not a key field. Partnumber is though. But I should be using the field on which both tables are joined, right?
When making the change into $keys, I get the message '<<< Record was NOT edited >>> Unknown column 'StockLocation' in 'field list'', which I don't understand as the column StockLocation does exist.
I just added the Part_ID field from the parent table and added it as key field. It still gives me the same error: '<<< Record was NOT edited >>> Unknown column 'StockLocation' in 'field list'
Sergey Kornilov admin 11/6/2013

I see what you saying. If Part_ID is not a key column it makes sense moving code back to BeforeEdit event.
It's hard to tell what might be wrong though. I would suggest switching from DAL-based updated to plain SQL and printing SQL query on the page can help you with troubleshooting.
W
 		wijninga author 11/6/2013



I see what you saying. If Part_ID is not a key column it makes sense moving code back to BeforeEdit event.
It's hard to tell what might be wrong though. I would suggest switching from DAL-based updated to plain SQL and printing SQL query on the page can help you with troubleshooting.


I'm not sue what you mean by that. Just do the update by using a SQL update query instead of DAL? Can I still use the same variables from the DAL based setup into the SQL update query? And would be using a SQL query be usable for production?
Sergey Kornilov admin 11/6/2013

Yes, you can use all the same variables i.e.

$sql = "Update Stock set StockLocation = '".$values["StockLocation"] . "' where Part_ID = " . $values["Part_ID"];

CustomQuery($sql);


Now, if this doesn't work for any reason you can do the following:

$sql = "Update Stock set StockLocation = '".$values["StockLocation"] . "' where Part_ID = " . $values["Part_ID"];

//CustomQuery($sql);

echo $sql;

exit();


This way you can see the query, execute it manually if required and figure out what is wrong.
W
 		wijninga author 11/7/2013



Yes, you can use all the same variables i.e.

$sql = "Update Stock set StockLocation = '".$values["StockLocation"] . "' where Part_ID = " . $values["Part_ID"];

CustomQuery($sql);


Now, if this doesn't work for any reason you can do the following:

$sql = "Update Stock set StockLocation = '".$values["StockLocation"] . "' where Part_ID = " . $values["Part_ID"];

//CustomQuery($sql);

echo $sql;

exit();


This way you can see the query, execute it manually if required and figure out what is wrong.


Well, that helped! But it gets me into a new issue.. This is the response I get:
Update Stock set Stocklocation = '' where Part_ID =
So the big question: why are both fields empty? The tables are filled of course and when running the sql view by hand, I get 1492 hits.
Well, I solved part of that one, now the new error message: Update Stock set Amount = '5' where Part_ID =
I just can't find out why the Part_ID stays empty. When exactly is the Part_ID variable read by PHP Runner?
Sergey Kornilov admin 11/7/2013

The value of $values["Part_ID"] is read from your Add or Edit form.
W
 		wijninga author 11/8/2013



The value of $values["Part_ID"] is read from your Add or Edit form.



Turned out I had to use $keys as Sergey suggested, to get things running.
Now I only have one last issue (sorry being so dreadful). After updating the amount, I get a white screen. Now clicking back in the browser works and the amount actually gets updated, so that all works. So my only issue and question left: why do I get the white screen? This is my code on the 'before record updated':

$sql = "Update Stock set Amount = '".$values["Amount"] . "' where Part_ID = " . $keys["Part_ID"];

CustomQuery($sql);

//echo $sql;

exit();

// Place event code here.

// Use "Add Action" button to add code snippets.
return true;
Sergey Kornilov admin 11/8/2013

exit(); statement terminates page execution. Remove it.
W
 		wijninga author 11/8/2013



exit(); statement terminates page execution. Remove it.


If I remove the exit(); the error message re appears:
Unknown column 'StockLocation' in 'field list'
Sergey Kornilov admin 11/8/2013

Make sure you didn't remove the following line of code from your BeforeAdd or BeforeEdit event:

unset($values["StockLocation"]);
W
 		wijninga author 11/9/2013



Make sure you didn't remove the following line of code from your BeforeAdd or BeforeEdit event:

unset($values["StockLocation"]);




Thanks Sergey, that fixed it! Thanks a lot for your quick responses and wonderful help!