1

I have three tables in database:

 trips(trip_id(pk), trip_name(unique), user_id(fk))

 places(place_id(pk), place_name(unique))

 trips_places_asc(trip_id(fk), place_id(fk))

Since, many trips can have many places, I have one junction table as above.

Now, if user insert places to the trip, the places will be added to places table and the trip will be associated with the places in trips_places_asc table.

So, if i write query like: 

INSERT INTO places (place_name)
VALUES ('XYZ')

INSERT INTO trips (trip_name)
VALUES ('MyTrip')

Then, How to store trip_id and place_id in Junction or Association table trips_places_asc? will I have to fire two queries? plz help.

Note: There are many questions on SO like this one and this one. but, none of them have accepted answer or not even an answer. so, plz do not mark as duplicate.

Community
  • 1
  • 1
xyz
  • 1,325
  • 5
  • 26
  • 50

1 Answers1

5

Since you have place_name and trip_name as unique just do as:

insert into  trips_places_asc ( trip_id, place_id )
    values (  (select trip_id from trips where trip_name = 'MyTrip'),
              (select place_id from places where place_name = 'XYZ') );

Or depending what comand you are using to insert (php command I mean) you can return the ids after the inserts and use it to run an insert command with it.

It will be like: (using mysqli* functions )

$query = "INSERT INTO trips (trip_name) values ('MyTrip')";
$mysqli->query($query);
$trip_id = $mysqli->insert_id;

$query2 = "INSERT INTO places (place_name) values ('XYZ')";
$mysqli->query($query2);
$place_id = $mysqli->insert_id;

$query3 =  "insert into trips_places_asc ( trip_id, place_id ) "; 
$query3 .= " values ($trip_id, $place_id)";

Note, I'm doing this directly from my mind, so maybe you have to adjust some syntax error or be concerned about prepared statements.

EDIT Though I should add the proper documentation link about this command: http://php.net/manual/en/mysqli.insert-id.php

Jorge Campos
  • 22,647
  • 7
  • 56
  • 87
  • 3
    thanks..But what if I remove unique key from trip_name? – xyz Oct 31 '14 at 16:59
  • 2
    Then you will have to use the second option. I will edit the answer with a little example using php commands. wait a bit. – Jorge Campos Oct 31 '14 at 17:00
  • Are you there sir? I'm waiting for your answer. – xyz Oct 31 '14 at 17:09
  • Hello sir, both cases run successfully. But, I have one problem : let suppose, If I run query in your first case twice, then I will have multiple records of same foriegn keys in the association table. what to do in such kind of situation? – xyz Oct 31 '14 at 17:29
  • Then, create an array and store the ids of each entity you will run your query multiple times. Make sure to add it in order, because the command `$mysqli->insert_id` will return just the last id inserted. So, the logical way in your model would be insert a trip and lots of places (in my point of view), so you store the tripid and create an array to places and each place id you put on this array then iterate it inserting the trip with each id. – Jorge Campos Oct 31 '14 at 17:38
  • what i want to do is to avoid this: http://oi60.tinypic.com/soo1n9.jpg - multiple records of pairs – xyz Oct 31 '14 at 17:45
  • You have to change that table to make both columns as PK, it would be like: `alter table trips_places_asc add constraint _pk_somename primary key (trip_id, place_id)` that way the database will make sure that duplicate values can't happen. – Jorge Campos Oct 31 '14 at 17:52
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/64053/discussion-between-xyz-and-jorge-campos). – xyz Oct 31 '14 at 17:53