[thelist] Free Tip about SQL

[Seb Potter]

> If you simply want the highest id number then yes. But if you want the
> id of a record you just inserted you'll have to use the WHERE clause.
>
> It's possible that before you turn around and ask the database that
> question (your query above) another record has already been added.
>
> I usually do something like:
>
> SELECT id
> FROM users
> WHERE username = 'chris'
> 	AND coolfactor = 'infinite'
> 	AND babemagnet = 'yes';
>
> (we are assuming that) chris, infinite, and yes all came from
> information that was passed to a form on a website.

Actually, this isn't guaranteed to work at all, simply because you're 
assuming that your record is unique. In a talbe with a large number of 
fields, you really don't want to be doing a where clause on every field. 
(Sure as hell on on large text or binary fields, performance will drop like 
shares in Enron.)

Stick to whatever mechanism your DB provides. It might not be as 100% 
portable, but it is guaranteed to work every time, which all of these other 
methods are not.

- seb


-- 
http://poked.org