Auto-creating a "link" table between 2 main tables (ex table EmployeeProject from Employee and Project tables)

I have these 2 tables, Medication containing: IDMedication, IDCategory, Name, and Supplier, containing: IDSupplier, Name. I want to automatically create a relationship table, MedicationSupplier, containing: IDMedication and IDSupplier as keys, Price, and Quantity. My idea was to have a main page where i request the following data: (Medication)Name, IDCAtegory, (Supplier)Name, Price, Quantity. I'm not sure if i'm doing the right thing here.

So this is what i'm receiveing in the .php where i do the insert:

$Denumire=$_POST['Denumire']; //Medication Name
$IDCategorie=$_POST['IDCategorie']; //IDCategory
$Nume=$_POST['Nume']; //Supplier Name
$Pret=$_POST['Pret']; //Price
$Stoc=$_POST['Stoc']; //Quantity

And this is my insert:

$q_produse = "INSERT INTO produse VALUES ('','$IDCategorie','$Denumire')";
$q_prodfurniz = "INSERT INTO produsfurnizor VALUES ('','$IDFurnizor','$Pret','$Stoc')";

mysql_query($q_produse) or die($error);开发者_JS百科
mysql_query($q_prodfurniz) or die($error);

mysql_close();

My main problem at the moment is that i don't know how to insert IDMedication in the relationship table. Any help / suggestions of improving my code would be greatly appreciated. Thanks!

It sounds like you're needing to get the auto id created for the rows in your Medication and Supplier tables. You can use mysql_insert_id()

$q_produse = "INSERT INTO produse VALUES ('','$IDCategorie','$Denumire')";
$q_prodfurniz = "INSERT INTO produsfurnizor VALUES ('','$IDFurnizor','$Pret','$Stoc')";

mysql_query($q_produse) or die($error);
$produse_id = mysql_insert_id();

mysql_query($q_prodfurniz) or die($error);
$prodfurniz_id = mysql_insert_id();

$q_relationTable = "INSERT INTO MedicationSupplier VALUES ('$produse_id','$prodfurniz_id')";
mysql_query($q_relationTable) or die($error);

mysql_close();

Documentation on mysql_insert_id() is available at http://php.net/manual/en/function.mysql-insert-id.php

http://us2.php.net/mysql_insert_id

retrieves the last autoincremented value, in this case, IDMedication.