2. PHPRunner General questions
  3. [SOLVED]  Administrate (add) field in one table and insert it in another
This topic is locked
[SOLVED]

 Administrate (add) field in one table and insert it in another

 		8/7/2009 11:38:04 AM
PHPRunner General questions
S
 Shankar author

I have tables: event and event_competitiors.

The former lists event date, name, weather, location and other details.

The latter holds the event competitors names, competitors numbers etc.
I have a view of the event_competitor table which I use for post event admin. However, one thing I want to enter is "event duration". Clearly this detail appertains to the whole event and not to a particular competitor, so needs to be stored in the event table and NOT the event_competitors table.

Granted, I could go back to the event table and enter/edit this field there, but that is messy, seeing I am doing the rest of the post event admin in the other table.
So, my question - is there a simple way to add the event duration in the event_competitors table and have this saved to the event table? Let's say I made another column "event_duration", entered the time against the event winner and then used a php event to write this to the event table? Woudl that work? Is it a wise solution? If yes, can someone hint me the code?

I guess I need something like > insert into event_table, field_event_table, where event.event_id = event_competitors.event_id?
Rgds

Sr
J
 		Jane 8/10/2009

Hi,
PHPRunner doesn't support updating multiple tables on one page.

You can implement this manually using Before record updated/addedevents.
Join your tables on the Edit SQL querytab, then add duration field to the event_competitors add page and then use following code in the Before record addedevent:

global $conn;

$sql = "update event set duration=".$values["duration"]." where event_id=".$values["event_id"];

db_exec($sql,$conn);

unset($values["duration"]);
S
 		Shankar author 8/11/2009

Hi,

PHPRunner doesn't support updating multiple tables on one page.

You can implement this manually using Before record updated/addedevents.
Join your tables on the Edit SQL querytab, then add duration field to the event_competitors add page and then use following code in the Before record addedevent:


Hi Jane.

Thanks for your help.

I already had my tables joined by event_id (is this necessary for the code to work?).
Anyway, I tried your code and it doesn't work - error 256 "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1"

I went to events and didn't see your example event so I tried "before record added" > "Save new data in another table":
global $conn,$strTableName;
$strSQLSave = "INSERT INTO event (duration) values (";
$strSQLSave .= $values["duration"]." where event_id=".$values["event_id"];
$strSQLSave .= ")";

db_exec($strSQLSave,$conn);
That didn't work either.
Regards,

Shankar
J
 		Jane 8/11/2009

I recommend you to publish your project on Demo Account and open a ticket at http://support.xlinesoft.com sending a URL to your pages along with instructions on reproducing this error.
S
 		Shankar author 8/13/2009

You can implement this manually using Before record updated/addedevents.

Join your tables on the Edit SQL querytab, then add duration field to the event_competitors add page and then use following code in the Before record addedevent:


I had a little dig around and found the manual! (here: http://www.xlinesoft.com/phprunner/docs/))
From that:

global $conn;

$sql = "update othertable set joinedfield=".$values["joinedfield"]." ... ";

db_exec($sql,$conn);

unset($values["joinedfield"]);
First thing I notice is that my (left outer) join is on event_id and not duration. I did that because event_id is the PK of the event table and FK of the event_competitors table.

Second thought is what is the " ..." for? Presumably some extra data?
I also tried the master-detail code - presumably this updates the mastyer table from the detail table? Anyway, no luck there either.
Regards.

Shankar
Sergey Kornilov admin 8/13/2009

I recommend you to publish your project on Demo Account and open a ticket at http://support.xlinesoft.com sending a URL to your pages along with instructions on reproducing this error.