Topic: Help with many-to-many tables

[filch]

Hi all,

I am new to this forum and also new to relational dB, although not new to single table MySQL dB implementation in web sites. This question is not about any server problems or errors, but rather, about how to use and insert into a many-to-many type table relationship.

I have attached an image of my ERD for the current version of my dB as well as an image of the form I am trying to use to insert new users into my dB.

Here is what the situation is and what I am trying to achieve. I have a members dB. Each of these members can be members of one or more airports, and at each of these airports, members can have access to one, two or all three of the services available. (all airports have the same services available). I am trying to create a form which creates the new user record and also inserts the appropriate data into the userairportservces linking table. What is confusing me is that while we are creating a single user and associated usr_id, we need to insert up to three records into the linking table .. one for each service the member has access to. I am thinking it has something to do with transactions and triggers, but, being new to this, I am not sure how to do this.

I should also not, that while I can code PHP to a decent level, I would not call myself and advanced coder so I have been using the Adobe Developers Toolkit in DreamWeaver to work on this. But, I don't have to use it ... I just found it useful.

I really appreciate any help and time anyone can give me on this.

Regards,

Dave

[Barand]

There's an example at the end of this tutorial

http://www.phpfreaks.com/tutorial/data-joins-unions

Notice that each checkbox is a member of a 2 dim array with pupil and subject ids as the indexes.

line 101
echo "<td class='cb'><input type='checkbox' name='choice[$pid][$sid]' value='1' $checked></td>\n";

Write these ids to the linking table in the many to many relationship.

foreach ($_POST['choice'] as $pid => $subjects) {
  foreach ($subjects as $sid => $v) {
    mysql_query ("INSERT INTO choice (pid,sid) VALUES ($pid, $sid)");
  }
}

[filch]

First, thanks for your time and reply.

I am in the process of reading through this tutorial and a question occurred to me that I wanted to ask right away. In my schema, my linking table attempts to link three tables together (well actually four if you consider the linking table itself). In all of the tutorials I have come across, the linking table is always only linking two tables together, not including itself. Is it correct and possible to create a many-to-many relationship in the manner I have done it in my diagram?

Dave

[corbin]

Looks fine/possible to me.

[filch]

Can you clarify this a bit more for me? I am slowly closing in on comprehension .. at least the theory.

I am not clear, however, how the examples you gave work with my situation. I see how it might but not sure how to apply it because I would really have two 2 dim arrays ... would I not? One for the airports and one for the services. But each entry in the airports array would have another array in that entry ... the services array ... wouldn't it?

My head hurts ;-)  If you could just give me a kick in the butt down the path as to how this applies to my situation I would be most appreciative.

Dave

[filch]

There's an example at the end of this tutorial

http://www.phpfreaks.com/tutorial/data-joins-unions

Notice that each checkbox is a member of a 2 dim array with pupil and subject ids as the indexes.

line 101
echo "<td class='cb'><input type='checkbox' name='choice[$pid][$sid]' value='1' $checked></td>\n";

Write these ids to the linking table in the many to many relationship.

foreach ($_POST['choice'] as $pid => $subjects) {
  foreach ($subjects as $sid => $v) {
    mysql_query ("INSERT INTO choice (pid,sid) VALUES ($pid, $sid)");
  }
}

I am becoming brain dead I fear. I do not understand where you got the values for $pid ans $sid from in the tutorial?

Dave

[Barand]

I am becoming brain dead I fear. I do not understand where you got the values for $pid ans $sid from in the tutorial?

Dave

line 76
while (list($house, $pupil, $pid, $sid, $chosen) = mysql_fetch_row($choice_res))

which is processing the query from line 20

[filch]

OK .. well, I have it inserting now but with one problem. 

In this test, I selected airport number one (airport_id = 1) and added two services to it (service_id's 1 + 2). I also selected airport eight (airport_id = 8) and added three services to it (services_id's 1+2+3).

Here is the code I am using to insert:

Code: [Select]

$_SESSION[$WA_sessionName] = mysql_insert_id();
  // the previous 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 airport and service ids along with the assocated user id in the linking table.
 
  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 for user id: $_SESSION[$WA_sessionName]');
$query = "";
}
}

Now this does insert service_id's and airport_id's but if you look at the first attached screen grab of the actual insert, you will see that it is inserting the total number of service_id checkboxes selected for EACH airport selected. What should be inserted what you see in the second attached image example.

It is obviously something in my insert logic but I can't spot it. Can anyone give me some insight here?

Cheers

Dave

[Barand]

depends on your form structure. EG

Code: [Select]

<?php
if (isset($_POST['btnSubmit']))
{
    foreach ($_POST['service'] as $aid => $chosen)
    {
        foreach ($chosen as $sid => $v)
        {
            $sql = "INSERT INTO tablename (aid, sid) VALUES ($aid, $sid)";
            echo $sql, '<br/>';
        }
    }
}
?>
<form method="POST">
<h3>Airport 1</h3>
<p>Services</p>
Service 1 <input type='checkbox' name='service[1][1]' value='1'><br />
Service 2 <input type='checkbox' name='service[1][2]' value='1'><br />
Service 3 <input type='checkbox' name='service[1][3]' value='1'><br />

<h3>Airport 2</h3>
<p>Services</p>
Service 1 <input type='checkbox' name='service[2][1]' value='1'><br />
Service 2 <input type='checkbox' name='service[2][2]' value='1'><br />
Service 3 <input type='checkbox' name='service[2][3]' value='1'><br />

<h3>Airport 3</h3>
<p>Services</p>
Service 1 <input type='checkbox' name='service[3][1]' value='1'><br />
Service 2 <input type='checkbox' name='service[3][2]' value='1'><br />
Service 3 <input type='checkbox' name='service[3][3]' value='1'><br />

<input type='submit' name='btnSubmit' value='Submit'>
</form>

[filch]

OK ... thanks to you and a few others I have got my insert problems more-or-less under control. However, on the other side, I now have an issue with displaying the data in a page.

I have created an SQL query that does what I want and have attached a picture of the output from a query. I have also attached a screen grab from my current web page showing the airports that the user has access to. What I need to accomplish is to also show, in each row that shows the airport, the services that the user has access to at that airport. I do not want to show the airport name more than once but need to show up to three services for each airport. Anyone have an idea how I can do this?

TIA

Dave

[Barand]

Again, I would direct you to that tutorial code which shows all subjects chosen by each pupil on a single row for each pupil

[filch]

OK .. I did manage to get this working. Thanks for your input. I have one last hurdle. I can list records from the database, I can insert new records, I can search the dB but with the update, I can update the users table but how do you update the link table between two related tables? You don't have a mysql_last_insert() to use so I am stumped how one goes about this? The link table obviously has a unique id but how does one get a list of the matching id's to update. If you look at the attached image, you see there is 11 records for usr_id_users = 1. So, it is possible that some or all of these records might need to be updated. I am thinking some sort of array needs to be built when the page is first loaded. But I am really stumped here so any and all help is definitely appreciated.

Dave