Querying for Pages that Link to This Post

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.

www.remarpro.com Forums <[email protected]>
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 4 years, 9 months ago by virtuosicmedia.
• This reply was modified 4 years, 9 months ago by virtuosicmedia. Reason: clarifying and formatting

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!

• This reply was modified 4 years, 9 months ago by Internal Links.

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.

• This reply was modified 4 years, 9 months ago by virtuosicmedia.

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.

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!

• This reply was modified 4 years, 9 months ago by Internal Links.
• This reply was modified 4 years, 9 months ago by Internal Links.
• This reply was modified 4 years, 9 months ago by Internal Links.

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!

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 ??