Have second field contain the ID

[jxrd]

Hi all,

quick question, is there a way to have two fields contain the auto-generated ID?

 

For example, I tried this:

mysql_query("INSERT INTO `table` (`ID`, `SecondID`) VALUES (NULL, '".mysql_last_insert()."')");

But it doesn't seem to work. SecondID is always 0.

 

Does anyone know how to do this?

 

Thanks.

[fenway]

Don't you mean mysql_insert_id?

[jxrd]

Oh yeah, that's what I meant. I'm using the correct function...but it's still returning 0.

 

I also tried mysql's native function to do so, but it didn't work either.

[J.Daniels]

mysql_last_insert_id() only returns an id if there was a previous INSERT statement.  Unless you have performed an INSERT before your code, it will return 0.  If you want both columns to have the same id, you will have to do the INSERT, then UPDATE the record with mysql_last_insert_id().

 

If this is what you are looking for, I am curious as to why you would need two columns with the same id.

[jxrd]

I thought mysql_insert_id() returned the last auto-generated ID number? Not necesserily the last insert....

 

Well, for the forum I'm making, I have boards set up so that if the parent board id is the same as its id, then it is a parent board, and if the parent board id is not the same as its id, it's a child of its parent board id. If you get me lol.

 

So yeah...basically I just need the two fields the same. That's what I'm doing at the moment; running an update after the insert...but it just seems as though there should be a more efficient way of doing this.

 

[mentalist]

Just a hunch, but if mysql uses an internal 'global' pointer to the last insert id, then as it's starting the insert, it'll reset the id to null (0). But technically i'd say that mysql_insert_id() should be returning a result before that statement is sent to mysql. Therefore i'd have to ask if the previous insert was successful and do you print out the result to prove the fact?

 

$id = mysql_insert_id();
echo 'id: '.$id.'<br />';
mysql_query("INSERT INTO `table` (`ID`, `SecondID`) VALUES (NULL, '".$id."')");

[rhodesa]

the UPDATE after the insert is the only way to do it. if it was me though, i would modify my code logic so a parent is represented by a NULL for the SecondID

[jxrd]

Well, I'm trying to do it on insert...so I can can't really echo out the id in the middle of a query lol.

 

It works fine after the insert - as in, if I echo out mysql_insert_id() after the query, it returns the correct ID. It just doesn't seem to be working in the query.

 

And yeah, the insert is successful.

 

the UPDATE after the insert is the only way to do it. if it was me though, i would modify my code logic so a parent is represented by a NULL for the SecondID

Oh right...that's a shame. And yeah...I did do that originally. But I had a problem with threads where if a user went to a thread, with the id of 0, it would show all threads in the single thread...which is not supposed to happen lol.

 

So I changed it to point to itself to prevent that. Ahh well...I guess I'll just have to run the update query afterwards.

 

Thanks for all your help.

[mentalist]

So does that work then?

$id = mysql_insert_id();
mysql_query("INSERT INTO `table` (`ID`, `SecondID`) VALUES (NULL, '".$id."')");

If so, it'll be something to do with how the statement is evaluated internally (probably by php rather than mysql).

[J.Daniels]

It works fine after the insert - as in, if I echo out mysql_insert_id() after the query, it returns the correct ID. It just doesn't seem to be working in the query.

 

It doesn't work in the query because the query hasn't happened yet.

[jxrd]

It works fine after the insert - as in, if I echo out mysql_insert_id() after the query, it returns the correct ID. It just doesn't seem to be working in the query.

 

It doesn't work in the query because the query hasn't happened yet.

 

Well, I thought mysql_insert_id() fetched the last generated ID, in which case it would have already happened by the time I get round to inserting into the parent board field.

 

And @Mentalist...I don't think that would work, since it would be getting the ID from the last insert, not necessarily the one that has just taken place...which could be anything.

[rhodesa]

also...you could leave SecondID blank and then modify your SELECT to have an IF:

 

SELECT ID,IF(SecondID IS NULL,ID,SecondID) as SecondID FROM table

[mentalist]

And @Mentalist...I don't think that would work, since it would be getting the ID from the last insert, not necessarily the one that has just taken place...which could be anything.

 

