SQL count example help needed

  • Resolved Damien

    (@takster)


    13 years, 11 months ago

    Hi there, I’m trying to learn how to pull info from the database, it’s pretty much the same thing as the SQL COUNT(column_name) Example at w3schools except I’m trying to count the author’s total ‘like_uid’ (likes by user ID) from a table called ‘wp_likes’ and display it on author and post pages.

    http://img175.imageshack.us/img175/6621/captureho.png

    This is what I had that does not work…

    <?php
global $wpdb;
$liked_count = $wpdb->get_var($wpdb->prepare("SELECT COUNT(like_uid) FROM $wpdb->likes WHERE uid = %d", $uid));
echo 'Likes ' . $liked_count . '';
?>

    So it would show ‘likes 5’. I hope I make sense 🙂

Viewing 8 replies - 1 through 8 (of 8 total)
  • Glenn Ansley

    (@blepoxp)

    13 years, 11 months ago

    Hi Tankster,
    Are you sure that you have your column and table names correct?
    I modified your query slightly and it worked:

    <?php
global $wpdb;
$liked_count = $wpdb->get_var($wpdb->prepare("SELECT COUNT(ID) FROM $wpdb->posts WHERE post_status = %s", 'publish'));
echo 'Likes ' . $liked_count . '';
?>

    The only thing I changed was column and table names.
    You could also try putting die( mysql_error() ); after your code to see what happens.
    Hope that helps.

    dralezero

    (@dralezero)

    13 years, 11 months ago

    Edit: post above has better info

    Thread Starter Damien

    (@takster)

    13 years, 11 months ago

    Thanks, I have working

    <?php $liked = $wpdb->get_var($wpdb->prepare( "SELECT COUNT(like_uid) FROM " . $wpdb->prefix .  "likes WHERE uid = %d", $uid) );
echo $liked.""; ?>

    and it shows the rows total number of likes, but as you can see here
    http://img175.imageshack.us/img175/6621/captureho.png

    I have like_uid containing the user ID of the author, so I’m trying to get WHERE uid = %d”, $uid to only show the count total for the article author only, either on single.php or author.php.

    It’s almost like having “John likes 5 other posts” in his article rather then count them all. I cant seem to work it out .:/

    Glenn Ansley

    (@blepoxp)

    13 years, 11 months ago

    What can’t you work out now? Getting the correct ID for the author?

    Thread Starter Damien

    (@takster)

    13 years, 11 months ago

    yes I cant get the ID working.

    Glenn Ansley

    (@blepoxp)

    13 years, 11 months ago

    In single.php and inside the loops $post->post_author will return the author ID.
    If you’re on the author’s archive page, the following should return their ID: get_query_var( 'author' );

    Hope that helps.

    Thread Starter Damien

    (@takster)

    13 years, 11 months ago

    Thanks blepoxp, I’m trying to implement it, without much luck.

    <?php
$liked = $wpdb->get_var($wpdb->prepare("SELECT COUNT(like_uid) FROM " . $wpdb->prefix .  "likes") );
echo $liked."";
?>

    returns ‘9’ or the total count, so trying to narrow it down with something like

    <?php
$liked = $wpdb->get_var($wpdb->prepare("SELECT COUNT(like_uid) FROM " . $wpdb->prefix .  "likes WHERE post_author = %d", $post->post_author) );
echo $liked."";
?>

    I was hoping would restrict it to only counting the ID of that page’s author. Just returning a blank with the above. Will keep trying anyways.

    Thread Starter Damien

    (@takster)

    13 years, 11 months ago

    <?php
$liked = $wpdb->get_var($wpdb->prepare("SELECT COUNT(like_uid) FROM " . $wpdb->prefix .  "likes WHERE like_uid = %d", $post->post_author) );
echo $liked."";
?>

    got it 🙂

Viewing 8 replies - 1 through 8 (of 8 total)
  • The topic ‘SQL count example help needed’ is closed to new replies.