Since you should still have the same information that you used to create
your entry in the candidate table you can do something like:
SELECT @candidateID := Candidate_ID
FROM candidate
WHERE Last_Name = '$lastname'
AND First_Name = '$firstname'
AND Middle_Initial = '$middle'
INSERT INTO resume (Candidate_ID, Section_ID, Section_Value)
VALUES (@candidateID, 1, '$field1'),
(@candidateID, 2, '$field2'),
(@candidateID, 3, '$field3'),
(@candidateID, 4, '$field4'),
(@candidateID, 5, '$field5'),
(@candidateID, 6, '$field6');
If you use this format, you have to worry about your field data (field1
through field6) messing with your single quotes. This is especially true
as I think those are user-entered fields, right? I can't think of it off
the top of my head but I believe that PHP already has a function that will
safely escape text strings so that they are INSERTable into MySQL using
single quotes (sorry for the brain freeze).
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Eve Atley" <[EMAIL PROTECTED]> wrote on 09/01/2004 03:02:21 PM:
>
> The question:
>
> Is there anything in MySQL that will allow me to determine, accurately,
the
> last auto-incremented field from a particular database, so I can then
insert
> based upon this into another table? What if 2 users input at the same
time?
> Please see the 'long explanation' for further details.
>
> ------------------------------------------------------------------------
>
> Long explanation:
>
> I've been reading over the previous thread for this. I am faced with a
> similar situation and would appreciate some syntax assistance. I am only
> partially finished.
>
> I have 2 tables to insert into:
> candidate
> resume
>
> When a new entry is entered via an INSERT statement I have set up, an
> auto-increment integer is assigned (Candidate_ID) in the 'candidate'
table.
> This insert is structured in PHP:
>
> $query = "INSERT INTO candidate (Last_Name, First_Name, Middle_Initial,
> Condition_Type, Employer, Country_ID, Visa_Status, Dt_Visa, MMDD_Birth,
SSN,
> CSG_Comments, Working, Available, Start_Date, Location, HoldOnPeriod,
> Relocation, Tech_Ranking, Comm_Ranking, Availability, Cert_Comments,
> Dt_Submitted, Def_Rate, Def_Rate_Unit, Other_Country, Currency_id,
> Interview_Availability, Interview_Contact, US_Experience,
Location_Country)
> values ('$lastname', '$firstname', '$middle', '', '$Employer', '', '',
> '$visa_dt', '$MMDD_Birth', '$ssn', '$csg_comments', '$working',
> '$Available', '', '$location', '', '$relocation', '$Tech_Ranking',
> '$Comm_Ranking', '', '$cert_comments', '', '$Def_Rate',
'$Def_Rate_Unit',
> '', '', '$interview_availability', '$interview_contact',
'$US_Experience',
> '')";
>
> $result = mysql_query($query) or die("Error: " . mysql_error());
>
----------------------------------------------------------------------------
> ----------
>
> Now, having entered that data, I need to insert different fields from my
> form into 'resume'; syntax is probably the same, but where I'm stuck is
how
> to determine what auto-incremented number was assigned to the entry in
> candidate, so I can then insert columns based on this.
>
> resume is structured as so:
>
> Field Type Null Default
> Candidate_ID int(10) Yes NULL
> Section_ID int(10) Yes NULL
> Section_Value longtext Yes NULL
>
> Scenario is as follows:
>
> New entry: James Brown, is assigned 10700 for Candidate_ID and is
inserted
> into table 'candidate'.
>
> Following this insert, insert is required for 6 rows of data into table
> 'resume':
> Candidate_ID must be 10700 / Section_ID = 1 / Section_Value = some form
data
> from textbox
> Candidate_ID must be 10700 / Section_ID = 2 / Section_Value = some form
data
> from a different textbox
> etc. until Section_ID 6.
>
> How would I go about setting this up for resume, now that it's set up
for
> candidate?
>
> Thanks,
> Eve
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
