Dynamically insert data into MySQL table based on select query result
I was working on a new feature for a site, and while it closely resembles WordPress’ native post formats, it offers more flexibility and control. Users can choose from predefined post layouts and it will display the post using a custom layout and branding.
Since this is a new feature, none of the existing posts have a post layout associated with them, and while there is a default, I actually want my existing posts to be specified.
Here is a MySQL query that performs an
insert based on the results of a
select query. It’s pretty straightforward, but wanted to share it nevertheless.
INSERT INTO wp_postmeta(post_id, meta_key, meta_value) SELECT ID AS post_id, '_post_layout' AS meta_key, 2 AS meta_value FROM wp_posts WHERE post_status = 'publish' AND post_type = 'post';
We basically specify the table (
wp_postmeta) and the columns (
meta_value) we wish to insert data to, then we select that data from another table, where the
post_id, in this case, is dynamic per row, but the
meta_value are statically set to
You can adjust this query to your liking, as long as you end up with the desired columns that correspond to the columns you wish to insert data to.
Featured image by Alvin Engler.
Previously posted in WordPress and transferred to Ghost.
March 3, 2020 at 12:50 pm
This was very helpful, exactly the model I was looking for. Thank you!