Ftyjtk

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

How I see it:

1. I want make a query to the posts table in the database and get all the posts which are published.


2. 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

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'

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.

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'