Problems with a many-to-many insert

[filch]

Ok ... after receiving some great help and education as to how to design a relational database, I went on to try and create the front end for one. I am learning here so bear with me.

To put this into context, so you know, I am using DreamWeaver and Adobe Developer Toolkit to try and create this app. I am not foreign to hand coding and can code PHP to a decent level but I am a bit out of my depth here with this. I want to learn it though so I keep trying ;-)

So I have attached the dB design we had come up with during our previous design sessions, but I decided to simplify it even more ... at least for now while I try and figure out the many-to-many insert thing. So, the second dB diagram is what I am currently using. I have also attached a screen grab of my current form as I thought seeing how this is set up might help you understand what I am trying to do.

So, my issue is that the service checkboxes do not properly get inserted into my userairportservices link table. First, only one of the values gets inserted, I think the last one in the list. What I need to have happen is that if only one service is selected, only one record with the airport_id and the usr_id along with the service_id gets put into the links table. However, if 2 or more services are selected, 2 or more records need to be inserted into the links table while only one user record is to be inserted into the users table. I hope that is clear and perhaps it is obvious to some of you but it is giving me a huge headache.

Perhaps I am way off base here I don't know but the way the form is laid out is the way I would like to have it. Just need to figure out how this is supposed to work

As I am really under the gun here, I am most grateful for any help anyone can give me.

Dave

[dickersonka]

not so sure about the dreamweaver thing, i normally use visual studio

as far as what you need to do, you need to loop through each selected checkbox for the airport / user

this will prob best be supported in multiple inserts, rather than trying to do it with a single insert

do you have the code where you are populating the values to insert in the database?

[filch]

Well, I am struggling with this but I need to use a two dimensional array I think. It might even be a three dimensional array because I need to save the airports into an array and for each airport in the array, I need to also have an airport specific services array. So, I think I need to create an array of of all the selected airports. Then, get the length of the array and while i < $length, then get the length of the services array. While < $length, insert $usr_id, $airport_id and services_id into my linking table and then move on to the next loop and do it all again until the loop finishes.

Am I over complicating this? Maybe it is way off, I don't know as I am pretty brain-dead over this now. Can you clear this up at all for me?

Dave

[dickersonka]

lol i think a little over complicating, but you have the right concepts down, just one step at a time

here is pseudo-code, my php is a little rusty right now and i'm sure i would mess it up

$user_id = insert user
foreach airport checked
{
   foreach(servicechecked for airport)
{
     insert userairportservice
     $user_id - from above
     $service_id - checkbox
     $airportid - airport from the loop
}
}

[filch]

Wow .. what a long strange trip this has been!!

I am almost there with the insert for this but have one problem with the insert logic that perhaps you can spot.

Here is my insert code:

foreach($_POST['airport_id'] as $aid) { 
  	   foreach($_POST['service_id'] as $sid) {
	   		$query = "INSERT INTO userairportservices (usr_id_users, service_id_service, airport_id_airport) VALUES ('$_SESSION[$WA_sessionName]', '$sid', '$aid')";
			mysql_query($query) or die('Error, insert into dB failed');
			}
		}

