Performing a join on an insert

looking up the userid from a different table based on the username

[salnajjar]

The short description:
I'm trying to write an insert statement that replaces a php $username variable with the user id looked up from another different table to the one the insert is being performed on.

The long description:
I'm still learning mysql joins, and although I can use them for selects, I've never used one on an insert statement before and it's causing me a lot of pain so I thought I would throw it out to the guru's for some direction.

I'm trying to write a pseudo e-mail system to allow the users on my website to send messages to each other. The trouble is that I want to let the users type in the username of the user they want to send the message to instead of the numeric unique userid which all my tables use instead of the username for their cross references.

The two tables in question are: users and messages

The relevant fields in the users table look like: users.userid(int10), users.username(varchar64)

The relevant fields in the messages table look like:
message.from(int10), messages.to(int10), messages.datetime(datetime), messages.subject(varchar255), messages.message(mediumtext)

So, the php script tries to perform an insert into the mySQL messages table whilst trying to let mySQL perform the join between the users.username field and the message.to field.

The query I'm working on looks like:
INSERT INTO messages (messages.from, messages.to, messages.datetime, messages.subject, messages.message) VALUES ('$userid', users.userid, NOW, 'test', 'message') WHERE users.username = '$userto'

The $userid variable is a numeric integer pulled from a session variable.

The $userto variable is a text string pulled from a form submit.

Thanks for any advice or suggestions, I've been a long time lurker on webmasterworld and have now stepped into the world of asking questions, so please go gentle ;o)

Seri

[coopster]

You don't join, you use a subselect:

INSERT INTO messages 
 ( 
  messages.from, 
  messages.to, 
  messages.datetime, 
  messages.subject, 
  messages.message 
 ) 
 VALUES 
 ( 
  $userid, 
  (SELECT users.userid FROM users WHERE users.username = '$userto') 
  NOW(), 
  'test', 
  'message' 
 )

[salnajjar]

Thanks for the pointer coopster, I was hoping there was a way to avoid additional mySQL queries as users may send messages to multiple users and I gather this way will involve an additional mysql lookup per recipient.

That being said, I'm being very frugal on my mySQL queries as it is, so I'm sure the DB server can stand a few additional queries whenever a user sends a message.

Thanks ever so much for the response and I'll be trying out the code tomorrow after the wine has worn off.

Thanks again

Seri

[syber]

This should work as well:

INSERT INTO messages (messages.from, messages.to, messages.datetime, messages.subject, messages.message)
SELECT '$userid', users.userid, NOW, 'test', 'message'
FROM users JOIN messages ON users.username = messages.messages.to
WHERE users.username = '$userto'

[carguy84]

Thanks for the pointer coopster, I was hoping there was a way to avoid additional mySQL queries as users may send messages to multiple users and I gather this way will involve an additional mysql lookup per recipient.

That SELECT statement in the insert won't make any difference to the performance of the INSERT. INSERTS are inherently "slow", so you won't even notice the SELECT.