Obtain last job_ref number from mysql table

nawthun · 12-17-2008, 04:22 PM

I have 22 fields in my table and would like to sort the job_ref (number) field and obtain the last number used and display it. Can anyone help me with something simple for this?

Tks,
Delirious Daydreamer

scheidel21 · 12-18-2008, 01:32 AM

Well your SQL statement is fairly easy it will be something like the following

Code:

SELECT MyTable.job_ref FROM MyTable ORDER BY job_ref;

But now getting the one you want may be more difficult, the SQL above should sort in ascending order if the job_ref number an autonumber where the highest one will be the most recent or is there some method as to how it is assigned. for instance I have a table with an arbitrary ID that is an autonumber it auto increments so the highest number will be the newest record but in this table I also have an employeeID field this is an arbitrary number I assign, the highest employeeID may not be the most recent record. If I want the most recent employee added I would need to have code like below to get both fields and use the autonumber field to identify the newest record and the employeeID field to get the most recent employee added.

Code:

SELECT MyTable.ID, MyTable.EmpID FROM MyTable ORDER BY ID;

Let me know if this helps at all or you would like some more advice, your request is a little unclear so I may have misinterpreted what you are asking for.

nawthun · 12-19-2008, 03:33 PM

Quote:

Originally Posted by scheidel21

Well your SQL statement is fairly easy it will be something like the following

Code:

SELECT MyTable.job_ref FROM MyTable ORDER BY job_ref;

But now getting the one you want may be more difficult, the SQL above should sort in ascending order if the job_ref number an autonumber where the highest one will be the most recent or is there some method as to how it is assigned. for instance I have a table with an arbitrary ID that is an autonumber it auto increments so the highest number will be the newest record but in this table I also have an employeeID field this is an arbitrary number I assign, the highest employeeID may not be the most recent record. If I want the most recent employee added I would need to have code like below to get both fields and use the autonumber field to identify the newest record and the employeeID field to get the most recent employee added.

Code:

SELECT MyTable.ID, MyTable.EmpID FROM MyTable ORDER BY ID;

Let me know if this helps at all or you would like some more advice, your request is a little unclear so I may have misinterpreted what you are asking for.

Hi,

Tks for responding. I'm a newbie with Php & Mysql. I have an "Add New Client" form with 20+ fields where Admin can enter data for new clients. To keep it simple silly (or stupid) KISS I want to:
1. Search the job_ref field and obtain the last number which is in IMP0001, IMP0002, IMP0003, etc. format;
2. Then increment that number by 1 and present it on the blank form (rather than having to keep a separate paper log of the last number used);

I can't get it to do anything worthwhile and was thinking maybe I should drop the IMP prefix from the job_ref field and stick to using numbers only? I guess I could always use a separate procedure to afix the IMP for display purposes.

Tks in advance for any help.

Poetics · 12-19-2008, 11:07 PM

Muuuuch easier to just let MySQL's auto-increment feature do its job.

You can call the column IMP or whatever you'd like, but as long as it's set to auto_increment, it'll keep track without you having to do so manually. I can't think of a numeric index I haven't used this for.

nawthun · 12-21-2008, 07:06 PM

Quote:

Originally Posted by Poetics

Muuuuch easier to just let MySQL's auto-increment feature do its job.

You can call the column IMP or whatever you'd like, but as long as it's set to auto_increment, it'll keep track without you having to do so manually. I can't think of a numeric index I haven't used this for.

Thanks very much for your response. I had finally come to that conclusion. It is rather silly to have a separate field for this sort of function and just by using the id field, that makes it SOOO much simpler.
Thanks again.