I know this is probably not something that is very hard, but I want to make my site as efficient as possible. currently, this is the psuedo structure of what I want to accomplish
I want to check if the part is already in the system
if exists, check if the field is blank
if blank, insert data
else if userdata != field data
update data (most likely across multiple tables)
field didnt change, move onto next
insert data (definitely across multiple tables)
Presently I would need to do this for every single field separately, I was wondering if this was the most efficient way possible, or if there was a more efficient way.
Also, when I update the data and even more so when I insert new data I would need to access the part id of the part being created... would I need a second insert statement for the additional tables being affected.
I would create a new part ( C ) that would be in assembly ( A ) however obsoleting part (B) from the assemblies latest parts
There are a lot more complicated queries I would need to perform, and while I do not have a restriction on the maximum number of connections I want to keep it to as little as possible. An example of a more complex would be when I select part B and I put it under a new assembly. I would need to check every field for partB across 2 more tables than the 2 tables I am using in this example for changes, updating the changes and inserting new data where applicable. Some of the tables have 7/8/ even 10 fields so that’s a lot of queries to check each field individually for changes, hence my concern for abundant connections.
[b]create part ( C )[/b]
INSERT number, description, status INTO Parts VALUES ('partC', 'description', ‘new’);
[b]get assembly part ( A ) id[/b]
$partA = SELECT part_id FROM Parts WHERE part_id = partA
[b]get partC id and make it a subpart of part ( A )[/b]
$partC = SELECT part_id FROM Parts WHERE part_id = partC
INSERT assembly_id, sub_id, generation INTO sub_parts VALUES($partA, $partC, generation + 1);
[b]update partB status to older generation[/b]
UPDATE Parts SET(“status = ‘old’) WHERE part_id = $partB
This wouldn’t really affect the user as they were attempting to access the site once complete, but this definitely has the potential to explode on the admin side; and I was just wondering if there was a way to “dumb down” the connections / queries?