Now this inserts the data alright ...but too much of it. In my last test, I selected one airport (airport_id = 1) and two services for it. I also selected another airport (airport_id=8) with 3 services for it. Attached is a screen grab of what it inserted. As you will see, it is inserting 5 records for each airport selected (the number of total services I have selected, rather than 2 services for one airport and 3 for the other. I am not quite seeing the error in logic but it is certainly there.

Can you spot it?

Dave

[dickersonka]

are you sure are checking if the checkbox is checked before adding it to the insert list?

[filch]

Well .. I was under the impression that a checkbox dos not get added to the $_POST array unless it is checked? I guess I can try and check it and see what gives.

Dave

[filch]

Well no luck with this but the more I thought about it, the more it seems that checking if the checkbox is checked is not the issue. If you look at what it is inserting, it is not inserting checkboxes that are not checked but rather, it is inserting the total amount of services checkboxes check for each checked airport. But, it is not inserting unchecked airports.

Dave

[filch]

Just throwing this out for consideration:

If you go back and look at my insert form, you will see the repeat regions there. For each airport there is a checkbox and they are all named airport_id[]. Then for each airport, there are three possible service selection checkboxes and they are all named service_id[]. The thing is that all of the generated serice checkboxes are named service_id[] and so by the time I submit the form, ALL of the service id's have been added to the service_id array. That is why it is inserting as it is. I need to figure out a different naming thing so that it inserts properly. OR, perhaps it is a 3D array? Crap ... just when I thought I was close.

Dave

[dickersonka]

i think maybe its a misunderstanding of the table

the table is for services that the user has per airport, which is whats its doing

the services that aren't checked don't go in the table

when trying to find what services a user has for an airport use

select * from userairportservics where user_id = ? and airport_id = ?

then you can loop through the results and see what services the user has at the specific airport, if the service isn't in the list, they don't have the service available

[dickersonka]

and maybe if i don't understand properly, let me know what services are being inserted that are wrong, what isn't being inserted my above post isn't correct

[filch]

But I am not referring to reading from the table ... I am referring to inserting original data into the table. It is not being inserted properly. I have determined that this is because the service_id[] array contains all of the service_id checkbox values selected, irregardless of the airport those services belong to. So, when I cycle through the array using the foreach construct, it is applying all of the services to all of the selected airports. I believe I have to rename the checkboxes in the form to something like:

service_id[1][1] value = <?php echo $row_rsServices['service_id']; ?>
service_id[1][2] value = <?php echo $row_rsServices['service_id']; ?>
service_id[1][3] value = <?php echo $row_rsServices['service_id']; ?>

However, I have been generating these dynamically so if I wanted to continue doing that, I would have to create logic that created these checkboxes on the fly and then incremented the first number based on which pass through the airport repeat region we were on. Getting more complicated by the minute.

Dave

[filch]

Maybe this example will show you what has been happening: I have atached two screenshots: the first is a test insert form I created showing what I selected; the second is a screen capture of the two arrays, the first being the airports selected and the second being the services_id array contents. Now if you think about the foreach construct I am currently using, we start with a foreach that walks through the airport_id array and for each one, inserts the contents of the service_id array. Not what I want at all. I need a construct that associated specific services with specific airports. Like a multidimensional array. Maybe array([airport_id] => array(services_id]), [airport_id] => array(services_id)); that sort of thing. So an associative array that has the id of the current airport with an array of the chosen services for that airport as the value. Make any sense at all?

Dave

[dickersonka]

ahhh i see now, i know you may not like the idea, but just name the checkboxes as groups when you are displaying them, on postback only grab the ones for the selected airport (also for each airport)

<input type="checkbox" name="YVR[]" />
<intput type="checkbox" name="YYC[]" />

[filch]

Hummm ... well what I am trying now is as follows:

This is a static form and not dynamic, at least at this point.

(from one row)

<tr>
            <td><label for="a_id[1]">YVR</label> 
            <input name="a_id[1]" type="checkbox" id="a_id[1]" value="1" /></td>
            <td><input name="a_id[1][]" type="checkbox" id="a_id[1][]" value="1" /> 
            Jet<br />
            <input name="a_id[1][]" type="checkbox" id="a_id[1][]" value="2" />
            Ground<br />
            <input name="a_id[1][]" type="checkbox" id="a_id[1][]" value="3" />
              Glycol</td>

The first attached image shows the form and what I selected and the second shows the output. The array seems to have what I need in it ... yes?

So looking at this array, I just need to figure out the logic of extracting the info and creating the SQL Query. The other thing though is that the form I have to submit now is a static form. I was hoping to build this form dynamically so that if airports or services are added, it wold update dynamically. Given how long this has taken though, I may need to hst go with this for now.

Dave

[dickersonka]

lol i've been looking at numbers all day, but yes the array looks good

wow, going the static route, i would suggest any time you have, make it a dynamic page, it will make your life much easier if things ever get changed, rather than trying to remember positions in the array, also it will actually be less code on you, but i know how time contraints go

hope you can knock this out in just a little bit :-)

[filch]

Hey ... well I finally got the insert code and it now, I am very happy to say, inserts the data correctly in the linking table. This is what I used, just so I can complete thread for any of those that read this in order to help them:

