Getting ID's to insert into a relation table for a "many to many" relation - MySQL

I'm inserting a new word into table 'w' and a definition into table 'c' which are linked in table 's' which is the relation table for the many to many relationship between 'w' and 'c'.

I've been passed 2 variables from a form; $word and $def and i'm inserting them ok but i'm having trouble getting and passing on their ID's in order to insert them into the relation table 's'

I've checked every query and they all work individually, so i think it must be something in my php. I'm a beginner trying to teach myself so i may be going about this totally wrong.

[PHP]<?php

//I get the variables OK
$word = $_GET['word'];
$def = $_GET['def'];

//I connect OK
require_once ('mysql_connect.php');

//This works fine. w.word is a unique column so i use IGNORE
$query1 = "INSERT IGNORE INTO db.w (db.w.word, db.w.date) VALUES ('$word', NOW())";
$result1 = @mysql_query ($query1);

//This works fine
$query2 = "INSERT INTO db.c (db.c.def, db.c.date) VALUES ('$def', NOW())";
$result2 = @mysql_query ($query2);

//This works on it's own but may not here.
$query3 = "SELECT db.w.wid FROM db.w WHERE db.w.word = '$word' LIMIT 1";
$result3 = @mysql_query ($query3);

//This works on it's own but may not here.. the 'titles' are not unique so I order them by their ID and pick the last one created to get the proper one.
$query4 = "SELECT db.c.cid FROM db.c where db.c.title = '$mod' ORDER BY db.c.cid DESC LIMIT 1";
$result4 = @mysql_query ($query4);

//See note below:
$query5 = "INSERT IGNORE INTO db.s (db.s.wid, db.s.cid) VALUES ('$result3', '$result4')";
$result5 = @mysql_query ($query5);

mysql_close();


?>[/php]

The last query is suppose to take the ID's from the previous two queries and INSERT IGNORE them into my relation table 's'. When run it on an empty table it inserts but assignes a '0' for both ID's. The second time i run it with a different word and def nothing happens, no insert. Because the two ID's are indexed together as unique it's probably not inserting the second time because it still has a value of '0' for the ID's.

My question is why isn't it passing on values for $result3 and $result4 to $query5?

And.. i could probably use some help with my methodology. Any ways to do this type of thing without 5 queries?

Thanks!

re: Getting ID's to insert into a relation table for a "many to many" relation - MySQL

Heya, bugboy.

You might like mysql_insert_id().

re: Getting ID's to insert into a relation table for a "many to many" relation - MySQL

Cool! thanks!

I'm so close! I'm getting a '0' wid with mysql_insert_id() when the IGNORE is used so i have an 'else' that initiates a SELECT on w.wid to find it instead. Everything appears to work except I'm still getting a wid of '0' inserted when i have to use the SELECT to retrieve it ...It should set $wid to the proper key but doesn't.. I even have an elseif to stop it if it doesn't ...but that doesn't seem to work either.. any ideas?


[PHP]<?php

$word = $_GET['word'];
$def = $_GET['def'];

require_once ('mysql_connect.php');

$query1 = "INSERT IGNORE INTO db.w (db.w.word, db.w.creationdate) VALUES ('$word', NOW())";
$result1 = @mysql_query ($query1);
$wid = mysql_insert_id();

$query2 = "INSERT INTO db.c (db.c.def, db.c.creationdate) VALUES ('$def', NOW())";

if ($wid > 0) { $result2 = @mysql_query ($query2);
$cid = mysql_insert_id();
$query3 = "INSERT IGNORE INTO db.s (db.s.wid, db.s.cid, db.s.date) VALUES ('$wid', '$cid', NOW())";
$result3 = @mysql_query ($query3);
echo '<p>It worked and new word was added';
$ok = 1;

} else { $query4 = "SELECT db.w.wid FROM db.w WHERE db.w.word = '$word'";
$wid = @mysql_query ($query4);
}

if ($ok == 1) { die('Yay');

} elseif ($wid == 0) { die('Sorry there\'s a problem');

} else { $result2 = @mysql_query ($query2);
$cid = mysql_insert_id();
$query5 = "INSERT IGNORE INTO db.s (db.s.wid, db.s.cid, db.s.date) VALUES ('$wid', '$cid', NOW())";
$result5 = @mysql_query ($query5);
echo '<p>It worked but the word already existed';
}

mysql_close();

?>[/PHP]


If i 'echo' $wid immediately after the $query4 SELECT attempt i don't get a number i get "Resource id #3".. maybe this is a clue.. i just don't know to what..!?

re: Getting ID's to insert into a relation table for a "many to many" relation - MySQL

Ok i'm closer... i'm not retrieving the results properly.. i thought if it only had one value that it would be assigned to my variable and i didn't need to fetch an array.. but maybe i do... I'm reading here:

http://us2.php.net/manual/en/function.mysql-query.php

re: Getting ID's to insert into a relation table for a "many to many" relation - MySQL

YAY! i added this in my SELECT attempt to get the wID!! and it seems to work.. have to test it a bit...

[PHP]

if ($row = mysql_fetch_assoc($result4)) { $wid = $row["wID"]; }[/php]