[thelist] SELECTing and auto_increment field

[rudy]

> The LIMIT 1 would return only the latest entry

that's right, as long as you don't forget the ORDER BY   ;o)

<tip>
never use a column name like "time" when creating
a database table -- it's too much like a reserved word
and will surely fail in some database 
</tip>


this whole business of getting back the last thing done
was a fascinating thread that i've only just now had a chance
to read, so i apologize for the lateness of this response

there seems to be a general perception that it is bad to
try to obtain the key of a just-inserted record by reading
back the max key --

    insert into xxx (pkey, ...) ...
    select max(pkey) from xxx

the thing that's bad about this has nothing to do with the
sql, and everything to do with multithreading

all you have to do to the above to make it copacetic 
is make it a transaction block

    begin block
       insert into xxx (pkey, ...) ...
       select max(pkey) from xxx
    end block

the nice thing about @@identity and mysql_insert_id() 
is that they are built into the insert statement, therefore the
select sql statement is not required, therefore there's only
one sql statement, therefore you don't need to use a block...

... which is handy if the database doesn't support blocks  ;o)

joshua pointed this out too, on another thread --
  "For example, it does not work in Access/Jet.  If you 
   try to do it, it says you need a semicolon.  So you add 
   the semicolon, and it says that there is an extra semicolon."

typical microsoft error message, eh?  

actually, makes sense, in a weird kind of way...


i wanted to make a comment about Bill's technique --

> My solution was to use a separate, two-column table 
> that contains the id of the last record edited, and the 
> name of the table it comes from. I update that last_id 
> field at the end of every INSERT or UPDATE. Then I 
> select it using the appropriate table name whenever 
> I need to refer to it. Seems convoluted, but it works.

sorry to say this, but unless you place those statements
into a block, you will suffer the same problems as the
multithreaded select max(pkey) technique

one day you are gonna produce bad data!
(which is the ultimate error, in the hierarchy of errors)

having said that...  

there are many reasons why one might need to 
have "transaction logging" tables in an application


rudy

p.s.
one of these days i simply must write that article about
what you might use instead of select max(pkey) -- i think 
i mentioned it in a tip some time ago, but it could use some 
nice examples to illustrate the concept...