<table id="service-info">
                <tr>
                <td width="22%" class="label">Airport/Services:</td>
                <td width="78%"><table id="arraybuilder">
                    <tr class="label">
                      <td colspan="2">Airport</td>
                      <td width="34%">Service</td>
                    </tr>
                    <?php do { ?>
// the following code calls a function to alternate table row colors //
                      <tr bgcolor="<?php
if($SSAdv_m1%$SSAdv_change_every1==0 && $SSAdv_m1>0){
$SSAdv_k1++;
}
print $SSAdv_colors1[$SSAdv_k1%count($SSAdv_colors1)];
$SSAdv_m1++;
?>" class="tr">
                        <td width="47%"><label for="airport_id" class="padding-left"><?php echo $row_rsAirports['airport']; ?></label></td>
                        <td width="19%"><input name="airport_id[<?php echo $row_rsAirports['airport_id']; ?>]" type="checkbox" id="airport_id[<?php echo $row_rsAirports['airport_id']; ?>]" value="<?php echo $row_rsAirports['airport_id']; ?>" /></td>
                        <td><table border="0">
                            <?php mysql_select_db($database_conFSM2, $conFSM2);
$query_rsServices = "SELECT * FROM service";
$rsServices = mysql_query($query_rsServices, $conFSM2) or die(mysql_error());
$row_rsServices = mysql_fetch_assoc($rsServices);
$totalRows_rsServices = mysql_num_rows($rsServices); ?>
                            <tr>
                              <td><?php do { ?>
                                  <table width="100%" border="0" cellspacing="0" cellpadding="0">
                                    <tr>
                                      <td><input type="checkbox" id="airport_id[<?php echo $row_rsAirports['airport_id']; ?>][]" name="airport_id[<?php echo $row_rsAirports['airport_id']; ?>][]" value="<?php echo $row_rsServices['service_id']; ?>" /></td>
                                      <td class="left"><?php echo $row_rsServices['service']; ?></td>
                                    </tr>
                                  </table>
                                  <?php } while ($row_rsServices = mysql_fetch_assoc($rsServices)); ?></td>
                            </tr>
                          </table>
                          <label for="service_id_jet"></label></td>
                      </tr>
                      <?php } while ($row_rsAirports = mysql_fetch_assoc($rsAirports)); ?>
                  </table>

The labels are called dynamically from the database and the id's for each airport and service is also called dynamically. The key here was to create a multidimensional array from the checkboxes. This is done using the form:

<input name="airport_id[<?php echo $row_rsAirports['airport_id']; ?>]" type="checkbox" id="airport_id[<?php echo $row_rsAirports['airport_id']; ?>]" value="<?php echo $row_rsAirports['airport_id']; ?>" />

<input type="checkbox" id="airport_id[<?php echo $row_rsAirports['airport_id']; ?>][]" name="airport_id[<?php echo $row_rsAirports['airport_id']; ?>][]" value="<?php echo $row_rsServices['service_id']; ?>" />

Note that in these two examples, the name of the checkboxes are the same for both the airports and the services. The difference is that for the airports, the form is name=airport_id[]. For the services, the form is name=airport_id[][]. So with the second services checkbox, you are appending to the original airport_id array. Because the services are themselves an array, you are linking the services directly to the airport ... exactly what I needed to do.

Finally, to insert the data into the linking table, I had to pull the info out in the correct way, using foreach statements. Here is what I did:

$_SESSION[$WA_sessionName] = mysql_insert_id();
  // the code above inserted the user data. $_SESSION[$WA_sessionName] now contains the id of the last inserted user. So now we need to insert the airort and service ids along with the assocated user id in the linking table.
  $a_id = $_POST['airport_id'];
  foreach ($a_id as $aid => $chosen) {
		foreach ($chosen as $sid => $v) {
			
		$query = "INSERT INTO userairportservices (usr_id_users, service_id_service, airport_id_airport) VALUES ($_SESSION[$WA_sessionName], $v, $aid)";
           mysql_query($query) or die('Error, insert into dB failed for user id: $_SESSION[$WA_sessionName]'); 
		}
	}

This involved getting the last inserted id from the database and then inserting that id as the user id into a record in the linking table, along with the associated airport id and services id's.

Thanks to dickersonka for all f his patience ad help while I tried to learn this. Much appreciated. Now to figure out how to display this data in details and update forms!! I can smell a new thread coming.