0

EDIT: I think I found a way to do it. It is not pretty and I know that I need to use prepared statements. (I'm working my way up to understanding them.)

// look up utilization_id
$sql_utilization_id = "SELECT id FROM system_utilization ORDER BY id 
DESC LIMIT 1";
$query_utilization_id = mysqli_query($link, $sql_utilization_id);
$result_utilization_id = mysqli_fetch_assoc($query_utilization_id);
$util_id=implode($result_utilization_id);
$new_util_id=intval($util_id)+1;

$sql = "INSERT INTO system_utilization (date_used, bunker_id, user_id, use_id, hours_used, activity_description) VALUES ('$date', '$bunker_id', '$user_id', '$use_id', '$hours_used', '$activity_description'); 
INSERT INTO system_meters (image_hours, treatment_hours, power_on_hours, gantry_revolutions, kv_energy_delivered, kv_image_hours, kv_amp_seconds, kv_num_exposures) VALUES ('$image_hours', '$treatment_hours', '$power_on_hours', '$gantry_revolutions', '$kv_energy_delivered', '$kv_image_hours', '$kv_amp_seconds', '$kv_num_exposures');
INSERT INTO hw_modifications (subsystem_id, component_id, manufacturer_id, new_pn, new_sn, new_fw, new_rev_id, old_pn, old_sn, old_fw, old_rev_id) VALUES ('$subsystem_id', '$component_id', '$manufacturer_id', '$new_pn', '$new_sn', '$new_fw', '$new_rev_id', '$old_pn', '$old_sn', '$old_fw', '$old_rev_id');
INSERT INTO failures_and_interrupts (component_id, error_message, actions_taken, utilization_id) VALUES ('$component_id_fail', '$error_message', '$actions_taken','$new_util_id')";

I am very new to SQL and coding in general. I am trying to learn as I go on this task. Please forgive me if I am ignorant of a simple solution.

I have been tasked with creating an equipment log book in a database. I am teaching myself PHP to create the code. I am using MySQL for my database.

I have several tables that may or may not have data inserted into them if they are not needed.

Tables:

system_meters system_utilization hw_modifications sw_modifications failures_and_interrupts

system_meters and system_utilization will be filled out on every use of the equipment. system_utilization has a column 'id' that will be used as a key 'utilization_id' on all of the other tables. 'id' will auto-increment whenever the table has a new row inserted.

Is there a way to have 'id' populate into 'utilization_id' whenever one of the other tables is updated?

Example: I change a piece of hardware. I fill out the necessary columns in system_utilization, system_meters, and hw_modifications. How would I have the 'id' from system_utilization inserted into the 'utilization_id' of the other 2 tables?

Delnoth
  • 3
  • 3
  • First, good luck with your learning, it is always great to see people learning to code! Second, it is strange that you'd want to replicate that key over when something changes. There are things called "triggers" that actually do that but I would consider them an advanced technique that is used for very specific problems. Can you describe your setup a little more? – Chris Haas Sep 27 '21 at 20:33
  • I have a bunker log that I am creating. Sometimes people are only running the system, so they fill out the system_utilization and system_meters tables. In this case, the auto-incremented 'id' from system_utilization would need to be inserted into the 'utilization_id' on system meters to tie the 2 events together. I am using the 'utilization_id' on each table to tie the events together so we have a way to track what event occurs with each use. I'm sorry if I am not explaining it well. – Delnoth Sep 27 '21 at 20:47
  • I'm still unfortunately not 100% clear on what you are doing, however it kind of sounds like you want to retrieve the most recent ID that MySQL generated for you. If that's the case, after an `INSERT` statement you can get that using one of the [two methods here](https://stackoverflow.com/a/1685867/231316), depending on your code. Am I on the right track? – Chris Haas Sep 27 '21 at 22:07
  • Thank you. I think that this is similar to what I am looking to do. I will read into these methods and see if I can figure out how to get them to work. I only started 2 months ago and am fighting between wanting to learn correctly and having to finish this project. Its one of those situations where you bit off more than you can handle, but didn't realize it until it was way to late. – Delnoth Sep 28 '21 at 14:26
  • I have updated my question with the code I came up with. I will be working on the prepared statements next. Again, thank you for your help. – Delnoth Sep 29 '21 at 16:27

0 Answers0