468,168 Members | 1,477 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,168 developers. It's quick & easy.

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

bugboy
160 100+
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!
Oct 14 '07 #1
4 4690
pbmods
5,821 Expert 4TB
Heya, bugboy.

You might like mysql_insert_id().
Oct 14 '07 #2
bugboy
160 100+
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..!?
Oct 15 '07 #3
bugboy
160 100+
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
Oct 15 '07 #4
bugboy
160 100+
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]
Oct 15 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

14 posts views Thread by Sharon | last post: by
4 posts views Thread by Jan Nielsen | last post: by
1 post views Thread by gcdp | last post: by
reply views Thread by kamranasdasdas | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.