Updating details records when Master table record is updated

On the table you are editng in the After record updated:

$data = array();
$keyvalues = array();
$data["email"] = $values["email"];
$data["gamename"] = $values["gamename"];
$data["subject"] = $values["subject"];
$data["topic"] = $values["topic"];
$data["subtopics"] = $values["subtopics"];
$keyvalues["gamename_id"] = $values["gamename_id"];
DB::Update("TABLE TO BE UPDATED", $data, $keyvalues );

Hope that helps..

Thanks Tandy,
It still isn't updating the value though. The master table is called gamenames and the details table is called partygames. The changes get made on the master table but not on the details records (partygames table). This is the code I'm using:

$data = array();
$keyvalues = array();
$data["email"] = $values["email"];
$data["gamename"] = $values["gamename"];
$data["subject"] = $values["subject"];
$data["topic"] = $values["topic"];
$data["subtopics"] = $values["subtopics"];
$keyvalues["gamename_id"] = $values["gamename_id"];
DB::Update("partygames", $data, $keyvalues );
return true;

It seems basically the same as Method 3 (which didn't work either), but with $data replacing $fieldValues.

It seems like the code is correct for both of these but nothing happens to the details. Are there other things besides the code itself that might cause these failures? For example, my project is connected to 2 databases, but gamenames and partygames are in the same database, so it doesn't seem like this should matter. Or is something missing in the code? Any further insight would be great. Thanks,
Brett

That code worked for mine. Was hoping that it would work for you. You are using case sensitive right? It is in After Record Updated as well right? Then in mine I do not have return true.

Here is mine. I have it in my fuel(After Record Updated) and need to update my Mileage in Truck Profile:

//Update Mileage in truck_profile - begin
$data = array();
$keyvalues = array();
$data["odometer"] = $values["odometer"];
$keyvalues["truck_number"] = $values["truck_number"];
DB::Update("truck_profile", $data, $keyvalues );
//Update Mileage in truck_profile - end

Here is another one taking my ending milage of my trip report and updating my mileage on my Truck Lube. Both tables have the same Key Values of the Truck Number. Not going for the ID. Again this is listed in my Trip Report under After Record Updated and still no return True;

//Update Mileage in truck_lube - begin
$data = array();
$keyvalues = array();
$data["mileage"] = $values["end_odometer"];
$keyvalues["truck_number"] = $values["truck_number"];
DB::Update("truck_lube", $data, $keyvalues );
//Update Mileage in truck_lube - end

Thanks,
I tried removing the return true line, but this didn't have an effect. I checked the cases and they all match correctly. I thought maybe the issue was that the master table uses "id" for the field that links to "gamename_id" in the details table so I edited the code as follows:

// In the After record Updated event for the master table 'gamenames'

// Define the new values to update in the details table 'partygames'
$data = array();
$keyvalues = array();
$data["email"] = $values["email"];
$data["gamename"] = $values["gamename"];
$data["subject"] = $values["subject"];
$data["topic"] = $values["topic"];
$data["subtopics"] = $values["subtopics"];
$keyvalues["gamename_id"] = $values["id"];
// Execute the update on the details table
DB::Update("partygames", $data, $keyvalues );

This didn't work but I'm wondering if maybe this is on the right track...

Is the syntax still wrong?

Instead of doing this in PHP I strongly advise to use a foreign key constraint in the database.
You gain not only the functionality you want but also consistency checks for your tables.
Always define foreign keys to maintain valid relationships between tables. Without foreign keys, orphaned rows may occur, leading to data inconsistency.

Hi Cristi,
I'm not very familiar with this approach, but I have heard of it through AI troubleshooting. Is there anything I need to do in PHPRunner to make this work or is it just done through PHP MyAdmin? Do you have any advice on how to go about this? Thanks,
Brett

https://youtu.be/oT4YWW_sbNQ?feature=shared

Be aware that foreign key constraints are supported ONLY on InnoDB for both MySQL and its most popular fork MariaDB.

Hi Cristi,
Some sources made it sound like foreign keys will only change the data for the key values but not for the other non-unique values in the tables. Things like email, subject, topic etc. are not unique in either of my tables. My understanding is that those fields can't be set up with foreign key constraints, so I am thinking they won't update ony details table. Am I mistaken?
Thanks for your help!

Ok, I used the following code (with help from Gemini) to check what is happening at the database:
// In the After record Updated event for the master table 'gamenames'

// Define the new values to update in the details table 'partygames'
$data = array();
$keyvalues = array();
$data["email"] = $values["email"];
$data["gamename"] = $values["gamename"];
$data["subject"] = $values["subject"];
$data["topic"] = $values["topic"];
$data["subtopics"] = $values["subtopics"];
$keyvalues["gamename_id"] = $values["id"];
// --- START: SQL DEBUGGING CODE ---

// 1. Build the SET part of the query
$set_parts = [];
foreach ($data as $field => $value) {
// Add quotes around string values and handle existing quotes
$set_parts[] = "" . $field . " = '" . addslashes($value) . "'";
}
$set_clause = implode(", ", $set_parts);

// 2. Build the WHERE part of the query
$where_parts = [];
foreach ($keyvalues as $field => $value) {
$where_parts[] = "" . $field . " = '" . addslashes($value) . "'";
}
$where_clause = implode(" AND ", $where_parts);

// 3. Assemble the final SQL statement
$sql_query = "UPDATE partygames SET " . $set_clause . " WHERE " . $where_clause . ";";

// 4. Display the query and stop the script
echo "Generated SQL Query:
";
echo "<pre style='background:#f1f1f1; border:1px solid #ccc; padding:10px; border-radius:4px;'>";
echo $sql_query;
echo "</pre>";
exit();

// --- END: SQL DEBUGGING CODE ---

// Your original code (the script won't run this part because of exit())
DB::Update("partygames", $data, $keyvalues );

I then did the following:

1. Edited and saved a record in my gamenames table to trigger the event.

   
   

2. Copied the SQL query that the script caused to be displayed

   
   

3. Pasted this SQL query into the SQL tab of phpMyAdmin (in the gamenames table) and ran it.

   
   

It gave me the following results:

UPDATE partygames SET email = 'bjbaughma@yahoo.com', gamename = 'Periodic Table2', subject = 'Science', topic = 'Chemistry', subtopics = 'Elements, Compounds, and Mixtures ' WHERE gamename_id = '231'

Affected rows: 6

This shows that the SQL query works and affected the 6 rows it should have affected.

This does NOT work however, when I build and test my PHPRunner project. There doesn't appear to be anything wrong with the code, but it doesn't work. Is this just a bug in PHPRunner 11 or is something else going on? Any ideas would be great! Thanks!

I am wondering if you have any unique indexes in the child table? then when updating the records this tries to produces a duplicate, then mysql doesn't allow the update query to work by any method.

I can't see anything wrong in the query as you've tried multiple methods to update the child records. I'd stick with method 3 in phprunner.

If you turn on the debug mode this should show the queries

When you echo the query which is produced, grab this query and try it in something like HeidiSql so it's running directly in the database, this will show you the error if any.

Hi Chris,
There is an auto-incremented index for both tables (id). These aren't linked though as they auto increment. The linked fields are id on the master table and gamename_id on the details table. I tried debug mode but it doesn't seem to show the sql queries for the edit page. I don't know if that is useful or not, but please let me know if you have any insight. Thanks,
Brett

It could also be that you might be trying to update a record which doesn't exist with that ID in the partygames table.

these are the steps I'd take to debug the issue, forgive me if you've tried these.

Turn on debug mode in after initialise application event
$dDebug = true;

Then check if there's a record in the after update record.

If the record does exist, then move the exit to after the update record block, this ensures the queries aren't cleared, once you have the sql query, use this in something like HeidiSql to ensure it runs or it will tell you any error.