[Resolved] Working with Multiple Tables

[janey]

I know this topic has been touched on many times, but I am still needing some clarification.

Here is an example: I have a table of "Members", which holds members' names. I then have a second table of "Specialties". Each member can have unlimited numbers of specialties.

When I add a member, I would like the form to include a field for the member's name as well as a field to allow the assignment of one specialty. So, I would like to basically insert info into two tables with one form.

Since I will later query the tables to display members who belong to a certain specialty, I will obviously need an ID field in both tables, and this value must be the same.

So, my question is...what is the best way to do this?

It seems I would need to:

1. Insert member info into the Members table
2. Query the Members table to get the ID
3. Insert specialty information along with the ID value queried from the Members table.

Will that work? If so, how? Would it be something along the lines of:

$query = "INSERT INTO Members (ID, Last_Name) VALUES('$ID', '$Last_Name')";

$result = mysql_query($query) or die ("Error in query: $query. " . mysql_error());


$query2 = "SELECT * FROM Members WHERE (ID = '$ID')";
$result2 = mysql_query($query2) or die ("Error in query: $query. " . mysql_error());


$query3 = "INSERT INTO specialty (ID, Last_Name, specialty) VALUES('$ID','$Last_Name', '$specialty')";
		$result3 = mysql_query($query3) or die ("Error in query: $query. " . mysql_error());

I know that the above is not correct, but can something along those lines be done? Any help would be appreciated. I've been trying to do this for months with no luck.

[YodaOfCamarilla]

Wouldnt the following be enough?

$sql="insert into members (id, name) values(\"$memberid\", \"$membername\")";
$rs=mysql_query($sql,$conn) or die("Err:Que");
$sql="insert into specialties (specialty, id) values(\"$specialtyname\", \"$memberid\")";
$rs=mysql_query($sql,$conn) or die("Err:Que");

[janey]

Thanks for your help. I finally got it working. The code:

		 // Insert into Members table
$insert_query = "INSERT INTO Members
             (Last_Name)
             VALUES
                        ('$Last_Name')";

$result = MYSQL_QUERY($insert_query) or die("Can not execute query $insert_query. ");
$key_of_new_entry=mysql_insert_id();

// Insert into Specialty table
$insert_query = "INSERT INTO Specialty 
             (ID, Last_Name)
             VALUES
                        ('$key_of_new_entry', '$Last_Name')";

$result = MYSQL_QUERY($insert_query) or die("Can not execute query $insert_query. ");