sql question

Anon

okay, so i have 3 tables

master_list
(personid,name, email, phone, address)

subject
(subjectid, description)

subscription
(subscriptionid, subjectid, personid)

all the id fields are auto-increment, unique values.

i am using this to maintain a list of subscriptions to different mailing lists. I have a forum where I can enter the users details (name, email, phone, address) and then i check which lists i want them to belong to, then hit submit.

how do i go about inserting the data into the mysql database i'm using? I know i can use 2 different insert statements, but the problem i'm running into to (well, i don't understand) is that how do i insert all the correct data into the subscription table?

insert into master_list (name, email, phone, address) VALUES (name, email, phone, address)

then what should be my insert statement for the subscription table? first do i have to do a select on the master_list to get the person_id and then a select on the subject table to get the subject id? that's where i'm lost.

Anon

i should say that one of the problems i'm running into in my thinking process is that i'm passing this in my form

for ($count = 1; $row = mysql_fetch_row ($rResult); ++$count)
{
$numvalues = $count;
echo "$row[1] : <input type = \"checkbox\" name=\"sublists[]\" value=\"$row[1]\"><br>";
}

i'm using a checkbox for the subscriptions..so if i have say 3 out of 10 of them checked, it will pass those values that way.....

Anon

Try inserting a record to the database, and then doing a "SELECT LAST_INSERT_ID()". This will give you the value of the last written auto_increment field. That might help you 🙂

Anon

i tried that, but it returns 0, but if i look at the personid, it's value is 1. (and i only have 1 record in the db).

since i'm passing that array of checkboxes (i check each box that i want the person subscribed to...i use a hidden input field, and the following code to generate the checkboxes on the forum, based on what subjects are listed in the subject table)

for ($count = 1; $row = mysql_fetch_row ($rResult); ++$count)
{
$numvalues = $count;
echo "$row[1] : <input type = \"checkbox\" name=\"sublists[]\" value=\"$row[1]\"><br>";
}

?>
</p>
</td>
</tr>
<tr>
<td width="22%" nowrap>
<div align="left">
<input type = "hidden" name="count" value = <? echo $numvalues; ?>>

so i was thinking of doing something like

INSERT into master_list (last,first,title,email,phone,fax,mailing_addy,fedex_addy,org,area,topic,notes) VALUES ($last,$first,$title,$email,$phone,$fax,$mailing_addy,$fedex_addy,$org,$area,$topic,$notes)";

SELECT peron_id FROM master_list WHERE first LIKE '$first AND last LIKE '$last' AND title LIKE '$title' AND email LIKE '$email'

$sQuery3 = "SELECT subject_id FROM subject WHERE description LIKE $sublists[$x]";

of course, the LIKE $sublists[$x] won't work, so i'm sure there is a different way to do this