Any SQL experts here?

On 5 Feb, 18:23, "Beauregard T. Shagnasty"
<a.nony.m....TakeThisOut@example.invalid> wrote:

> http://www.phpfreaks.com/quickcode/Get_the_ID_of_Last_MySQL_Entry/20.php

Eeewwww....

If you're going to read the last value returned by an identity column,
do it within SQL and do it within some sort of atomic transaction. The
idea of setting it in one line of PHP and then reading it in another
line is just icky. Who's to say another process hasn't incremented the
thing in the meantime?
 >> Stay informed about: Any SQL experts here? 

On Feb 5, 4:59 pm, SpaceGirl wrote:
> Quick SQL question:
>
> Table A contains articles
> Table B contains images that belong to articles
>
> Inserting a new record (article) into table A, which has an
> incrementing primary key. Several entries also need to be made in
> table B, containing the images that belong to the article in table A.
> How do I get the key value from A and put it in B at the same time as
> inserting A.
>
> Platform: mySQL 5.

Maybe add a new column to table A (the value could be time, so unique)
to use as primary key instead?
 >> Stay informed about: Any SQL experts here? 

On 5 Feb, 20:06, Mark Goodge <use....RemoveThis@listmail.good-stuff.co.uk> wrote:

> I've worked with PHP and Perl accessing MySQL and using
> LAST_INSERT_ID() on large (ISP level) installations and never had a
> problem with it yet.

Then they weren't large installations.