Ah, well then, in reality it hasn't taken place, because the function mysql_insert_id is technically called (expanded) before the query is made... If your trying to do what I think you are, why not use an 'auto increment' column? But unless it's the last insert query that you really want, I can't see the point...

[J.Daniels]

It works fine after the insert - as in, if I echo out mysql_insert_id() after the query, it returns the correct ID. It just doesn't seem to be working in the query.

 

It doesn't work in the query because the query hasn't happened yet.

 

Well, I thought mysql_insert_id() fetched the last generated ID, in which case it would have already happened by the time I get round to inserting into the parent board field.

 

 

Sorry.. I meant that it wouldn't work in this query:

 

mysql_query("INSERT INTO `table` (`ID`, `SecondID`) VALUES (NULL, '".mysql_last_insert()."')");

[jxrd]

also...you could leave SecondID blank and then modify your SELECT to have an IF:

 

SELECT ID,IF(SecondID IS NULL,ID,SecondID) as SecondID FROM table

 

That means extra code in loads of queries...if I could get this to work, it'd mean a little bit of extra code in one.

 

And @Mentalist...I don't think that would work, since it would be getting the ID from the last insert, not necessarily the one that has just taken place...which could be anything.

 

Ah, well then, in reality it hasn't taken place, because the function mysql_insert_id is technically called (expanded) before the query is made... If your trying to do what I think you are, why not use an 'auto increment' column? But unless it's the last insert query that you really want, I can't see the point...

If for some reason the ID auto incremented and the parent board field did not, that could potentially throw every thread out of place...I'd rather not rely on that not happening

 

Yeah...is there no way I can get the data of the last id generated? If not, this seems like something that mysql should look into doing...

[mentalist]

I'm lost!

 

Are you saying that both the values should be the same?

 

Are you trying:

mysql_query("INSERT INTO `table` (`ID`, `SecondID`) VALUES ('', ID)");

 

 

Is the ID column defined as auto increment ? (in whichever table you wanting it from)

[jxrd]

Yeah Lol!!

 

ID is auto increment, I basically just want to copy its value on the insert. This is instead of running an update query straight afterwards.

 

mysql_query("INSERT INTO `table` (`ID`, `SecondID`) VALUES ('', ID)");

But that doesn't work

 

Glad you get it though lol.

[mentalist]

So, you want two columns that are identical?

[jxrd]

I do; two columns that have the exact same value. But without having to run an update query afterwards

[mentalist]

Why not set it to NULL, then if at a later date you want to update/change it's association, then just check to see if it's NULL, else use ID.

 

Or, if there always to be the same (redundant data use), then why not try setting both columns to 'auto inc', (not that i've ever tried it, might have to drop the 'primary' bit though).

[jxrd]

also...you could leave SecondID blank and then modify your SELECT to have an IF:

 

SELECT ID,IF(SecondID IS NULL,ID,SecondID) as SecondID FROM table

 

That means extra code in loads of queries...if I could get this to work, it'd mean a little bit of extra code in one.

 

And @Mentalist...I don't think that would work, since it would be getting the ID from the last insert, not necessarily the one that has just taken place...which could be anything.

 

Ah, well then, in reality it hasn't taken place, because the function mysql_insert_id is technically called (expanded) before the query is made... If your trying to do what I think you are, why not use an 'auto increment' column? But unless it's the last insert query that you really want, I can't see the point...

If for some reason the ID auto incremented and the parent board field did not, that could potentially throw every thread out of place...I'd rather not rely on that not happening

 

Unfortunately, for them reasons, that wouldn't be very practical...

 

And yeah, but it's not really redundant data. If it weren't the same, it would suggest that it's part of another board.

 

I'm guessing there's no way to do this all in one query...

[rhodesa]

as i said before...you can't do it in one query. either stick with the update or change the rest of your code to handle a NULL

[haku]

Change the rest of your code to handle NULL. That's the more intuitive way to do it. If it wasn't working before, then you had some errors in your logic, and should fix those, rather than trying to duplicate columns in the DB.

[jxrd]

Yeah....well how would I stop people from say...viewing all threads in one thread going to thread 0. Obviously thread 0 doesn't exist, but because all threads have a thread of 0, they will all be fetched from the database.

 

I could put if $id > 0....but that just seems random and illogical.

[haku]

Why do think think that is random and illogical?