View Full Version : create / insert / update record(s)

08-10-2007, 07:24 PM
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
create record
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.
example being

I would create a new part ( C ) that would be in assembly ( A ) however obsoleting part (B) from the assemblies latest parts

create part ( C )
INSERT number, description, status INTO Parts VALUES ('partC', 'description', ‘new’);

get assembly part ( A ) id
$partA = SELECT part_id FROM Parts WHERE part_id = partA

get partC id and make it a subpart of part ( A )
$partC = SELECT part_id FROM Parts WHERE part_id = partC
INSERT assembly_id, sub_id, generation INTO sub_parts VALUES($partA, $partC, generation + 1);

update partB status to older generation
UPDATE Parts SET(“status = ‘old’) WHERE part_id = $partB

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.
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?