Locking tables in mysql

Anon

hi
Iam doing a proj in php with mysql as the database.
Actually i want to Insert a value in a transaction table and get the max count of that table.
For this I have to lock the table.
But if that I lock the table and then insert it is not inserting even for the current thread of connection.
so please suggest me way to find the correct max count by using Lock method.
If that any other way of geting the max count please do tell me.
Thanx
with regards
kalyan

[deleted]

a) why do you think you need to lock the table?
b) mysql never "silently" ignores an insert. did you check the mysql_error() value after the insert?

Anon

hi thanx for ur consideration.
My problem is
the inserting table has an autoincrement field. I need to get the value from the autoincremental field for the data i have inserted, (This data iam fetching from someother table and inserting into this table) and insert into another table, that autoincremental field.
That is why i taught of using LOCK method here.
with regards
kalyan

[deleted]

When you insert a record into a table that has an auto_increment column, the new value for the auto_increment column can be found using the mysql_insert_id() function.

This value is stored per connection that means that if you insert a record you get value X, and if 10 other people insert records at the same time, you will still see the value that was created during your query.

Anon

hi
onceagain thanks for ur consideration.
Here we are writing queries using (adodb).
i.e., our queries should be compatible to all databases (such as oracle, sql,...).
But ur idea of using mysql_insert_id() wont work for other databases.
I have someother questions also for u,
1) Does Mysql support to ansi standards like other RDBMS (mysql,oracle).
2) Can we write queries that is comaptible to all databases, if there is a way tell me solution for that.

with regards
kalyan

[deleted]

Many databases have an equivalent to mysql_insert_id().

1) MySQL supports a subset of SQL92, with some extras

2) No. the SQL92 standard comes close, but not all databases support the full SQL92, and some databases have small differences from the SQL92 standard.

That is why you should seperate the SQL queries from the main code, and include that code depending on the database you use.
You'd have a group of queries for MySQL, and a group for Oracle, and a group for Informix. Then depending on which database you want to use, you'd include the group that is for that database.