Retrieving last record

viraj

There is a column ID, i want to put E001, E002,. ....... and so on

I want to retrieve the last record of the column ID and increment it by 1. Can some1 please tell me the codes please

Houdini

Could you maybe should how the table structure is set up, cause an id field or column is normally set up as an int or smallint or maybe even a tinyint (must not have much to put there) so if you have an id of E001...Ewhatever it would be a varchar most likely. but you could try mysql_num_rows() for getting the number of rows in a field in this case id here is a link mysql_num_rows

Roger_Ramjet

The simplest way to get the highest value in a field is this

$sql = "SELECT id_col FROM table ORDER BY id_col DESC LIMIT 1";

That will return one record, the one with the highest value in id_col according to sort order. (A smart db engine would not even need to access the table but return the value from the index on the column. Don't ask me if mysql can do that)

Drop the prefix E, cast the rest to integer and add 1, cast that back to a string and prepend the E.

viraj

Well the situation is as follows:

I have a table student with fields ID, Firstname, Lastname and so on.
ID Firstname Lastname
E001

There is a form for registering student, the field ID in the form is readonly. Whenever a student registers himself, the last record of the column ID is retrieved and it is incremented by 1. Then the user has just to enter his personal details and click on the button register. The next record in the Table will then be E002. I just want to know the code for retrieving the last record. plz help me out, am stuck. Thanks

Roger_Ramjet

I gave you the query to retrieve the last record. Maybe I confused you by also telling you how to increment it by 1. Now do you mean 'give you the code to connect to the database, run the query and display the result'?

viraj

I just wanted the code to run the query to retrieve the last record, if you are familiar with ASP, there is the rs.EOF to get to the last record. I have been doing ASP for some time and now am learning PHP. So dont know how to get to the last record. Thanks in advance for your help

Roger_Ramjet

 $sql = "SELECT id_col FROM table ORDER BY id_col DESC LIMIT 1";
$result=mysql_query($sql);
$row = mysql_fetch_array($result);
$last_id = $row['id_col'];

Don't need to worry baout .eof, you are only selecting 1 record anyway.