help with insert query

neilwatson

I have these tables:

jobs:
jobid int auto-increment
position varchar(20)
dept int
description text

dept:
deptid int auto-increment
dept varchar(10)

Consider this mysql select statement:

SELECT * FROM jobs,dept WHERE (jobs.dept=dept.deptid)

The jobs table contains the dept id number and the dept table contains the dept id number and the dept name (e.g. Engineering). Using this statment, I can list the full dept name from the dept table while listing the info in the jobs table.

Question: how would I use this syntax for an insert statement. I have the full dept name, but I'd like to insert the dept id number into jobs. I tried this but was not successful:

INSERT INTO jobs,dept ('position','dept','description) WHERE jobs.dept=dept.deptid values ('$position','$dept','$desc')

Suggestions?

Lars_Berg

Insert is used for inserting into one table at a time. Thus, if you need to insert into two tables, you need two separate insert statements.

If you want the operation to be atomic, enclose the statements in a transaction with some error handling.

neilwatson

It don't want to insert into two tables. I want to insert postion and description from strings in my program. Dept is an int howerver, I have it stored as the full name in my program. The dept table shows the relationship between the dept number and the deptid.

Without having a separate query, is there away to have mysql insert the deptid given that I have the dept name?

Lars_Berg

You mean

$q= "insert into jobs(position, dept,description ) select  '$position',deptid,'$desc' from dept where dept = '$dept'";

neilwatson

No, I mean:

$position = "Network Administrator";
$desc = "job description text...";
$dept = "eng";

Now $position and $desc can go straight into the jobs table. For $dept I need to consult the dept table and get the deptid that matches the string eng:

deptid dept
1 hr
2 eng
3 sales

The deptid goes into the jobs table with the other variables. Can this be done with only one INSERT query?

Lars_Berg

Did you try my proposal?

neilwatson

After a little debugging, yes your suggestion worked.

Thank you.