double MySQL query in one query in PHP wordpress

Question

I am using a wordpress website and a custom PHP srcipt inside, which will run with CRON to update posts.

How I see it:

I want make a query to the posts table in the database and get all the posts which are published.
I need to make one more query to the table - postmeta - to get the value from my custom field (there is a link I need to parse)

How I do it:

$pages = $wpdb->get_results( 
"
SELECT post_title, id 
FROM $wpdb->posts
WHERE post_status = 'publish' 
AND post_type = 'post'
"
);

if( $pages ) {
foreach ( $pages as $page ) {
    echo $page->post_title . " - ";
    echo $page->id . "<br>";
}
}

So the question is: The problem is with the MySQL query. I need this response: array[0] -> ID (from posts), post_title (from posts), meta_value (from postmeta where meta_key = 'src_link'). How I can get this response?

I tried this - but id doesnot work:

SELECT post_title, id, meta_value
FROM $wpdb->posts as post
INNER JOIN $wpdb->postmeta as meta ON post.id=meta.post_id
WHERE post.post_status = 'publish' 
AND post.post_type = 'post'
AND meta.meta_key='src_link'

The problem is when I add this line -

AND meta.meta_key='src_link'

It don't find anything. If I delete this line. It finds all I need but with dupclicates ( I need only rows where Meta_key = 'src_link'.

The tables:

posts:

-------------------
id | post_title
------------------
1  | new title here
------------------
2  | again a title here

postmeta:

meta_id | post_id | meta_key | meta_value
---------------------------------------------
1       | 2       | src_link | here_is_my_link
---------------------------------------------
2       | 1       | empty    | not_my_link

please add schema and sample data of both the tables.

Sahith Vibudhi
– Sahith Vibudhi

2018-11-19 09:11:38 +00:00
Commented Nov 19, 2018 at 9:11 — Sahith Vibudhi
– Sahith Vibudhi, Commented Nov 19, 2018 at 9:11
added the sample data

mrdeath4
– mrdeath4

2018-11-19 10:17:15 +00:00
Commented Nov 19, 2018 at 10:17 — mrdeath4
– mrdeath4, Commented Nov 19, 2018 at 10:17

Sadikhasan · Accepted Answer · 2018-11-19 10:46:51Z

1

You can use INNER JOIN like

SELECT post_title, id, meta_key 
FROM $wpdb->posts as post
INNER JOIN $wpdb->postmeta as meta ON post.id=meta.post_id
WHERE post_status = 'publish' and meta.meta_key='src_link'
AND post_type = 'post'

edited Nov 19, 2018 at 10:46

answered Nov 19, 2018 at 8:52

Sadikhasan

18.6k23 gold badges84 silver badges126 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

mrdeath4 Over a year ago

thank you for fast anwering but this code - added 5 duplicates and didnot found the src-link

Sadikhasan Over a year ago

Please update question with your sample data so I can help you as per your expected output.

Sadikhasan Over a year ago

Try my updated answer do not forgot to take field post_title, id, meta_key

mrdeath4 Over a year ago

I did in my answer

indextwo · Accepted Answer · 2018-11-19 11:35:56Z

0

As you're using the global $wpdb we can assume your PHP is within the WP framework. As such, you shouldn't need a custom SQL query - you can do this with a standard WP_Query:

$args = array(
    'post_type'         => 'post',
    'post_status'       => 'publish',
    'posts_per_page'    => -1,

    'meta_query'        => array(
        array(
            'key'     => 'src_link',
            'compare' => 'EXISTS',
        ),
    ),
);

$postsQuery = get_posts($args);

foreach ($postsQuery as $myPost) {

    //  We're using $myPost rather than $post as the latter is a global var used in The Loop

    echo '<pre>' . print_r($myPost, true) . '</pre>';

    update_post_meta($myPost->ID, 'updated_link', $myCustomValue);
}

Here we're fetching all post-type posts with a status of publish and using a meta_query to find posts where src_link exists.

answered Nov 19, 2018 at 11:35

indextwo

5,9556 gold badges52 silver badges68 bronze badges

2 Comments

mrdeath4 Over a year ago

THank you but I copypasted this code and it doesnot fins any posts

indextwo Over a year ago

@mrdeath4 That code definitely works - I tested it with a couple of different keys on a handful of installations and it returns posts in all cases. As such, I would assume that most likely the meta_key you're searching for doesn't exist. Check your database values and make sure that the things you're searching for definitely exist (any post-type posts with the meta_key of src_link).

Uchqun Tursunbayev · Accepted Answer · 2018-11-19 11:37:24Z

0

Try this:

SELECT post_title, id, meta_key 
FROM $wpdb->posts as post
INNER JOIN $wpdb->postmeta as meta ON post.id=meta.post_id and meta.meta_key='src_link'
WHERE post_status = 'publish'
AND post_type = 'post'

answered Nov 19, 2018 at 11:37

Uchqun Tursunbayev

527 bronze badges

1 Comment

mrdeath4 Over a year ago

it finds nothing too ;(

Collectives™ on Stack Overflow

double MySQL query in one query in PHP wordpress

3 Answers 3

4 Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related