Fetching the last row in a table with a single varchar field.

[prashanth023]

hi can anybody tell, how can i fetch last record from table.
In that table only one varchar type column is there like name

[code green]

What do you mean by `lasr record`?

[prashanth023]

Hi,
Last record means newly inserted record.
and we should get that record only not more than that .And that record should get by using select statement not the insert statement.

[code green]

Why not have another field of data type DATETIME?

[michaelb]

Why not have another field of data type DATETIME?

Because if DATETIME granularity is one second you still won't be able to identify the last record any time when more than one record inserted within the same second.

An auto_increment id seems like a better choice.

[prashanth023]

Yes. This is ok. can not we get last inserted record with out timestamp and auto increment.

Thank u for ur reply

[code green]

Sounds like you have met this before michaelb. In answer to the problem I don't see how this can be done. I have had the same problem with files rather than records. I got round it by adding a numeric suffix to the file. Could you tag a number onto the record entry then use string functions to strip out the numbers and compare them?

[balabaster]

What type of database are you using?

If you are using SQL Server you can use the Scope_Identity() value which records the identity of the primary key of the last inserted record within your scope. If someone inserts a record in a different scope, you still get your record back. Oracle also has a similar ability.

If you're using MS Access it becomes a little more complex as it doesn't support such a feature. You need to use a different means of doing this. If someone else inserts a record inside your time window then you run the risk of getting their record instead of yours. In order to alleviate that issue, you would need to lock the table on insert and not unlock it until you've retrieved the key to prevent someone else screwing up your app. This method has its own drawbacks that you need to consider.

For generally getting the last record, you just do "Select * From <Table_Name> Where <Primary_Key_Field> = (Select Max(<Primary_Key_Field_Name>) From <Table_Name>)". However, this one won't take into account records inserted from other scopes (i.e. by other users) in the period between your insert and your select.

[balabaster]

What type of database are you using?

If you are using SQL Server you can use the Scope_Identity() value which records the identity of the primary key of the last inserted record within your scope. If someone inserts a record in a different scope, you still get your record back. Oracle also has a similar ability.

If you're using MS Access it becomes a little more complex as it doesn't support such a feature. You need to use a different means of doing this. If someone else inserts a record inside your time window then you run the risk of getting their record instead of yours. In order to alleviate that issue, you would need to lock the table on insert and not unlock it until you've retrieved the key to prevent someone else screwing up your app. This method has its own drawbacks that you need to consider.

For generally getting the last record, you just do "Select * From <Table_Name> Where <Primary_Key_Field> = (Select Max(<Primary_Key_Field_Name>) From <Table_Name>)". However, this one won't take into account records inserted from other scopes (i.e. by other users) in the period between your insert and your select.

Sorry, didn't realise this was MySQL - I would use the same method using a table lock as I would in MS Access. Lock the table for your own use, complete the insert and corresponding select and then unlock the table.

[phillihp]

hi can anybody tell, how can i fetch last record from table.
In that table only one varchar type column is there like name

Using MySQL?? It's easy: LAST_INSERT_ID( ).

i.e.
1st Query:
INSERT INTO whatevertable (field1, field2) VALUES ('oh my', 'oh gee');

2nd Query:
UPDATE whatevertable set field1='cool stuff' WHERE id=LAST_INSERT_ID( )

[code green]

LAST_INSERT_ID() only works on AUTO_INCREMENT field. For reasons that defy belief prashanth023 has only one field in this table and it is a VARCHAR. At least that's how I understand the problem.

[Atli]

Why create a table with only one text field?
It is a basic rule of relational databases, as well as just common sence, to have a unique primary key for each table.

[jakekoh]

waho.. not sure if its too late..
you may do this;

$id = mysql_query("SELECT * FROM tablename ORDER BY id DESC");
$id = mysql_fetch_array($id);
$id = $id[id];
 

$id will now store the last inserted if without auto_increament enabled

[Atli]

@jakekoh
Hi.

A few notes on your code.

• Most importantly, this code fetches the entire table, just to get the highest ID listed. This is extremely inefficient, seeing as only a single integer out of all that data is actually needed.
  Your site is extremely likely to take a performance hit if you were to use this method.
  Use the LIMIT clause to reduce the number of rows returned.
  (See 12.2.8. SELECT Syntax in the manual)
• You should always try to avoid using the wild-card character (*) when specifying which columns to select, and rather specify which columns you need. That way MySQL does not have to return a bunch of data that you will never use. Also, by specifying the columns in the query, any errors in the column names will be treated as SQL errors, rather than PHP undefined index errors, which is arguably easier to debug.
• When you fetch associative array elements (elements named using a string), you should use a string. And all strings should be quoted.
  If you do not quote a string, PHP will assume it is a constant. If that constant does not exists, PHP may assume you meant to use a string and convert it for you, but you have no guarantee that this behavior will work consistently across different PHP versions, which could cause problems later on.
• Reusing the same variable ($id) over and over again, like you do, is a very bad idea from a design perspective.
  It makes the code very hard to read, and makes it very easy for you, or whoever may need to read your code later on, to confuse variables and make a mistake.
  You should always give your variables distinctive names, so their purpose and content can be easily identified.

So, with that in mind, I would try doing something more like:

$query = "SELECT `id` FROM `tablename` ORDER BY `id` DESC LIMIT 1";
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($result);
$id = $row['id'];

Note that I added a die() call in case the SQL query fails. This shows the error, if any, in case the SQL query fails.