php code to generating id on click button event

here i need to generate jod_id  which will be same for all the rows which we 'll insert on one click of submit button as same for next job it should be increment by one

I want output like as below

id       job_id        name
1          1                   a
2          1                  b
3          1                  c
4          2                  a
5           2                 b
6           2                 c
7           2                d


for new job new id should be created
so Please let me know the solution for this

Thank you
shruti Aweb developerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Julian HansenCommented:
Create a table called jobs
create table `jobs`( 
   `id` int NOT NULL AUTO_INCREMENT , 
   `createdate` timestamp DEFAULT CURRENT_TIMESTAMP , 
   `description` varchar(100) , 
   PRIMARY KEY (`id`)
 )

Open in new window


Start transaction
Insert your job into this table
Get the insert id
Use that to add to your target table.
Example

<?php
$host = 'localhost';
$user = 'me';
$password = 'password';
$db = 'db';
$mysqli = new mysqli($host, $user, $password, $db);

if ($mysqli->connect_errno) {
    printf("Connect failed: %s\n", $mysqli->connect_error);
    exit();
}
// Start transaction
$mysqli->begin_transaction(MYSQLI_TRANS_START_READ_WRITE);

// Add job to jobs table
$mysqli->query("INSERT INTO jobs (`description`) VALUES ('Job A')");

// Get the insert id
$id = $mysqli->insert_id;

// Add to second table
$mysqli->query("INSERT INTO otherTable (job_id, name) VALUES($id, 'Name Here')");

// Commit
$mysqli->commit();

$mysqli->close();

Open in new window

1

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
shruti Aweb developerAuthor Commented:
thank you i 'll try this
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
knowing your output but how's your source looks like?

what's the database are you using?
1
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

shruti Aweb developerAuthor Commented:
@Julian Hansen i don't want add description in jobs table ,just id , created_date , in another table description should be added
0
shruti Aweb developerAuthor Commented:
@Ryan Chong i'm using phpmyadmin
0
Julian HansenCommented:
i don't want add description in jobs table ,just id , created_date , in another table description should be added
I gave you an example of what a Jobs table might look like - I don't have sight on your code or what it is you are trying to do so I have to fill in the blanks.

You want a single number per Job - the correct way of doing that in a relational database is to create a table of Jobs. The DB does the allocation of the ID and you use that in your target table.

What details you choose to put in the jobs table is up to you - I just put description as an example.
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
@shruti A,

i'm using phpmyadmin

you got to explain further as we requested for more details.
1
NerdsOfTechTechnology ScientistCommented:
I agree with Julian:

You want a single number per Job - the correct way of doing that in a relational database is to create a table of Jobs. The DB does the allocation of the ID and you use that in your target table.

Essentially in the hypothetical design, you would have three tables to form 1:M (1 to many) between a 'middle' table which creates a fundamental M:M (many to many) relationship:

Worker | WorkerJob | Job


Worker table:
workerID (primary key)
nameFirst
nameLast
etc.

Job table:
jobID (primary key)
description
etc.

WorkerJob table:
workerjobID (primary key)
jobID (foreign key)
workerID (foriegn key)
startDate
endDate

This allows for information such as the workers 'name' to be in one table instead of multiple. Duplicated data is taboo in database management for a reason: to reduce human error as much as possible (e.g. typos). The information then becomes easily referenced by a single Worker or Job ID.

Some sources on the internet may suggest using MAX() to obtain the last ID; however, this is dangerous and incorrect. Julian presented the best practice way to retain the ID used in the transaction (LAST_INSERT_ID) as it is isolated to the transaction. MAX poses ID error/collision risks and should be avoided.

I also concur with Ryan, more information is needed.
0
shruti Aweb developerAuthor Commented:
ya i got to know thanks for your support thank you all
0
NerdsOfTechTechnology ScientistCommented:
I vote to split as follows or delete.
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.