2. PHPRunner General questions
  3. [SOLVED]  update field in the master record froma field in detail record
This topic is locked
[SOLVED]

 update field in the master record froma field in detail record

 		8/12/2017 6:02:57 PM
PHPRunner General questions
E
 elephant author

I have a database in which the master holds overall information regarding a project.
The detail table is used to track the progress of the project. Each time a detail reord is added it is assigned to another department/individual for processing.
In the detail record I have a field 'Assign_to'. I want to get the value in this field and update the 'Assigned_to' field in the master record.
The master record table is 'Project' and the Detail table is 'Progress'
When entering a new Progress record in After Record Added I have tried using:
$sql = "INSERT INTO Project (Assigned_to) values $values[Assign_to]";

CustomQuery($sql);
Syntax checks OK but when I run the application I get the error below.
Does anyone have any sugestions please?
Ian
php error happened

Technical information

Error type 256

Error description You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'Ian Thomas' at line 1

URL localhost/Progress_add.php?submit=1&fly=1&

Error file C:\Users\owner\Documents\PHPRunnerProjects\LLD_Tracker_build30v911v97\output\connections\Connection.php

Error line 661

SQL query INSERT INTO Project (Assigned_to) values Ian Thomas
More info
Call stack

File: line Function Arguments

0. connections\Connection.php:661 trigger_error 1. You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'Ian Thomas' at line 1;

  1. 256;

    1. connections\Connection.php:661 Connection->triggerError 1. You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'Ian Thomas' at line 1;

    2. connections\MySQLiConnection.php:142 MySQLiConnection->query 1. INSERT INTO Project (Assigned_to) values Ian Thomas;

    3. include\dal.php:24 CustomQuery 1. INSERT INTO Project (Assigned_to) values Ian Thomas;

    4. include\Progress_events.php:39 eventclass_Progress->AfterAdd 1. Array ( [Project_Ref] => Array [ID] => Array [Hotel] => Array [Assign_to] => Array [Email] => Array [Assign_for] => Array [Assign_Date] => Array [Required_by] => Array ...;


  2. Array ( [PID] => Array ) ;
  3. ;
  4. AddPage;

    5. classes\addpage.php:670 AddPage->callAfterAddEvent N/A

    6. classes\addpage.php:418 AddPage->processDataInput N/A

    7. classes\addpage.php:301 AddPage->process N/A

    8. Progress_add.php:282 Global scope N/A
C
 		copper21 8/12/2017

The syntax is not correct and you need to do an update statement.
Try:
$sql = "UPDATE Project SET Assigned_to = '".$values['Assigned_to']."' WHERE MASTERID_FROM_MASTER_TABLE = ".$values['ID_FIELD_IN_DETAILS_TABLE_THAT_EQUALS_MASTER_ID']."";

CustomQuery($sql);
Now you must have a field that ties the detail record to the master table. "MASTERID_FROM_MASTER_TABLE" should be your primary key field and "ID_FIELD_IN_DETAILS_TABLE_THAT_EQUALS_MASTER_ID" should be the field in the details table that will match the primary key field in the master table.
Brian
E
 		elephant author 8/13/2017



The syntax is not correct and you need to do an update statement.
Try:
$sql = "UPDATE Project SET Assigned_to = '".$values['Assigned_to']."' WHERE MASTERID_FROM_MASTER_TABLE = ".$values['ID_FIELD_IN_DETAILS_TABLE_THAT_EQUALS_MASTER_ID']."";

CustomQuery($sql);
Now you must have a field that ties the detail record to the master table. "MASTERID_FROM_MASTER_TABLE" should be your primary key field and "ID_FIELD_IN_DETAILS_TABLE_THAT_EQUALS_MASTER_ID" should be the field in the details table that will match the primary key field in the master table.
Brian


Many thanks Brian.
Ian