Function that creates a toplist from comment submit in Wordpress

[pahunrepublic]

I want to display next to the post content values that are submitted through a custom comment field. The function that I'm trying to make is

creates a top list where the top 5 are the most submitted words appear. I want to make that post specific. For each individual single post must have a top 5 words list. Now it makes a top list from all the submitted words at all the posts.

Here is what I did so far:

<?php 
add_action('show_etiqueta','et_get_post_user_etiqueta');
function et_get_post_user_etiqueta( $post_id ){
    $approved_comments = et_get_approved_comments( $post_id );
    global $wpdb;
    $result = $wpdb->get_results($wpdb->prepare("SELECT meta_value, count(*) AS countof FROM $wpdb->commentmeta WHERE meta_key='et_comment_etiqueta' GROUP BY meta_value ORDER BY countof DESC LIMIT 1", $post_id));
echo "<table>";
//foreach($result as $key => $value)
foreach ( $result as $post_id => $value)
{
echo "	<tr>

		<td>" . $value->meta_value . "</td>
		<td>" . $value->countof . "</td>
	</tr>
";
}
echo "</table>";
update_post_meta($post_id,'_et_inreview_comments_etiqueta',$result);
return $result;
}?>

'etiqueta' means label in English

The tables that are affected are:

-wp_commentmeta: meta_id; comment_id; meta_key; meta_value columns

-wp_postmeta: meta_id; post_id; meta_key; meta_value

 

Can anyone help me with this?

[erm410]

Try the following prepared statement:

 

$wpdb->prepare('SELECT cm.meta_value, count(*) AS countof FROM $wpdb->commentmeta cm, $wpdb->comments c WHERE cm.comment_id=c.comment_ID AND c.comment_post_id=%d AND cm.meta_key='et_comment_etiqueta' GROUP BY meta_value ORDER BY countof DESC LIMIT 5", $post_id)

 

Explanation: The commentmeta table does not have the post ID in it but the comments table does (in the column comment_post_id), so in order to use the post ID you need to JOIN the two tables. To cut down on typing I have aliased them as cm and c. The %d in the where clauses is a placeholder for $post_id. Together the WHERE clauses should return results with the correct meta_key and the current post. LIMIT 1 would only give the top result, LIMIT 5 is probably what you want instead. Then print out the table:

 

echo "<table>";
foreach ( $result as $value)
{
echo "	<tr>

		<td>" . $value->meta_value . "</td>
		<td>" . $value->countof . "</td>
	</tr>
";
}
echo "</table>";

 

There should be no need to update the post meta data, nor return any value from the function.

[pahunrepublic]

I tried with this solution but no output. It doesn't display the value, than I tested in phpmyadmin to see if the SQL query gives a result:

SELECT cm.meta_value, count(*) AS countof FROM wp_commentmeta cm, wp_comments c WHERE cm.comment_id=c.comment_ID AND c.comment_post_id=45 AND cm.meta_key='et_comment_etiqueta' GROUP BY meta_value ORDER BY countof DESC LIMIT 5

the query is good but only if I set the comment_post_ID: c.comment_post_id=45

as you can see.

Any idea how to make it display for each single post the corresponding value?

[erm410]

The %d in the prepared statement should be taking care of getting the value from each single post. It may be useful to see what is going on behind the scenes. Try running:

 

<?php 
add_action('show_etiqueta','et_get_post_user_etiqueta');
function et_get_post_user_etiqueta( $post_id ){
    global $wpdb;
    $query = $wpdb->prepare("SELECT cm.meta_value, count(*) AS countof FROM $wpdb->commentmeta cm, $wpdb->comments c WHERE cm.comment_id=c.comment_ID AND c.comment_post_id=%d AND cm.meta_key='et_comment_etiqueta' GROUP BY meta_value ORDER BY countof DESC LIMIT 5", $post_id);
    echo '<p>The post ID is: '.$post_id.' and the sql query is: '.$query.'</p>';
    $result = $wpdb->get_results($query);
    echo '<p>The query result is:</p>';
    echo nl2br(print_r($result, TRUE));
}?>

 

Then post the output so we can try to figure out why the query is working when put in directly but not when you run it from the PHP.

[pahunrepublic]

it gives me this:

The post ID is: and the sql query is: SELECT cm.meta_value, count(*) AS countof FROM wp_commentmeta cm, wp_comments c WHERE cm.comment_id=c.comment_ID AND c.comment_post_id=0 AND cm.meta_key='et_comment_etiqueta' GROUP BY meta_value ORDER BY countof DESC LIMIT 5

 

The query result is:

Array

(

)

[pahunrepublic]

Let me share the files that modify everything. They're located in a theme folder.

 

[attachment deleted by admin]

[pahunrepublic]

The solution was that we needed a global variable inside the function that represents a post from the Wordpress database:

global $post;
    global $wpdb;

 

Finally the function looks like this

<?php
add_action('show_etiqueta','et_get_post_user_etiqueta', 10, 0);
function et_get_post_user_etiqueta(){
    global $post;
    global $wpdb;
    $post_id = $post->ID;
    $result = $wpdb->get_results($wpdb->prepare("SELECT cm.meta_value, count(*) AS countof FROM $wpdb->commentmeta cm, $wpdb->comments c WHERE cm.comment_id=c.comment_ID AND c.comment_post_id = %d AND cm.meta_key='et_comment_etiqueta' GROUP BY meta_value ORDER BY countof DESC LIMIT 1", $post_id));
echo "<table>";
foreach($result as $key => $value)
//foreach ( $result as $value)
{
echo "	<tr>

		<td>" . $value->meta_value . "</td>
		<td>" . $value->countof . "</td>
	</tr>
";
}
echo "</table>";
$result = $value->meta_value;
if ( !get_post_meta($post_id,'_et_inreview_comments_etiqueta',true) ) update_post_meta($post_id,'_et_inreview_comments_etiqueta',$result);
return $result;
    
}?>

Thank you erm410 for your help