Link the retrieved custom field values to its respective posts

Question

Using the code below, I have been able to make a list of custom field values (on a seperate page template) from across all the posts for a specific custom meta key "realname". The code gives me a nice alphabetically ordered list of all the custom values.

<?php
function get_meta_values( $key = '', $type = 'post', $status = 'publish' ) {
    global $wpdb;
    if( empty( $key ) )
        return;
    $r = $wpdb->get_col( $wpdb->prepare( "
        SELECT pm.meta_value FROM {$wpdb->postmeta} pm
        LEFT JOIN {$wpdb->posts} p ON p.ID = pm.post_id
        WHERE pm.meta_key = '%s' 
        AND p.post_status = '%s' 
        AND p.post_type = '%s'
        ORDER BY pm.meta_value ASC
        ", $key, $status, $type ) );

    return $r;
}
?>
<?php $my_list_items = get_meta_values( 'realname' ); ?>
<h3>PEOPLE ALREADY REGISTERED - ALPHABETICAL ORDER :</h3>
<ol>
<?php foreach( $my_list_items as $my_list_item ) { ?>

            <li><?php echo $my_list_item; ?></li>
<?php } ?>
</ol>

Now, Please suggest me how I get the retrieved list items (custom values) to link to their respective posts. I am a noob, please pardon my ignorance. I tried the following but it doesnot work.

<li><a href="<?php the_permalink() ?>"><?php echo $my_list_item; ?></a></li>

Answer 1

You achieve this by modifying your code as following and using get_results function instead of get_col function.

<?php
function get_meta_values( $key = '', $type = 'post', $status = 'publish' ) {
global $wpdb;
if( empty( $key ) )
    return;
$r = $wpdb->get_results( $wpdb->prepare( "
    SELECT pm.meta_value, p.ID FROM {$wpdb->postmeta} pm
    LEFT JOIN {$wpdb->posts} p ON p.ID = pm.post_id
    WHERE pm.meta_key = '%s' 
    AND p.post_status = '%s' 
    AND p.post_type = '%s'
    ORDER BY pm.meta_value ASC
    ", $key, $status, $type ) );

return $r;
}
?>
<?php $my_list_items = get_meta_values( 'realname' ); ?>
<h3>PEOPLE ALREADY REGISTERED - ALPHABETICAL ORDER :</h3>
<ol>
<?php 
if ( $my_list_items )    {
foreach( $my_list_items as $my_list_item ) { ?>

        <li><a href="<?php echo get_permalink( $my_list_item->ID ); ?>"><?php echo $my_list_item->meta_value; ?></a></li>
<?php } 
}?>
</ol>

Answer 2

I think you could do something like this within your foreach loop:

<?php echo get_permalink($my_list_item->ID); ?>

If this doesn't work check the values of $my_list_items by doing print_r($my_list_items) to see how to access the post ID from within that array.

Hope this makes sense/helps!