How can I reference ID during an INSERT?

[Reply-Via-Newsgroup Thanks]

Gentle People,

I think I read some where on how to do this, maybe I'm wrong.

I'm working on someone elses PHP code which updates an existing database
- One of the programmers used to generate a unique key (a transaction
code) for every purchase using a hash - A second programmer thought the
idea of using the unique numeric ID field, prefixed with the two letters
'Tx' would be more sensible. He did this by first making an insert to
the database with whatever transaction details he had, then getting the
mysql_insert_id() and then going back and updateing the exact record he
just inserted except to modify one field/column with the value he got
from his mysql_insert_id() prefixed with the letters "Tx".

I am pretty sure I can do this all in one go and I'd appreciate if
anyone can help direct me here...

basically, I want to do something like

mysql> insert myTable set firstname="elvis",txcode='tx'+ID;

Where ID is the ID of the new/active record being inserted.

The insert works, except txcode equals zero - when the unique ID for the
row is/was 2795 - and it does not even have a 'tx' prefix which leads me
to believe its performing some maths to my request.

Can what I want to do be done in a single step, or must I go back and
update the record after the insert?

All help, via the newsgroup please (to help others) is greatly appreciated,

thanks
randelld

[Pedro Graca]

Reply-Via-Newsgroup Thanks wrote:
(snip)

basically, I want to do something like

mysql> insert myTable set firstname="elvis",txcode='tx'+ID;

Where ID is the ID of the new/active record being inserted.

[ partial non-answer ]
Why? (Yes, I know you are changing someone else's code)

Isn't if *far* better to /not/ duplicate information in the database?
Instead of Isn't it better to have
id | txcode | etc id | txcode | etc
----+--------+----- ----+--------+-----
17 | tx17 | ... 17 | tx | ...
71 | tx71 | ... 71 | tx | ...

You can always "select concat(txcode, id)" from the table on the right
to obtain "tx17" and "tx71"
And, if the column txcode is filled with "tx" for all lines, simply
remove it from the database :)
--
USENET would be a better place if everybody read: : mail address :
http://www.catb.org/~esr/faqs/smart-questions.html : is valid for :
http://www.netmeister.org/news/learn2quote2.html : "text/plain" :
http://www.expita.com/nomime.html : to 10K bytes :

[Reply-Via-Newsgroup Thanks]

Pedro Graca wrote:

Reply-Via-Newsgroup Thanks wrote:
(snip)

basically, I want to do something like

mysql> insert myTable set firstname="elvis",txcode='tx'+ID;

Where ID is the ID of the new/active record being inserted.

[ partial non-answer ]
Why? (Yes, I know you are changing someone else's code)

Isn't if *far* better to /not/ duplicate information in the database?
Instead of Isn't it better to have
id | txcode | etc id | txcode | etc
----+--------+----- ----+--------+-----
17 | tx17 | ... 17 | tx | ...
71 | tx71 | ... 71 | tx | ...

You can always "select concat(txcode, id)" from the table on the right
to obtain "tx17" and "tx71"
And, if the column txcode is filled with "tx" for all lines, simply
remove it from the database :)

Everything you've said is very true and something I've already
considered - However there are limits being imposed on the bounds of my
project which I must follow - I am not permitted to edit other code that
depends on this txcode - Thus, while your suggestion is valid and
helpful, and to all accounts, simple to implement - if I were to use
your suggestion, txcode would no longer be unique and therefore make
other dependancies fail.

Therefore, have you (or anyone else) any idea on how I can actively copy
the id column to the txcode column and prefix it with 'tx' ?

Thanks in advance,
randell d.

[Lüpher Cypher]

Reply-Via-Newsgroup Thanks wrote:

Gentle People,

I think I read some where on how to do this, maybe I'm wrong.

