INSERT MySQL Query

I have two different tables.  One for  Job Numbers.

The Table jobNumbers has a field job_id that is auto increment.

If I write a  MySQL Query like:
INSERT INTO jobNumbers (job_id, first_name, last_name, date) VALUES ('', 'John', 'Doe', 'NOW()')

Open in new window


When the query runs the Job ID is automatically generated and entered into the DB. How do I grab that specific Job Id number? so I can run a second query before it moves onto the next?
 Also, If I had 10 or so jobs going in how would I grab each new job_id before it went to the next so I could use those job_id' fo a different query.

INSERT INTO jobNumbers (job_id, first_name, last_name, date) VALUES ('', 'John', 'Doe', 'NOW()')

$id = $row['job_id'];

INSERT INTO jobCategory (job_Cat_id, job_name date) VALUES ('$id', 'WORK', 'NOW()')

Open in new window

LVL 8
rgranlundAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
this is the function you want to use: LAST_INSERT_ID()
http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id
btw, you don't want to use 'NOW()', but NOW() ?!

so, in 1 single mysql_query, you can do both inserts (to avoid the 2 server round-trips):
INSERT INTO jobNumbers (job_id, first_name, last_name, date) VALUES ('', 'John', 'Doe', NOW())
INSERT INTO jobCategory (job_Cat_id, job_name date) SELECT LAST_INSERT_ID(), 'WORK', NOW()) 

Open in new window

0
GaryCommented:
INSERT INTO jobNumbers (job_id, first_name, last_name, date) VALUES ('', 'John', 'Doe', 'NOW()')

$id = mysql_insert_id();

INSERT INTO jobCategory (job_Cat_id, job_name date) VALUES ('$id', 'WORK', 'NOW()') 

Open in new window


But I recommend you move over to PDO
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rgranlundAuthor Commented:
@GaryC123 what is PDO?
0
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

GaryCommented:
PDO and MySQLi are the two new db access drivers for PHP, the old mysql_query has been/will be dropped from all future PHP releases, tho still currently supported.
http://php.net/manual/en/ref.pdo-mysql.php

Makes for easier queries and prevention of injection.
0
COBOLdinosaurCommented:
You will find most of what you need to know about migrating to pdo or mysqli in Ray's first class article

Cd&
0
Ray PaseurCommented:
You might consider getting the Welling/Thompson book mentioned in this article.  It shows a great many useful things about how to use PHP and MySQL (no matter which extension you choose).
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11769-And-by-the-way-I-am-new-to-PHP.html
0
Ray PaseurCommented:
@rgranlund: I recommend you test the solutions posted here at EE.  It is traditional to award at least SOME of the points to the first correct solution.  I believe you would have found the solution from AngelIII to be correct.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28233930.html#a39474897
0
GaryCommented:
Valid point Ray, but I think Angel's solution was maybe more confusing and I don't believe LAST_INSERT_ID can be relied upon within separate queries.
Correct me if I am wrong
I would be happy with a question reopen and points split.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.