Link to home
Start Free TrialLog in
Avatar of Robert Granlund
Robert GranlundFlag for United States of America

asked on

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

Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

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

ASKER CERTIFIED SOLUTION
Avatar of Gary
Gary
Flag of Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Robert Granlund

ASKER

@GaryC123 what is PDO?
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.
You will find most of what you need to know about migrating to pdo or mysqli in Ray's first class article

Cd&
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).
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11769-And-by-the-way-I-am-new-to-PHP.html
@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.
https://www.experts-exchange.com/questions/28233930/INSERT-MySQL-Query.html?anchorAnswerId=39474897#a39474897
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.