Auto Increment

[nottoolate]

Hello,

 

I am creating a job setup form and have a need for the job id field to increment based on the last inserted job id into our Oracle database.

So far I have created a function that selects the maximum job id from the job table and adds 1 to the number.

 

However, I run into the problem of when a user is filling out the form and another user opens it and begins filling out the form they both will receive the same job id in the field. I'm assuming this could possibly be solved with sessions, but I'm not sure where to start.

 

Also, the form is not inserted into the table on submit (instead an excel sheet is created with the information and someone will enter the data into the database manually). Since the form data can be entered anytime from completion to next week/two weeks, how can I ensure that that job id is not used again.

 

Thanks

[scootstah]

I think your problem will be solved if you let SQL do the work for you.

INSERT INTO table (id) VALUES (id + 1);

[freelance84]

 

If the "job id" is the primary field with an auto increment you don't even have to do that, as it will auto increment itself:

 

$query_insert_new = mysql_query("INSERT INTO jobTable  (jobID, name, date)VALUES   (NULL,'$new_name',CURDATE())");

[scootstah]

 

If the "job id" is the primary field with an auto increment you don't even have to do that, as it will auto increment itself:

 

$query_insert_new = mysql_query("INSERT INTO jobTable  (jobID, name, date)VALUES   (NULL,'$new_name',CURDATE())");

 

He said he's using Oracle, which doesn't support that.

[kicken]

He said he's using Oracle, which doesn't support that.

 

Oracle supports sequences.  It's been a long time since I used it, but if these google results are accurate still it'd be something like:

 

first create the sequence

CREATE SEQUENCE jobAutoInc INCREMENT BY 1 START WITH 1;

 

Then, whenever you insert:

INSERT INTO jobs (jobId, Name, whateverElse) VALUES (jobAutoInc.NEXTVAL, 'blah', 'bleh');

 

If you need the id for later use in your code, you can first do a SELECT jobAutoInc.NEXTVAL FROM DUAL and grab the results, then use it in your insert and wherever else you need it.

 

[SergeiSS]

If you use ORACLE (or PostgreSQL), the syntax of INSERT could be different from MySQL's INSERT. Also, if id field is a sequence, don't worry about it! Just insert you data and return a new id.

 

INSERT INTO jobs (Name, whateverElse) VALUES ('blah', 'bleh') RETURNING jobID;

 

You get jobID as if you start SELECT query.

[nottoolate]

Thanks for the help everyone!

 

I'm not allowed to insert into the Oracle database, which I think would be really beneficial to getting this done correctly.

But I do have full access to our MySQL database and I think I can apply the solutions you have given with that.

 

 

[scootstah]

Okay, so you are inserting data into a MySQL database based on the last ID that was inserted into an Oracle database?

[SergeiSS]

nottoolate - could you explain more detail... In the beginning you say nothing about MySQL.

[nottoolate]

scootstah - Well currently I'm using the Oracle database to test the job numbering system, but we are planning to change to a new system in how we number our jobs. So we will start from the beginning using 0000, I figure I could just create a new table in MySQL and work with that. I'm assuming it would be easier?

 

SergeiSS - I noticed that. Our Oracle database contains all of our companies info (employee, jobs, invoices, etc.). This is what we utilize for mostly everything. I am not allowed to use inserts to get form data into this database, so everything is usually manually entered.

The MySQL database is only used by our intranet for administration (report, page, and tool access) purposes. None of the information you will find in the oracle database would be found in the mysql database. I do have full rights with this database so I can create and insert any data I would like...