If you meet a DBA who _hasn't_ yet encountered some sort of obscure
problem caused by mis-use of code for reading allocated identity
values (usually other peoples' code), then they're still pretty low-
mileage.

This is just one reason why I avoid the things in favour of GUIDs.
Even for small systems that don't need them for performance reasons.
 >> Stay informed about: Any SQL experts here? 

SpaceGirl wrote:
> Quick SQL question:
>
> Table A contains articles
> Table B contains images that belong to articles
>
> Inserting a new record (article) into table A, which has an
> incrementing primary key. Several entries also need to be made in
> table B, containing the images that belong to the article in table A.
> How do I get the key value from A and put it in B at the same time as
> inserting A.
>
> Platform: mySQL 5.
>
> Me... stuck.
>

Miranda,

Check out mysql_insert_id() or SELECT LAST_INSERT_ID(). Either will
return the last autoincrement value for an insert on the current
connection (and are therefore multitasking-safe).

This is non-standard SQL - but virtually every SQL database has
something similar because the SQL standard hasn't seen fit to add it
(maybe the latest has - I haven't checked).

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex.RemoveThis@attglobal.net
==================
 >> Stay informed about: Any SQL experts here? 

"SpaceGirl" <nothespacegirlspam DeleteThis @subhuman.net> wrote in message
news:6b7a5cdf-bb8a-4dbc-ab6a-5495e61af5d7@h11g2000prf.googlegroups.com...
> Quick SQL question:
>
> Table A contains articles
> Table B contains images that belong to articles
>
> Inserting a new record (article) into table A, which has an
> incrementing primary key. Several entries also need to be made in
> table B, containing the images that belong to the article in table A.
> How do I get the key value from A and put it in B at the same time as
> inserting A.
>
> Platform: mySQL 5.


You can use the SQL function MAX( )

After you insert the data in to Table A, if your primary key is "ID" you can
do a:

SELECT MAX(id) as NewPK FROM Table_A


If you are using PHP, I do beleive they have a special function just for
this purpose:

mysql_insert_id()
http://www.w3schools.com/php/func_mysql_insert_id.asp
 >> Stay informed about: Any SQL experts here? 

Auggie wrote:
> "SpaceGirl" <nothespacegirlspam DeleteThis @subhuman.net> wrote in message
> news:6b7a5cdf-bb8a-4dbc-ab6a-5495e61af5d7@h11g2000prf.googlegroups.com...
>> Quick SQL question:
>>
>> Table A contains articles
>> Table B contains images that belong to articles
>>
>> Inserting a new record (article) into table A, which has an
>> incrementing primary key. Several entries also need to be made in
>> table B, containing the images that belong to the article in table A.
>> How do I get the key value from A and put it in B at the same time as
>> inserting A.
>>
>> Platform: mySQL 5.
>
>
> You can use the SQL function MAX( )
>
> After you insert the data in to Table A, if your primary key is "ID" you can
> do a:
>
> SELECT MAX(id) as NewPK FROM Table_A
>
>
> If you are using PHP, I do beleive they have a special function just for
> this purpose:
>
> mysql_insert_id()
> http://www.w3schools.com/php/func_mysql_insert_id.asp
>
>
>
>

Not reliable. What happens if two people insert at virtually the same
time? The first might get the value of the second.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex DeleteThis @attglobal.net
==================
 >> Stay informed about: Any SQL experts here? 

"Auggie" <Imperial.Palace RemoveThis @Rome.It> wrote:

> "SpaceGirl" <nothespacegirlspam RemoveThis @subhuman.net> wrote in message
> news:6b7a5cdf-bb8a-4dbc-ab6a-5495e61af5d7@h11g2000prf.googlegroups.com.
> ..
>> Quick SQL question:
>>
>> Table A contains articles
>> Table B contains images that belong to articles
>>
>> Inserting a new record (article) into table A, which has an
>> incrementing primary key. Several entries also need to be made in
>> table B, containing the images that belong to the article in table A.
>> How do I get the key value from A and put it in B at the same time as
>> inserting A.
>>
>> Platform: mySQL 5.
>
>
> You can use the SQL function MAX( )

EXTREMELY WRONG ADVICE.

> After you insert the data in to Table A, if your primary key is "ID"
> you can do a:
>
> SELECT MAX(id) as NewPK FROM Table_A

No, that's WRONG.

> If you are using PHP, I do beleive they have a special function just
> for this purpose:
>
> mysql_insert_id()

Which hopefully does:

SELECT LAST_INSERT_ID()

http://dev.mysql.com/doc/refman/5.0/en/information-
functions.html#function_last-insert-id

--
John Bokma http://johnbokma.com/
 >> Stay informed about: Any SQL experts here? 

SpaceGirl <nothespacegirlspam.RemoveThis@subhuman.net> wrote:

> Quick SQL question:
>
> Table A contains articles
> Table B contains images that belong to articles
>
> Inserting a new record (article) into table A, which has an
> incrementing primary key. Several entries also need to be made in
> table B, containing the images that belong to the article in table A.
> How do I get the key value from A and put it in B at the same time as
> inserting A.
>
> Platform: mySQL 5.
>
> Me... stuck.

To be honest, if you're stuck on basic stuff like this, you might want to
hire someone who has sufficient skills to do the database stuff for you. A
database is not a simple toy, there are so many places where it can go
wrong, and that means corrupt data at some future point, which are
extremely hard to hunt down.

Just my 2 cents.

--
John Bokma http://johnbokma.com/
 >> Stay informed about: Any SQL experts here? 

SpaceGirl wrote:

> Quick SQL question:
>
> Table A contains articles
> Table B contains images that belong to articles
>
> Inserting a new record (article) into table A, which has an
> incrementing primary key. Several entries also need to be made in
> table B, containing the images that belong to the article in table A.
> How do I get the key value from A and put it in B at the same time as
> inserting A.
>
> Platform: mySQL 5.

http://us3.php.net/mysql-insert-id

http://www.phpfreaks.com/quickcode/Get_the_ID_of_Last_MySQL_Entry/20.php

> Me... stuck.

<g>

--
-bts
-Google is your friend
 >> Stay informed about: Any SQL experts here? 

John Bokma wrote:
> SpaceGirl <nothespacegirlspam.TakeThisOut@subhuman.net> wrote:
>
>> Quick SQL question:
>>
>> Table A contains articles
>> Table B contains images that belong to articles
>>
>> Inserting a new record (article) into table A, which has an
>> incrementing primary key. Several entries also need to be made in
>> table B, containing the images that belong to the article in table A.
>> How do I get the key value from A and put it in B at the same time as
>> inserting A.
>>
>> Platform: mySQL 5.
>>
>> Me... stuck.
>
> To be honest, if you're stuck on basic stuff like this, you might want to
> hire someone who has sufficient skills to do the database stuff for you. A
> database is not a simple toy, there are so many places where it can go
> wrong, and that means corrupt data at some future point, which are
> extremely hard to hunt down.
>
> Just my 2 cents.
>

Ah, John, don't be so hard on her. She's just trying to learn. We all
have to start someplace.

And I've found SpaceGirl doesn't just throw out simple questions as a
rule. She tried to find them first. But this one could be hard to
find. It's obvious once you know the term, but if you don't, it can be
very difficult to find in the MySQL doc - there's too much of it1

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex.TakeThisOut@attglobal.net
==================
 >> Stay informed about: Any SQL experts here? 

Andy Dingley wrote:
> On 5 Feb, 18:23, "Beauregard T. Shagnasty"
> <a.nony.m....DeleteThis@example.invalid> wrote:
>
>> http://www.phpfreaks.com/quickcode/Get_the_ID_of_Last_MySQL_Entry/20.php
>
> Eeewwww....
>
> If you're going to read the last value returned by an identity column,
> do it within SQL and do it within some sort of atomic transaction. The
> idea of setting it in one line of PHP and then reading it in another
> line is just icky. Who's to say another process hasn't incremented the
> thing in the meantime?
>

Because mysql_insert_id() is guaranteed to get the id of the last
insertion of the current connection. Another process inserting rows
will have a different connection - and will get a different id.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex.DeleteThis@attglobal.net
==================
 >> Stay informed about: Any SQL experts here? 

mynameisnobodyodyssea.TakeThisOut@googlemail.com wrote:
> On Feb 5, 4:59 pm, SpaceGirl wrote:
>> Quick SQL question:
>>
>> Table A contains articles
>> Table B contains images that belong to articles
>>
>> Inserting a new record (article) into table A, which has an
>> incrementing primary key. Several entries also need to be made in
>> table B, containing the images that belong to the article in table A.
>> How do I get the key value from A and put it in B at the same time as
>> inserting A.
>>
>> Platform: mySQL 5.
>
> Maybe add a new column to table A (the value could be time, so unique)
> to use as primary key instead?
>
>

Time is not necessarily unique.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex.TakeThisOut@attglobal.net
==================
 >> Stay informed about: Any SQL experts here? 

Jerry Stuckle <jstucklex RemoveThis @attglobal.net> wrote:

> Ah, John, don't be so hard on her. She's just trying to learn. We
> all have to start someplace.

Like I said, this is extremely basic. Get a book and study MySQL. If those
problems are hard, you probably bump into much harder problems soon.

SQL is not something you learn by asking basic questions in a news group.
It takes quite some time and study to get it right.

> And I've found SpaceGirl doesn't just throw out simple questions as a
> rule. She tried to find them first. But this one could be hard to
> find. It's obvious once you know the term, but if you don't, it can
> be very difficult to find in the MySQL doc - there's too much of it1

A book on MySQL will explain this, and much more. It's not HTML were if
you get it wrong it still works.

Also, the best place to ask MySQL questions is in a MySQL related group.
In such a group it's extremely unlikely you get answers like "use MAX()".

--
John Bokma http://johnbokma.com/
 >> Stay informed about: Any SQL experts here?