0

I have looked through the similar questions here but have not been able to solve my problem…all help is greatly appreciated!

Basically, I have the following SQL code in my PHP:

$query = 'CREATE TEMPORARY TABLE tmp SELECT * from orders WHERE id='.$old_order_id.';
    ALTER TABLE tmp drop id; # drop autoincrement field
    UPDATE tmp SET groupID=null, timeOrdered=CURRENT_TIMESTAMP, totalOrderStatus=0; 
    INSERT INTO orders SELECT 0,tmp.* FROM tmp;
    DROP TABLE tmp;';
$db->exec($query);
$new_order_id = $db->lastInsertId();

Now, I would have thought that lastInsertId() would successfully return the id of the row that was last inserted into 'orders'. Instead, I get a zero--but I don't know whether this is because it is taking the last inserted ID of table 'tmp', or if it's because of some other mistake I'm making. (I don't see why it would take the id from tmp, since that's not the last inserted id really...)

Jo.P
  • 1,139
  • 4
  • 15
  • 35
  • 1
    ... Are you sure it's not returning the 0 you're inserting into `orders`? – Ignacio Vazquez-Abrams Nov 13 '14 at 23:00
  • Why are you using temp tables and multiple queries for this at all? This can be written as a single query. – Mike Brant Nov 13 '14 at 23:02
  • @IgnacioVazquez-Abrams Well...the actual id's of the inserted rows (in 'orders') is not 0. Shouldn't that be what is being returned? – Jo.P Nov 13 '14 at 23:04
  • @MikeBrant-I wasn't sure how to duplicate a row and found it being done like this online (here on SO, if I remember correctly). I didn't realize there's a better way, but I'm all ears! – Jo.P Nov 13 '14 at 23:05
  • @IgnacioVazquez-Abrams-I just tried it with 'null' instead of 0, and then with nothing there, and still got 0 as the result... – Jo.P Nov 13 '14 at 23:08

2 Answers2

1

It looks like you are just trying to copy an existing row into your table with some data modified. Why don't you simplify this and do this like:

INSERT INTO orders (
    /* fields you are changing */
    groupID,
    timeOrdered,
    totalOrderStatus,
    /* other fields from select below that are not changing */
    field1,
    field2,
    ...
)
SELECT
    /* values for fields you are changing */
    null,
    CURRENT_TIMESTAMP,
    0,
    /* other fields from order table that are to remain unchanged */
    field1,
    field2,
    ...
FROM orders
WHERE id = ?

Just don't INSERT a value for whatever you primary key field is (assuming it is autoincrement), then your insert id will work fine.

Mike Brant
  • 70,514
  • 10
  • 99
  • 103
  • So…the reason I want to duplicate it (as opposed to select and re-insert) is because I want this query to be able to survive any changes to the database that may happen in the future. So I don't want to tie the code to the specific columns that currently exist. – Jo.P Nov 13 '14 at 23:11
  • 1
    @Jo.P That's kind of a false choice. What if your database changes happen on the fields for which you are substituting values? You would still have to make a query change. – Mike Brant Nov 13 '14 at 23:14
  • That's true, I guess. But I would think that's less likely than, say, adding a column. You don't think it's worth the effort to do it like this? (I'm new at this stuff, as is probably clear… :P) – Jo.P Nov 13 '14 at 23:20
  • Going with your suggestion--tnx Mike! – Jo.P Nov 14 '14 at 03:44
  • 1
    @Jo.P To answer your last question. I think I have found that usage of `SELECT *` tends to be more problematic over time in one's code. For example, what if one added a column and this changed behavior in the code, introducing a bug? If one would have had columns specifically specified, he need not worry that an area of code that doesn't need this new column could be inadvertently broken. – Mike Brant Nov 14 '14 at 14:37
0

Check that the row is actually being inserted. If it's not being inserted, then it follows that there will be no new Id. This was what I discovered was happening and it turned out that I hadn't granted Insert rights for my user on the relevant table.

Richard Hunter
  • 1,933
  • 2
  • 15
  • 22