MySQL… LAST_INSERT_ID()

@bokehMar 24.2006

Is it possible to use LAST_INSERT_ID() in this context or am I forced to do this in two steps. The query does not fail but sent_message.message_id is empty. The other fields are updated.

[CODE]INSERT INTO sent_messages( user_id, message_id, ts) VALUES ( 1, LAST_INSERT_ID(” INSERT INTO messages ( sender, message, ts ) VALUES ( ‘$user’, ‘$message’, UNIX_TIMESTAMP() ) “), UNIX_TIMESTAMP() )[/CODE]

to post a comment

PHP

14 Comments(s) _↴

@chazzyMar 24.2006 — #I'm thinking 2 different queries.

@bokehauthorMar 24.2006 — #Me too. When I wrote that I was thinking PHP where the arguments are run first and then are available to the function.

@chazzyMar 24.2006 — #it works like that with selects only.

if you want you can run these queries (gotta check syntax) in succession if you want

<i>
 </i>INSERT INTO messages (sender, message, ts) VALUES('$user', '$message', UNIX_TIMESTAMP());
 SELECT @last_ins := LAST_INSERT_ID();
 INSERT INTO sent_messages( user_id, message_id, ts) VALUES (1,@last_ins,UNIX_TIMESTAMP());

Also, instead of using UNIX_TIMESTAMP() all the time, you can setup a "trigger" that automatically inserts the current time on insert.

@bokehauthorMar 24.2006 — #Ok, So now I have to queries to run sequencially which does actuall work using the following two queries, but, it would have been nice to do it in one hit.

[CODE]INSERT INTO messages( 
 sender, message, ts 
 )
 VALUES 
 (
 '$user', '$message', UNIX_TIMESTAMP()
 )
 
 
 INSERT INTO 
 sent_messages ( 
 user_id, message_id, ts
 )
 VALUES
 (
 1, LAST_INSERT_ID(), UNIX_TIMESTAMP()
 )[/CODE]

Also, instead of using UNIX_TIMESTAMP() all the time, you can setup a "trigger" that automatically inserts the current time on insert.[/QUOTE]Can you expand on this... And your use of the SELECT in the above query.

@chazzyMar 24.2006 — #mysql can use variables. Similar to how PHP has $var_name, mysql has @var_name. you define them in selects and can use them in inserts/updates/deletes/selects or really any sql query (show databases like '%@dbname%') for example.

for some reason, i think that some versions of mysql won't let you just insert straight through like that. but as long as it works, keep it.

what version of mysql do you use? you can use UNIX_TIMESTAMP() as the default value and skip it, or setup a trigger for on update insert the new date. but that is in version >=5.0. either way you can still have the default as well.

@bokehauthorMar 24.2006 — #My MySQL book is a bit basic; it doesn't mention variables anywhere.what version of mysql do you use? you can use UNIX_TIMESTAMP() as the default value and skip it, or setup a trigger for on update insert the new date. but that is in version >=5.0. either way you can still have the default as well.[/QUOTE]4.1.12a on the server box and 5.0.16 on this one. The INSERT query works on both.

@bokehauthorMar 24.2006 — #you can use UNIX_TIMESTAMP() as the default value[/QUOTE]How would I set that up?

@chazzyMar 24.2006 — #if you pick up any of MySQL AB's books they won't, but I figured that.

why aren't you running the same version throughout? can lead to issues but that's fine.

i'm not sure what books you'll find to be honest, you're really in spain?

any book specific to a developer should mention it though (about the variables). they're very useful if you want to do something like this:

<i>
 </i>SELECT @myvar := unique_val FROM table WHERE something...;
 SELECT column_list FROM table2 WHERE a_column='@myvar';
 SELECT column_list FROM table3 WHERE a_column!='@myvar';

I might be off on the single quotes though. basically if you want to get a value and keep on reusing it for a bunch of selects or updates or deletes.

@bokehauthorMar 24.2006 — #you're really in spain?[/QUOTE]Yes I am. I can see the Mediterranean Sea from home and the weather is gorgeous. 300+ sunny days a year and warm. Today it was 80 degrees (27C) and in the summer its 100 degrees every day. Even the coldest days of winter at night it doesn't get below 50 degrees.

I don't have any trouble getting books, I just order them from Amazon.

The reason for the mismatch in versions is because I bought this machine at christmas due to a failure and just loaded the latest version onto it.

How would I set up the timestamp as a default value?

@chazzyMar 24.2006 — #well here's the thing - you can do it and it'll work in 5.0, but when you go back to your server @ 4.1 it won't work right, or at all, because it doesn't support triggers until 5.

so nevermind that i mentioned it.

that's probably a beautiful place to be. i would love it if it were about 15 degrees F cooler though.

@bokehauthorMar 24.2006 — #i would love it if it were about 15 degrees F cooler though.[/QUOTE]I live about 5 kilometers inland (3 miles) and just over 200 metres altitude and here the relative humidity is pretty low (25-40% most days) so the heat is very bearable but at the coast relative humidity can be over 80% and the sea doesn't cool you down much becuase the water is nearly 80 degrees in the summer.

@chazzyMar 24.2006 — #i need near 0% humidity. my sinuses react to very minimal changes.

@bokehauthorMar 24.2006 — #0% humidity[/QUOTE]Blimey. Where do you live? On the moon?

@chazzyMar 24.2006 — #ok so maybe i exaggerateda bit. i do feel humidity quite strong though.

Also in #PHP _↴

directory listing How to use a weather php restict page access

Success!

Help @bokeh spread the word by sharing this article on Twitter...

Tweet This

MySQL… LAST_INSERT_ID()

14 Comments(s) _↴

Also in #PHP _↴

Success!

Social

Version

MySQL… LAST_INSERT_ID()

14 Comments(s) ↴

Also in #PHP ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

14 Comments(s) _↴

Also in #PHP _↴