getting an id from one table, placing in another 1

[davaby]

Hi,
Relatively new to the PHP/MySQL world, hoping you can help. I have built a database and I am having trouble grasping one little part. I need to get the id that is a primary key, auto increment, in one table (document) and put it into another table(key_doc_join) as the data is being entered.

Here is the plan. I have inserted $title, and $newid into table document. Now I need $newid to also go into the docid column of the key_doc_join table. The key_doc_join table is a little scary to populate, I have half of it working. As info is put into the database, a bunch of stuff goes into table 'document' and some of the stuff goes into a keyword table, which breaks down phrases and looks for repeating words before they are allowed into the table.

So now the goal of my code is to say if the $keyword isn't in the keyword table, add it, then place the keyword's id into the keydocjoin table, along with the documents id that was put into the document table. I have the keyword's id going into to the keydocjoin table, but the code trips up when it goes to get the docid, which is the $newid.

Hmmmmm did any of that make sense????

So I suspect that my code is having trouble where I assign the variable $newid:

$SQL = "INSERT INTO document (docid, title) VALUES ('$newid', '$title')"; 

mysql_query($SQL); 
$newid= mysql_insert_id(); 
printf (&quot;<br>here is the newid $newid&quot;);

because when I print the $newid, only the default is printed (0), not the actual id. If I get this $newid variable to work, I should be able to figure out the rest from there.


Here is my code, that's in the middle of a mountain of other code, that should put the keyword's id and the document's id into the key_doc_join table:

$keyID = mysql_query("SELECT keyid FROM keywords WHERE keytype=$eachword[$k]"); 
$ID = mysql_result($keyID, "0", "keyid"); 

$inputlink = "INSERT INTO key_doc_join(keyid,docid) VALUES($ID,$newid)"; 
mysql_query($inputlink);

so sorry if I have entirely confused you, but I appreciate any guidance that you can offer.

Thank you!
Michelle

 

[KarveR]

Here is your problem and your answer:

id that is a primary key, auto increment,

Auto_increment will do the id for you;
$SQL = "INSERT INTO document (title) VALUES ('$title')";

You don't add $newid to an auto increment, its created for you.

Then you should use the id from that to link to all other relevant data:
$newid= mysql_insert_id();
use $newid to populate the id fields in the other tables, make sure that these are NOT auto_increment.

Does that help any?

*note; why add your own keywords, if you use innoDB tables and fulltext mode you can search for keywords within the table content and rate them for search results:

http://www.mysql.com/doc/F/u/Fulltext_Search.html

***************************************
Party on, dudes!

 

[davaby]

Thanks KarveR,
I was a little suspicious of that newid variable being inserted into the table...so I fixed that. So what my code looks like now:

$SQL = "INSERT INTO document (title) VALUES ('$title')";
		
        $newid= mysql_insert_id();

     	mysql_query($SQL);
	printf (&quot;<br>here is the newid $newid&quot;);

However, my code still doesn't work, and I wonder if it's still the newid, because when I do the printf to show the $newid, it still prints 0.

Thanks for the keyword suggestion. I had my database set up to do that earlier, but changed to this method instead. My head has exploded several times since then, so I can't remember the exact reason for my switch. When you use the method you suggested, are you able to highlight the keyword in the results? That might have been my reason for the switch.
Thank you so very much!
Michelle

 

[KarveR]

this is round the wrong way:
$newid= mysql_insert_id();

mysql_query($SQL);
printf (&quot;<br>here is the newid $newid&quot;

should be :
mysql_query($SQL);
$newid= mysql_insert_id();
printf (&quot;<br>here is the newid $newid&quot;
***************************************
Party on, dudes!

 

[davaby]

Thank you KarveR, you are my favourite person today! That code has been bothering me for days.
thankyouthankyouthankyou
Michelle

 

[sonnysavage]

So give him a &quot;star&quot;!