[Resolved] Give users a job_ref no.

Calahan

It's probably because I've been at this all day again and the early hours are slowing my brain but I'm stumped at the moment on the following:

I have a MySQL DB with 2 tables. One called jobdetails, one called employees. Basically the employees log jobs with an interactive form. At the end of logging the job, the user will be given a unique job ref number. I have a field within the jobdetails table called job_no. This is an auto incremental field so that I can make each job unique. This is the number that I want to give to users as a reference number. At the back of my mind while I was working on other parts of this project I was thinking that I could use the primary key field in the table jobdetails called username or perhaps the date. Neither of these will work though as the user can log more than one job in a day. Using this method would return lots of ref numbers. I'm thinking that I'll possibly need to use the LIMIT option in the query but I'm not too sure so if any of you would like to point out the obvious, I can then marvel at my incompetence in about 6 hours time when I get up.
🙂
Cheers.

easethan

I dunno about anyone else, but I find that a bit confusing...Could you please clarify what you're trying to do?

You really must be tired :p

Calahan

Ok, that was a tad long winded so to cut it down to basics.
I have a table called jobdetails with a field called job_no. This field is a Primary Key and an auto incremental field. As users submit jobs, the job_no field is assigned an auto-generated number. I want to let the users know what this number is after they log a job.
I can't think of how to word the query so that I only bring back the number associated with the job just logged.

An example of the way I'm thinking is this:

User logs a job and it is given the job_no of 22. I can write a query using something like

SELECT job_no from jobdetails where ____________

This is where I'm stumped. I could use

SELECT job_no from jobdetails WHERE DATE='$_POST['date']'

but that will bring back all the job_no's for a particular date. Alternatively I could use

SELECT job_no from jobdetails WHERE username=$_POST['username']

but this would bring back all job_no's for the user currently logging a job.
Am I being really stupid? I think possibly I am so if someone could point my in the right direction, I'd appreciate it.

[deleted]

You are looking to retrieve the last id. There is a function for that:

SELECT mysql_last_id from jobdetails

cgraz

You could also use mysql_insert_id().

Cgraz

Calahan

Thanks for the info. Looks like thats just what I need.

easethan

you guys beat me to the punch, that's what I thought, but didn't want to embarrass myself by telling you something you weren't asking for.

I do this often and all I do is

$job_no = mysql_last_id();

echo "Your job number is $job_no.";

The mysql_last_id function selects the last inserted row, then finds a feild(there can only possibly be one) that is an auto_increment. No way to go wrong.

Calahan

Haven't implemented this yet and I know I've just marked this as "Resolved" but what would happen if the database was processing lots of queries at once and someone logged a job at the same time someone else was logging a job? Is there a possibility that one user might get the other user's job number?

bubblenut

It's the last insert id of the particular connection. So in this case it would be the last insert id of this particular instance of the script, if that makes sense?
Bubble

easethan

I don't think that's a possibility because, as bubblnut said, it's from that specific connection.