Tag Archives: Query

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.

1
2
3
4
5
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';
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 (post_id, meta_key and 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_key and meta_value are statically set to _post_layout and 2 respectively.

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.

MySQL query for getting three most recent posts for last 12 months in WordPress

I recently had a need to retrieve the last three posts for the last 12 months that had at least one or more posts in WordPress. Another requirement was to display a “more” link that would redirect the user to the archive of that month, provided there was at least one additional post to show for.

Before we go over how we can do that, here is an excerpt of what the array ultimately will look like:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
Array
(
    [201303] => Array
        (
            [month_year] => March 2013
            [more_posts] => 1
            [posts] => Array
                (
                    [0] => Array
                        (
                            [post_title] => Test Post 1
                        )
 
                    [1] => Array
                        (
                            [post_title] => Test Post 2
                        )
 
                    [2] => Array
                        (
                            [post_title] => Test Post 3
                        )
 
                )
 
        )
 
    [201302] => Array
        (
            [month_year] => February 2013
            [more_posts] => 1
            [posts] => Array
                (
                    [4] => Array
                        (
                            [post_title] => Test Post 4
                        )
 
                    [5] => Array
                        (
                            [post_title] => Test Post 5
                        )
 
                    [6] => Array
                        (
                            [post_title] => Test Post 6
                        )
 
                )
 
        )
    ...
)
Array
(
    [201303] => Array
        (
            [month_year] => March 2013
            [more_posts] => 1
            [posts] => Array
                (
                    [0] => Array
                        (
                            [post_title] => Test Post 1
                        )

                    [1] => Array
                        (
                            [post_title] => Test Post 2
                        )

                    [2] => Array
                        (
                            [post_title] => Test Post 3
                        )

                )

        )

    [201302] => Array
        (
            [month_year] => February 2013
            [more_posts] => 1
            [posts] => Array
                (
                    [4] => Array
                        (
                            [post_title] => Test Post 4
                        )

                    [5] => Array
                        (
                            [post_title] => Test Post 5
                        )

                    [6] => Array
                        (
                            [post_title] => Test Post 6
                        )

                )

        )
    ...
)

It’s an array with 12 elements, if there are, in fact, 12 months with posts, where each element contains the name of the month with a corresponding year, a boolean of whether there are more posts to show for, and another array with three post titles linking to the full post.

Continue reading