I'm working on someone elses PHP code which updates an existing database
- One of the programmers used to generate a unique key (a transaction
code) for every purchase using a hash - A second programmer thought the
idea of using the unique numeric ID field, prefixed with the two letters
'Tx' would be more sensible. He did this by first making an insert to
the database with whatever transaction details he had, then getting the
mysql_insert_id() and then going back and updateing the exact record he
just inserted except to modify one field/column with the value he got
from his mysql_insert_id() prefixed with the letters "Tx".

I am pretty sure I can do this all in one go and I'd appreciate if
anyone can help direct me here...

basically, I want to do something like

mysql> insert myTable set firstname="elvis",txcode='tx'+ID;

Where ID is the ID of the new/active record being inserted.

The insert works, except txcode equals zero - when the unique ID for the
row is/was 2795 - and it does not even have a 'tx' prefix which leads me
to believe its performing some maths to my request.

Can what I want to do be done in a single step, or must I go back and
update the record after the insert?

All help, via the newsgroup please (to help others) is greatly appreciated,

thanks
randelld

I had the same problem some time ago.. As far as I remember, I got rid
of it by sending a query to the db:

$result = mysql_query("last_insert_id()");
$row = mysql_fetch_row($result);
$lastId = $row[0];

For some reason the php's function returns 0..

[Data Goob]

Example:

$user_id is an int, autoincremented column on my table...

$query = "insert blah blah blah..." ;

$result = mysql_query( $query, $connection ) or die ( "Error in query: $query: "
.. mysql_error());

$user_id = mysql_insert_id() ;
-DG-
Lüpher Cypher wrote:

Reply-Via-Newsgroup Thanks wrote:

Gentle People,

I think I read some where on how to do this, maybe I'm wrong.

I'm working on someone elses PHP code which updates an existing
database - One of the programmers used to generate a unique key (a
transaction code) for every purchase using a hash - A second
programmer thought the idea of using the unique numeric ID field,
prefixed with the two letters 'Tx' would be more sensible. He did
this by first making an insert to the database with whatever
transaction details he had, then getting the mysql_insert_id() and
then going back and updateing the exact record he just inserted except
to modify one field/column with the value he got from his
mysql_insert_id() prefixed with the letters "Tx".

I am pretty sure I can do this all in one go and I'd appreciate if
anyone can help direct me here...

basically, I want to do something like

mysql> insert myTable set firstname="elvis",txcode='tx'+ID;

Where ID is the ID of the new/active record being inserted.

The insert works, except txcode equals zero - when the unique ID for
the row is/was 2795 - and it does not even have a 'tx' prefix which
leads me to believe its performing some maths to my request.

Can what I want to do be done in a single step, or must I go back and
update the record after the insert?

All help, via the newsgroup please (to help others) is greatly
appreciated,

thanks
randelld

I had the same problem some time ago.. As far as I remember, I got rid
of it by sending a query to the db:

$result = mysql_query("last_insert_id()");
$row = mysql_fetch_row($result);
$lastId = $row[0];

For some reason the php's function returns 0..

[Lüpher Cypher]

Data Goob wrote:

Example:

$user_id is an int, autoincremented column on my table...

$query = "insert blah blah blah..." ;

$result = mysql_query( $query, $connection ) or die ( "Error in query:
$query: " .. mysql_error());

$user_id = mysql_insert_id() ;

Try to change
$user_id = mysql_insert_id();
to
$result = mysql_query("last_insert_id()");
$row = mysql_fetch_row($result);
$user_id = $row[0];

If that works, I'd simply wrap it as a function.

[Data Goob]

So writing 3 extra lineths of code is better than what I thuggested.

Thankths Lupher.

Lüpher Cypher wrote:

Data Goob wrote:

Example:

$user_id is an int, autoincremented column on my table...

$query = "insert blah blah blah..." ;

$result = mysql_query( $query, $connection ) or die ( "Error in query:
$query: " .. mysql_error());

$user_id = mysql_insert_id() ;

Try to change
$user_id = mysql_insert_id();
to
$result = mysql_query("last_insert_id()");
$row = mysql_fetch_row($result);
$user_id = $row[0];

If that works, I'd simply wrap it as a function.