444 posts
  • Bought between 1 and 9 items
  • Exclusive Author
  • Has been a member for 4-5 years
  • United Kingdom
TahaH-Studio says

hey guys.

I’m trying to write a function in wordpress which basically counts posts number by a certain author in a certain category. After digging into wordpress’s database I found that the way to approach that using MySQL queries to select some bit from tables to and use it to select bit from other tables. The following code is the code I wrote for this function. The problem that in line 16, I tried storing series of IDs that was imported from another table in one variable. I was expecting it to function the same way that it would when I echo it. But it didn’t, It only gave me the last ID that was imported from the other table. which then result in counting one post only by each author.

Here is the code below and excuse me, my english is bad and im still a beginner in php and mysql if you found that was done in a stupid way :D

function get_post_count() {
    global $wpdb;

    $term_id = $wpdb->get_results("SELECT term_id FROM {$wpdb->prefix}terms WHERE slug='myCatSlug'");
    foreach($term_id as $tr_id)
    $tri = $tr_id->term_id;

    $tax_id = $wpdb->get_results("SELECT term_taxonomy_id FROM {$wpdb->prefix}term_taxonomy WHERE term_id=$tri");
    foreach($tax_id as $tx_id)
    $txi = $tx_id->term_taxonomy_id;

    $obj_id = $wpdb->get_results("SELECT object_id, term_taxonomy_id FROM {$wpdb->prefix}term_relationships WHERE term_taxonomy_id=$txi ORDER BY object_id ASC");
    foreach($obj_id as $o)

    // The whole problem is here
    $obj_i = "'".$o->object_id."',";

    $query = "SELECT COUNT(id), post_type, post_status, post_author FROM {$wpdb->prefix}posts WHERE post_type='post' AND post_status='publish' AND id IN($obj_i) GROUP BY post_author"; 

    $result = mysql_query($query) or die(mysql_error());

    while($row = mysql_fetch_array($result)){
        echo "<br />";
        echo "There are ". $row['COUNT(id)'] ." Posts By ". $row['post_author'];
    }
}
444 posts
  • Bought between 1 and 9 items
  • Exclusive Author
  • Has been a member for 4-5 years
  • United Kingdom
TahaH-Studio says

Hey guys, forget about the first problem. My problem now is in how to remove the last comma in a string like this '23','45','63','21','31','12', generated from a variable like that

$var = "'" . $v . "'"
I tried substr_replace($var,"",-1); But it removes the last comma from each bit and i end up with something like '23''45''63''21''31''12' completely without no commas and I want it to be like this '23','45','63','21','31','12' with the last comma ONLY removed

Thanks in advance

374 posts
  • Bought between 10 and 49 items
  • Contributed a Blog Post
  • Contributed a Tutorial to a Tuts+ Site
  • Exclusive Author
  • Has been a member for 4-5 years
  • Referred between 1 and 9 users
  • Sold between 1 000 and 5 000 dollars
_rohan says
Hey guys, forget about the first problem. My problem now is in how to remove the last comma in a string like this '23','45','63','21','31','12', generated from a variable like that
$var = "'" . $v . "'"
I tried substr_replace($var,"",-1); But it removes the last comma from each bit and i end up with something like '23''45''63''21''31''12' completely without no commas and I want it to be like this '23','45','63','21','31','12' with the last comma ONLY removed Thanks in advance

Lets say you have a variable $var.

if($var != ""):
    $var=substr($var, 0, -1); 
endif;
252 posts
  • Elite Author
  • Won a Competition
  • Has been a member for 4-5 years
  • Sold between 100 000 and 250 000 dollars
  • Exclusive Author
  • Bought between 10 and 49 items
  • Referred between 50 and 99 users
der says

You can also do this:

// Your String
$string = "1,2,3,4,5,";

// Remove the last character
$string[strlen($string)-1] = '';

// Returns 1,2,3,4,5
echo $string;

Also, let’s say you have a string with lots of commas after it, then you can use the following (bullet proof) code to do it:

// Your String
$string = "1,2,3,4,5,,,,,,,,,,,,,,";

// Replace with Regular Expression
$string = preg_replace('/(,+$)/', '', $string);

// Returns 1,2,3,4,5
echo $string;
5 posts
  • Bought between 1 and 9 items
  • Exclusive Author
  • Has been a member for 4-5 years
  • Indonesia
dierka says
Hey guys, forget about the first problem. My problem now is in how to remove the last comma in a string like this '23','45','63','21','31','12', generated from a variable like that
$var = "'" . $v . "'"
I tried substr_replace($var,"",-1); But it removes the last comma from each bit and i end up with something like '23''45''63''21''31''12' completely without no commas and I want it to be like this '23','45','63','21','31','12' with the last comma ONLY removed Thanks in advance
if you still use $obj_i as variable like the previous problem, then simply use this to remove the comma from the end of the list IDs generated:
$obj_i = rtrim($obj_i,",");
Hope it help. :)
444 posts
  • Bought between 1 and 9 items
  • Exclusive Author
  • Has been a member for 4-5 years
  • United Kingdom
TahaH-Studio says

