How can I select SELECT + INSERT in the same mysql query?

Question

I have a php script that processes keywords from the mysql "keywords" table (columns: id - keyword) and then saves the data to another data table (column: id [foreign key keywords.id] - dataname - datavalue) .

My problem is that when the script is ready to save data, I only have a keyword, not an identifier.

So, is there a way to get the keyword id and save the data in a single mysql query? (I mean, without having to do something like SELECT id from keywords, where keyword = keyword, then run another query for INSERT.

Answer 1

If the selection and change tables are different, you can use a simple subquery:

INSERT INTO `data` (`id`, `dataname`) VALUES
(
    (SELECT `id` FROM `keywords` WHERE `keyword` = 'keyworrrrrd'),
    'blabla'
)

or

INSERT INTO `data` (`id`, `dataname`)
SELECT `id`, 'blabla' FROM `keywords` WHERE `keyword` = 'keyworrrrrd'