• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 69
  • Last Modified:

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
0
shruti A
Asked:
shruti A
  • 4
  • 2
  • 2
  • +1
5 Solutions
 
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
 
shruti AAuthor Commented:
thank you i 'll try this
0
 
Ryan ChongCommented:
knowing your output but how's your source looks like?

what's the database are you using?
1
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
shruti AAuthor 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 AAuthor 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 ChongCommented:
@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 AAuthor 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now