Sorry guys, probably I couldn’t explain my problem properly. Any way I revised the code I wrote from yesterday and shortened it alot but the problems weren’t fixed. Below you will find the new code with the problems commented :D. Please guys can any one help me :(. Seriously Its driving mad. Here is the code.

BTW don’t get scared of the code. Its mostly comments. and I’m sure I made it alot more clearer and makes sense than the previous one. :D

function get_post_count() {
    global $wpdb;

    // gets the term id of the category by its slug
    $term_id = $wpdb->get_var($wpdb->prepare("SELECT term_id FROM $wpdb->terms WHERE slug='categorySlug'"));

    // gets the term taxonomy id using the term id from the last query
    $term_taxonomy_id = $wpdb->get_var($wpdb->prepare("SELECT term_taxonomy_id FROM $wpdb->term_taxonomy WHERE term_id='$term_id'"));

    /* 
    PROBLEM IS HERE
    The query below uses the term taxonomy id from the pravious query
    and gets the post ids matching with the term taxonomy id which baiscly means
    that it gets the post id that in category with slug 'categorySlug'

    The first problem is that it return one value only when I store $o->object_id in a variable
    but when I echo it It returns all matching post IDs.
    */
    $object_id = $wpdb->get_results($wpdb->prepare("SELECT object_id, term_taxonomy_id FROM $wpdb->term_relationships WHERE term_taxonomy_id=$term_taxonomy_id"));

    foreach($object_id as $o) {
        $o->object_id;
    }

    /*
    SECOND PROBLEM
    some how, I need to return the post IDs from the last query in the form of '1','2','3','4'
    where the numbers are the IDs of 4 different posts and notice that I need to get rid if the last
    comma so I can store them in a variable and insert them into the IN() statement without any errors.
    HOW DO I DO THIS?

    The query bellow works with no erros as i tested it using the IDs inserted in the IN statement below.
    Post IDs 30, 32, 33 and 34 belongs to user ID 1,
    and post ID 32 belongs to user ID 2.

    Do when I echo this function into the user profiles header in buddypress it counted 4 for user ID 1,
    and counted 1 for user ID 2 which proofs that it works.
    Note: I'm using buddpress and bp_displayed_user_id() function is to get the user ID on his profile
    page with no need to login.
    */
    $post_count = $wpdb->get_var($wpdb->prepare("SELECT COUNT(id), post_type, post_status, post_author FROM $wpdb->posts WHERE post_type='post' AND post_status='publish' AND id IN('30','31','32','33','34') AND post_author=".bp_displayed_user_id().""));

    return $post_count;
}
753 posts
  • Sold between 10 000 and 50 000 dollars
  • Referred between 10 and 49 users
  • Author had a Free File of the Month
  • Exclusive Author
  • Has been a member for 5-6 years
  • Bought between 1 and 9 items
  • Repeatedly Helped protect Envato Marketplaces against copyright violations
AdamGold says
Of course, because in each loop you reset the value of the variable and then defines it again. You have 2 options: 1. Store it in an array
    foreach($object_id as $o) {
        $array[ $o->object_id ] = $o->object_id;
    }
2. make a joining variable
    foreach($object_id as $o) {
        $variable .= $o->object_id . ',';
    }
444 posts
  • Bought between 1 and 9 items
  • Exclusive Author
  • Has been a member for 4-5 years
  • United Kingdom
TahaH-Studio says
Of course, because in each loop you reset the value of the variable and then defines it again. You have 2 options: 1. Store it in an array
    foreach($object_id as $o) {
        $array[ $o->object_id ] = $o->object_id;
    }
2. make a joining variable
    foreach($object_id as $o) {
        $variable .= $o->object_id . ',';
    }

I dont quite understand what you mean.. Like what I meant to do now :D

753 posts
  • Sold between 10 000 and 50 000 dollars
  • Referred between 10 and 49 users
  • Author had a Free File of the Month
  • Exclusive Author
  • Has been a member for 5-6 years
  • Bought between 1 and 9 items
  • Repeatedly Helped protect Envato Marketplaces against copyright violations
AdamGold says
Of course, because in each loop you reset the value of the variable and then defines it again. You have 2 options: 1. Store it in an array
    foreach($object_id as $o) {
        $array[ $o->object_id ] = $o->object_id;
    }
2. make a joining variable
    foreach($object_id as $o) {
        $variable .= $o->object_id . ',';
    }
I dont quite understand what you mean.. Like what I meant to do now :D

I suggest you to do my 1st idea, but as I see in your first post, you’re trying to get all of the result into one variable with a comma between each one, right? So just copy my 2nd code and you got it.

444 posts
  • Bought between 1 and 9 items
  • Exclusive Author
  • Has been a member for 4-5 years
  • United Kingdom
TahaH-Studio says
Of course, because in each loop you reset the value of the variable and then defines it again. You have 2 options: 1. Store it in an array
    foreach($object_id as $o) {
        $array[ $o->object_id ] = $o->object_id;
    }
2. make a joining variable
    foreach($object_id as $o) {
        $variable .= $o->object_id . ',';
    }
I dont quite understand what you mean.. Like what I meant to do now :D
I suggest you to do my 1st idea, but as I see in your first post, you’re trying to get all of the result into one variable with a comma between each one, right? So just copy my 2nd code and you got it.

I GIVE UP .

by
by
by
by
by
by