mysql - insert many to many relationship

Question

I am trying to insert records in 2 different mysql tables. Here's the situation:

Table 1: is_main that contains records of resorts with a primary key called id.

Table 2: is_features that contains a list of features that a resort can have (i.e. beach, ski, spa etc...). Each feature has got a primary key called id.

Table 3: is_i2f to connect each resort id with the feature id. This table has got 2 fields: id_i and id_f. Both fields are primary key.

I have created a form to insert a new resort, but I'm stuck here. I need a proper mysql query to insert a new resort in the is_main table and insert in is_i2f one record for each feature it has, with the id of the resort id id_i and the id of the feature id id_f.

$features = ['beach','relax','city_break','theme_park','ski','spa','views','fine_dining','golf'];

mysql_query("INSERT INTO is_main (inv_name, armchair, holiday, sipp, resort, price, rooms, inv_length, more_info)
VALUES ('$name', '$armchair', '$holiday', '$sipp', '$resort', '$price', '$rooms', '$length', '$more_info')");

$id = mysql_insert_id();

foreach($features as $feature) {
    if(isset($_POST[$feature])) {
        $$feature = 1;
        mysql_query("INSERT INTO is_i2f (id_i, id_f) VALUES (" . $id . ", ?????????????? /missing part here????/ ); }
    else {
        $$feature = 0; }
}

Thanks. Please, I'm going CrAzY!!!!!!!!!!!!!!

score 0 · Answer 1 · answered Jan 31 '13 at 09:48

This may not be relevant to you, but...

Would it not make more sense to leave the link table unpopulated? You can use JOINs to then select what you need to populate the various views etc in your application

i.e. query to get 1 resort with all features:

SELECT
    Id,
    f.Id,
    f.Name
FROM IS_MAIN m
CROSS JOIN IS_FEATURES f
WHERE m.Id = $RequiredResortId

score 0 · Answer 2 · edited May 23 '17 at 10:24

0

Please find the answer on Mysql insert into 2 tables.

If you want to do multiple insert at a time you can write a SP to fulfill your needs

edited May 23 '17 at 10:24

Community

1
1

answered Jan 31 '13 at 09:52

Rakesh Mahapatro

866
5
12

score 0 · Answer 3 · answered Jan 31 '13 at 11:05

If I understand you correctly you could concatenate variable amount of to be inserted/selected values into one query. (This is the second query which needs an id from the first.)

//initializing variables
$id = mysql_insert_id();
$qTail = '';
$i = -1;

//standard beginning
$qHead = "INSERT INTO `is_i2f` (`id`,`feature`) VALUES ";

  //loop through variable amount of variables
  foreach($features] as $key => $feature) {
    $i++;

    //id stays the same, $feature varies
    $qValues[$i] = "('{$id}', '{$feature}')";

    //multiple values into one string
    $qTail .= $qValues[$i] . ',';

  } //end of foreach

//concatenate working query, need to remove last comma from $qTail
$q = $qHead . rtrim($qTail, ',');

Now you should have a usable insert query $q. Just echo it and see how it looks and test if it works.

Hope this was the case. If not, sorry...

mysql - insert many to many relationship

3 Answers3