[RESOLVED] Intermediary Table Question

j_a_g

Hi,

I'm currently working on a project that requires the user to input various services, and associate them with various target sectors. Service Names are entered as text, while target sectors are selected using checkboxes, which are created by pulling values from a table called 'sectors'.
Since each service can have mutiple target sectors, I've created an intermediary table to store which target sectors apply to each service.
So basically, I've got the following tables with the following fields:

sectors: Sector_Id (AUTOINC), Sector_Name
services: Service_Id (AUTOINC), Service_Name, Target_Sectors
target_sectors: Id (AUTOINC), Service_Id, Sector_Id

However, there are a couple of things that are causing me problems:

1) How can I take the Service_Id value from the service currently being created in order to insert it into the Service_Id field of the target_sectors table?

2) How can I create a row in the target_sectors table for each checked target sector box?

I'm assuming I'll have to use foreach for the second one, but I'm still a bit new to all this, so I'm not exactly sure how.
If someone could point me in the right direction, I'd be extremely grateful.

Thanks in advance,

Krik

1) How can I take the Service_Id value from the service currently being created in order to insert it into the Service_Id field of the target_sectors table?

Now an SQL wizard my have a better way but after inserting the new record I run a query, looking for the record I just inserted, using the data that was placed in it to reference the new record and then grab the unique identifier

2) How can I create a row in the target_sectors table for each checked target sector box?

This problem is fixed in how you set the "name" attribute in your html. Use a name like "sectors[]" this way all the sector values are stored in an array named "sectors" and you can then loop through that array.

bradgrafelman

j_a_g wrote:
1) How can I take the Service_Id value from the service currently being created in order to insert it into the Service_Id field of the target_sectors table?

After you insert the information into the services table, use the appropriate DB function to retrieve the ID generated for that row. For example, if using the 'mysql' extension, there's [man]mysql_insert_id/man.

j_a_g

Cool, thanks!

I'd say I'll (hopefully) be able to figure it out from here.
One more question though - am I right in assuming it's okay to put the code to do this on the same page as my form? or would it have to go on whatever page the user is redirected to on submission?

bradgrafelman

j_a_g wrote:
One more question though - am I right in assuming it's okay to put the code to do this on the same page as my form? or would it have to go on whatever page the user is redirected to on submission?

I don't understand your question... you'd process the data in whatever script you've pointed the form's 'action' attribute to.

j_a_g

Thanks Brad, that's a really handy one to know! :-)

j_a_g

And yeah, that's what I was thinking I should do.
Okay, time to get the coding head on...

Cheers again guys!

j_a_g

Okay, so after a couple of days of trial and error, I'm stuck again.....

Instead of having a row inserted in the intermediary table for each checked box, I'm getting one row, containing '0' as the value for sector_id, and only the value of the last checked box for service_id. It all looks okay to me, but as I said this is all fairly new to me. I'm going to keep trying, but it'd be great if someone could point out to me what I'm doing wrong. Here's my code (I initially set up the form in dreamweaver, but I'm making modifications by hand, if that makes a difference...)

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "addregser")) {
  $insertSQL = sprintf("INSERT INTO regional_services (Region_Id, Service_Name, Service_Purpose, Service_Description, Service_Impact, Service_www, Service_Contact_Name, Service_Contact_Email) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)",
                       GetSQLValueString($_SESSION['Region_Id'], "int"),
                       GetSQLValueString($_POST['Service_Name'], "text"),
                       GetSQLValueString($_POST['Service_Purpose'], "text"),
                       GetSQLValueString($_POST['Service_Description'], "text"),
                       GetSQLValueString($_POST['Service_Impact'], "text"),
                       GetSQLValueString($_POST['Service_www'], "text"),
                       GetSQLValueString($_POST['Service_Contact_Name'], "text"),
                       GetSQLValueString($_POST['Service_Contact_Email'], "text"));

  $Service_Id = mysql_insert_id();
  $SectorId = $_POST['sector'];

  foreach ($_POST['sector'] as $val) {
  $insertSQL2 = sprintf("INSERT INTO target_sectors (Service_Id, Sector_Id) VALUES (%s, %s);",$Service_Id, $val);
  }


  mysql_select_db($database_ernact, $ernact);
  $Result1 = mysql_query($insertSQL, $ernact) or die(mysql_error());
  $Result2 = mysql_query($insertSQL2, $ernact) or die(mysql_error());

Cheers again!

j_a_g

Got it!
It's working now. I just had to reposition my inserts like this:

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "addregser")) {
  $insertSQL = sprintf("INSERT INTO regional_services (Region_Id, Service_Name, Service_Purpose, Service_Description, Service_Impact, Service_www, Service_Contact_Name, Service_Contact_Email) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)",
                       GetSQLValueString($_SESSION['Region_Id'], "int"),
                       GetSQLValueString($_POST['Service_Name'], "text"),
                       GetSQLValueString($_POST['Service_Purpose'], "text"),
                       GetSQLValueString($_POST['Service_Description'], "text"),
                       GetSQLValueString($_POST['Service_Impact'], "text"),
                       GetSQLValueString($_POST['Service_www'], "text"),
                       GetSQLValueString($_POST['Service_Contact_Name'], "text"),
                       GetSQLValueString($_POST['Service_Contact_Email'], "text"));

  mysql_select_db($database_ernact, $ernact);
  $Result1 = mysql_query($insertSQL, $ernact) or die(mysql_error());

  $Service_Id = mysql_insert_id();

  $SectorId = $_POST['sector'];
  foreach ($SectorId as $val) {
  $insertSQL2 = sprintf("INSERT INTO service_sectors (Service_Id, Sector_Id) VALUES (%s, %s);",$Service_Id, $val);
  $Result2 = mysql_query($insertSQL2, $ernact) or die(mysql_error());
  }