CODEWITHSUNDEEP
phpmysql
Cory Nickerson
Cory Nickerson

Reputation: 893

Reading auto increment column during INSERT INTO query

I was wondering if it is possible to read the value of a column that is set to auto increment for a row that is going to be inserted? I plan on encoding the order_id column for the row (auto increment primary key) to a transaction_id will be a publicly viewable ID.

So instead of the client searching http://website.com/order.php?order_id=3 it will read something like http://website.com/order.php?transaction_id=188117104.

Here is how I will be hashing the order_id to a publicly viewable transaction_id.

base_convert(  hash( "adler32", $string ), 36, 10 )

Is there a way I can get the order_id column so I can modify it to the transaction_id column before the query is executed to insert the record into the database?

Thanks.

EDIT 1:

Using mysql_last_id is it possible to do this in a single query?

<?php

// Insert records for product_orders
mysql_query('
    INSERT INTO ' . DB_PFIX . 'product_orders (
        order_transaction_id
    ) VALUES (
        "' . base_convert(  hash( "adler32", "LAST_INSERT_ID" + 1 ), 36, 10 ) . '"
    ), (
        "' . base_convert(  hash( "adler32", "LAST_INSERT_ID" + 1 ), 36, 10 ) . '"
    ), (
        "' . base_convert(  hash( "adler32", "LAST_INSERT_ID" + 1 ), 36, 10 ) . '"
    ), (
        "' . base_convert(  hash( "adler32", "LAST_INSERT_ID" + 1 ), 36, 10 ) . '"
    ), (
        "' . base_convert(  hash( "adler32", "LAST_INSERT_ID" + 1 ), 36, 10 ) . '"
    )
') or die (
    mysql_error()
);

?>

Upvotes: 0

Views: 68

Answers (1)

Jim Wharton
Jim Wharton

Reputation: 1415

MySQL doesn't support sequences so getting a NEXTVAL isn't easy. It's a pretty bad idea to attempt this as having more than one user creating inserts could lead to wrong IDs being returned. A better idea would be to do a two phased commit (or even return the last_insert_id to PHP and then perform another query) I'd go for a two phased transaction using the mysql_insert_id. Then do your logic and update the row.

Upvotes: 1

Related Questions