Support » Plugin: Internal Link Juicer: SEO Auto Linker for WordPress » Querying for Pages that Link to This Post

  • Resolved virtuosicmedia

    (@virtuosicmedia)


    Hi, I’m looking to write a query that would list all posts that link to a specific post. There’s no documentation like that on your website, currently. I’m wondering if you can point me in the right direction. If possible, I’d like to grab the $post->id of referencing posts.

    In other words, I’m on Post 21, which has a keyword of “culture clash”, and in my page template for Post 21, I want to display a list of all the posts that link to the keyword “culture clash.”

    Is there a way to do this?

    Thank you!

Viewing 9 replies - 1 through 9 (of 9 total)
  • Plugin Author Internal Links

    (@internallinkjuicer)

    Hi @virtuosicmedia

    This is possible. Currently, we have not (yet) documented this, as it is of more interest to developers and less to users.

    Method one, to get all links that pointing out from the current post:

    $current_post_id = 21;
    $outgoing_links = ILJ\Database\Linkindex::getRules($current_post_id, 'post');
    
    /*
    $outgoing_links = array(
      [
        'link_from' => 21,
        'link_to'   => [id of linked post],
        'anchor'    => [anchortext],
        'type_from' => 'post',
        'type_to'   => 'post'
      ],[...],[...],...
    )
    */

    For retrieving all posts that link to another post through keyword “culture clash” you have to write your own DB query, which would look like this:

    $anchor = 'culture clash';
    global $wpdb;
    $query = $wpdb->prepare("SELECT link_to FROM " . $wpdb->prefix . ILJ\Database\Linkindex::ILJ_DATABASE_TABLE_LINKINDEX . " linkindex WHERE linkindex.type_to = 'post' AND linkindex.anchor = %s", $anchor);
    $results = $wpdb->get_results($query);

    This will get you a list of post ids, that have an incoming link through your defined anchor text.

    WordPress.org Forums <noreply@wordpress.org>
    5:43 AM (2 hours ago)
    to noreply

    Internal Links wrote:

    Hi @virtuosicmedia

    This is possible. Currently, we have not (yet) documented this, as it is of more interest to developers and less to users.

    Method one, to get all links that pointing out from the current post:

    $current_post_id = 21;
    $outgoing_links = ILJ\Database\Linkindex::getRules($current_post_id, 'post');
    
    /*
    $outgoing_links = array(
      [
        'link_from' => 21,
        'link_to'   => [id of linked post],
        'anchor'    => [anchortext],
        'type_from' => 'post',
        'type_to'   => 'post'
      ],[...],[...],...
    )
    */

    For retrieving all posts that link to another post through keyword “culture clash” you have to write your own DB query, which would look like this:

    $anchor = 'culture clash';
    global $wpdb;
    $query = $wpdb->prepare("SELECT link_to FROM " . $wpdb->prefix . ILJ\Database\Linkindex::ILJ_DATABASE_TABLE_LINKINDEX . " linkindex WHERE linkindex.type_to = 'post' AND linkindex.anchor = %s", $anchor);
    $results = $wpdb->get_results($query);

    This will get you a list of post ids, that have an incoming link through your defined anchor text.

    Thank you for your help. My example was more for clarification than what I’m solving for, however. I’m trying to make this solution work for multiple posts, so will need to retrieve the page keywords dynamically.

    How would I retrieve the keyword of the current page? And if there are multiple keywords, how would that change the SQL query in your second method?

    I suppose I could use $outgoing_links['anchor'] from your method one to retrieve an array of anchor keywords, but is there a more direct method of doing so?

    I appreciate your help!

    • This reply was modified 5 months, 1 week ago by virtuosicmedia.
    • This reply was modified 5 months, 1 week ago by virtuosicmedia. Reason: clarifying and formatting
    Plugin Author Internal Links

    (@internallinkjuicer)

    You’re welcome 🙂

    How would I retrieve the keyword of the current page? And if there are multiple keywords, how would that change the SQL query in your second method?

    You could retrieve the keywords for the current page/post by its corresponding post meta:

    
    $keywords = get_post_meta($post->ID, \ILJ\Database\Postmeta::ILJ_META_KEY_LINKDEFINITION, true);
    

    The query in the example gives you back all matches (that have your provided anchor text). Means if there are 5 posts, that link with your queried anchor text, they will all be returned.

    I suppose I could use $outgoing_links[‘anchor’] from your method one to retrieve an array of anchor keywords, but is there a more direct method of doing so?

    Could you specify your use-case? It’s not clear enough for me, what you try to achieve.

    Cheers!

    Could you specify your use-case? It’s not clear enough for me, what you try to achieve.

    Sorry about that. I misunderstood the code in $outgoing_links. It wouldn’t do what I was trying to accomplish.

    I’m trying to build a widget, or shortcode, or perhaps a Gutenberg Block, that will list the posts the link to the current post. I’m not using the plugin for SEO purposes, I’m using to to provide automatic links between pages in a small reference website for a fiction novel series.

    So, I want a block or widget in the sidebar that says:

    Other Pages You Might Be Interested In
    ————————————–
    Post Title
    Post Title
    Post Title
    Post Title
    Post Title
    Post Title
    Post Title

    I think I can accomplish that with what you’ve given me above. But If there is more than one keyword, I’m worried the SQL query you gave me in your first response won’t work. I don’t know much about sequel queries, but I think that the linkindex.anchor = %s in

    "SELECT link_to FROM " . $wpdb->prefix . ILJ\Database\Linkindex::ILJ_DATABASE_TABLE_LINKINDEX . " linkindex WHERE linkindex.type_to = 'post' AND linkindex.anchor = %s", $anchor)

    will need to be linkindex.anchor IN (%s), and maybe some other things will need to change. But I can play around with it until I get stuck again.

    So, I’m trying out your existing SQL query with a hardcoded keyword:

    $anchor = 'King';
    
    global $wpdb;
        
    $query = $wpdb->prepare("SELECT link_to FROM " . $wpdb->prefix . ILJ\Database\Linkindex::ILJ_DATABASE_TABLE_LINKINDEX . " linkindex WHERE linkindex.type_to = 'post' AND linkindex.anchor = %s", $anchor);
        
    $results = $wpdb->get_results($query);

    And when I print_r out $results, I’m getting this:

    Array
    (
        [0] => stdClass Object
            (
                [link_to] => 576
            )
    
        [1] => stdClass Object
            (
                [link_to] => 576
            )
    
        [2] => stdClass Object
            (
                [link_to] => 576
            )
    
        [3] => stdClass Object
            (
                [link_to] => 576
            )
    
        [4] => stdClass Object
            (
                [link_to] => 576
            )
    
        [5] => stdClass Object
            (
                [link_to] => 576
            )
    
        [6] => stdClass Object
            (
                [link_to] => 576
            )
    
        [7] => stdClass Object
            (
                [link_to] => 576
            )
    )

    When I check out wp-admin/admin.php?page=internal_link_juicer I see that there are, indeed, 8 posts that link to the keyword “King.” Unfortunately, the ID that is returned (576) is the ID of the current page. I need the ID of those 8 referring pages, so I can get the title, and the permalink for each of those pages.

    So this is really close to what I’m looking for! Also, it wasn’t working with an array of multiple keywords. I can implode the array of keywords into a comma-separated string, but when i add a second keyword to the post, the existing SQL query returns an empty array. I assume the SQL query is not formatted to accept multiple values for $anchor. I haven’t been able to fix that, but I’m sure I could figure it out in time.

    I really do appreciate your help! If you have any insight to altering the SQL Query so it will a) accept an array, and b) return the ID of each post, instead of the current post ID, I’ll be all squared up!

    Ok, more progress!

    I took a guess, and substituted "SELECT link_to FROM " with "SELECT link_from FROM " and now it’s giving me the referencing posts’ IDs in an array, like I want. So I just need to figure out how to write the SQL query to accept a comma-separated list of keywords, and I can move on to using the array of IDs in a get_posts() or WP_Query loop.

    Plugin Author Internal Links

    (@internallinkjuicer)

    I took a guess, and substituted “SELECT link_to FROM ” with “SELECT link_from FROM ” and now it’s giving me the referencing posts’ IDs in an array, like I want. So I just need to figure out how to write the SQL query to accept a comma-separated list of keywords, and I can move on to using the array of IDs in a get_posts() or WP_Query loop.

    Hi @virtuosicmedia

    I’m glad you’ve got it figured out by now. As you noted, “link_to” gives you the link target post ID, while “link_from” gives you the linking post ID.

    To have them all in an array of IDs, simply iterate over the database results:

    $result_ids = [];
    
    foreach($results as $result) {
      $result_ids[] = $result->link_to;
    }

    Have fun and please give us your appreciated rating for our plugin if you like the support and the plugin itself!

    Thanks again for your help. For reference if anyone else comes to this page, here was my final solution:

    /**
     * Retrieve the keywords of the current page
     */
    
    function aw_retrieve_keywords( $post_id ) {
        $keywords = get_post_meta( $post_id, \ILJ\Database\Postmeta::ILJ_META_KEY_LINKDEFINITION, true );
        return $keywords;
    }
    
    /**
     * Use this function in templates with get_posts() or WP_Query
     */
    
    function aw_get_referencing_ids( $post_id, $posttype = '%' ) {
    
        $array = array_map( 'esc_sql', (array) aw_retrieve_keywords( $post_id ) );
        $anchor = '"' . implode('", "', $array ) . '"';
    
        global $wpdb;
        
        $query = $wpdb->prepare("SELECT link_from FROM " . $wpdb->prefix . ILJ\Database\Linkindex::ILJ_DATABASE_TABLE_LINKINDEX . " linkindex WHERE linkindex.type_to LIKE '" . $posttype . "' AND linkindex.anchor IN (" . $anchor . ")");
        
        $results = $wpdb->get_results($query);
    
        $count = count($results);
        $i = 0;
        $clean_results = array();
    
        while ( $i < $count ) {
            array_push( $clean_results, $results[$i]->link_from );
            $i++;
        }
    
        return $clean_results;
    }

    Then you can use that function inside a template file to get an array of IDs for referencing posts or pages. By default, the function will query for all post types, but you can pass in a single post type if you want to limit it.

    From there, you can use get_posts, or WP_Query to do pretty much anything with the IDs. Print titles, excerpts, featured images, etc.

    Thank you for the help!

    Plugin Author Internal Links

    (@internallinkjuicer)

    Thanks for posting the complete result and your rating!

    Have fun with the Internal Link Juicer and always come back if you have any questions or ideas to improve 🙂

Viewing 9 replies - 1 through 9 (of 9 total)
  • You must be logged in to reply to this topic.