The MU forums have moved to

Any MySQL Guru's Out There? (18 posts)

  1. suleiman
    Posted 15 years ago #

    Simple request, I just want to try and take the code here:

    $post_list = $wpdb->get_results( "SELECT DISTINCT UNIX_TIMESTAMP(timestamp) AS timest, title, blog_id, post_id FROM wp_global_posts WHERE $insert (UNIX_TIMESTAMP(timestamp) <= $tnow ) ORDER BY timestamp DESC LIMIT " . $limit. "");

    And modify it so that it only pulls entries from public blogs and not spam or deleted blogs. I tried doing this:

    $post_list = $wpdb->get_results( "SELECT DISTINCT UNIX_TIMESTAMP(timestamp) AS timest, title, blog_id, post_id FROM wp_global_posts WHERE $insert (UNIX_TIMESTAMP(timestamp) <= $tnow ) AND public='1' AND spam = '0' AND deleted ='0' ORDER BY timestamp DESC LIMIT " . $limit. "");

    But no luck. Any ideas folks?

  2. suleiman
    Posted 15 years ago #

    no takers?

  3. Ovidiu
    Posted 15 years ago #

    I have a similar question, I jsut got my first 5 spam-blogs and their posts do show up on the mainpage in the latest posts section and in my modified dashboard too.

    for both I use IT damagers sitewide feed so I would be very interested how I could set his plugin not to include entried from spam or deleted blogs into the masterfeed.

    btw. I jsut forgot to mentione that I flagged these 5 blogs as spam

  4. andrea_r
    Posted 15 years ago #

    I think you forgot to ad the the SQL that table name where the flag is set. (can't think of it at the moment)

    As written, it's only grabbing results from timest, title, blog_id, post_id fields, none of which have the public or spam flags. See? :)

  5. skcsknathan001
    Posted 15 years ago #

    The conditions should go after the WHERE syntax. You did that right.
    But there is a var $insert. Don't know what's in there. Need to know what's in there so can check the whole SQL syntax is correct or not.

    [I assume the above syntax gave you syntax error]

  6. suleiman
    Posted 15 years ago #

    nathan, this is where in the code the insert variable is defined:

    if ($_GET['c']) {
    $category = $_GET['c'];
    $insert = "category = '$category' AND";

    But I should be quick to point out, my modifications above don't produce a syntax error, they simply don't provide any results on the page at all.

    This code is for a tags page where posts are ordered by category. After my modifications above, the categories show up correctly but none of the posts, even those from blogs marked as public without any deleted or spam markers, fail to show up.

  7. Ovidiu
    Posted 15 years ago #

    I solved my problem with the sitewide feed displaying spam blogs like this:

    function create_map($type) {
    global $wpdb, $wpmuBaseTablePrefix;
    $multiplier = 100; // new setting to dig deep for posts/comments until we workaround wpmu_update_blogs_date messing with timestamp
    $blogs = $wpdb->get_col("SELECT
    blog_id FROM $wpdb->blogs
    WHERE public = '1' AND archived = '0' AND spam = '0' AND deleted= '0' AND last_updated != '0000-00-00 00:00:00'

    all I did was insert this: AND spam = '0' AND deleted= '0' this way if one of these conditions is false it does not get displayed in the feeds...

    @ suleiman

    sorry for hijacking your thread, at first glance it looked related to my problem :-(

  8. skcsknathan001
    Posted 15 years ago #

    make sure the table wp_global_posts actually contains fields public, spam, and deleted.

  9. suleiman
    Posted 15 years ago #

    you know what nathan, it doesn't (and it's suleiman btw ;))

    So I guess I start my search where the entries are being made into the wp_global_posts field in the first place and see if I can't filter out private/spam/deleted blogs thattaway.

    Thanks for the tips! I'll report back soon.

  10. suleiman
    Posted 15 years ago #

    alright, I officially have given up. I can't for the life of me figure this one out. I tracked down the code used to insert posts into the wp_global_posts table, but as far as filtering out posts from private, spam, or deleted blogs, there's nothing doing.

    If anyone else wants to take a stab, the code used in /wp-includes/post.php is as follows:

    if ($post_type == "post" ) {

    $p_id = $wpdb->get_var("SELECT ID FROM $wpdb->posts WHERE post_title = '$post_title' AND post_date = '$post_date'");

    if ($p_id) {

    foreach ($post_category as $key => $value) {
    $playground = get_category($value);
    $accat = $playground->cat_name;
    "INSERT IGNORE INTO wp_global_posts
    (ID, blog_id, title, category, timestamp, post_id)
    ('', $blog_id, '$post_title', '$accat', '$post_date', '$p_id')");
    } //end insert into global_posts

  11. suleiman
    Posted 15 years ago #

    FIXED! I on a roll this week or what!?

    In any event this fix will only resolve the issue of posts coming into the table from private blogs, I have yet to figure out how to also block deleted and spam blogs from having their posts entered.

    Simply put you start by finding this line (around 657) in the your modified /wp-includes/posts.php file:

    if ($post_type == "post" ) {

    and add this line:

    if ($blogs == "public" ) {

    immediately above it. Be sure to also add the closing "}" bracket at the end of the new if statement.

    This has worked great for me, if anyone has ideas for improvement or what additional information would be needed for blocking posts from spam and deleted blogs, then please post it here.

  12. skcsknathan001
    Posted 15 years ago #

    Suleiman, [sorry about the name misspelling]

    you have to look even further beyond the code you pasted above.

    By the time it checks
    if ($post_type == "post" )
    the data is already retrieved. So you have to check even before. Somewhere there will be a SELECT statement. that is where you have to put the conditions after WHERE.

  13. suleiman
    Posted 15 years ago #

    The thing is nathan, the part of the code that inserts posts into the wp_global_posts table is everything that I pasted above.

    What I don't really understand about your comment is that it was my understanding the $post variable had already been defined from earlier in the post.php file. If I were to adjust what qualified as a "post" in general, filtering spam, deleted, and private blogs from it, then wouldn't that have other consequences besides blocking spam, deleted, and private blog entries from the wp_global_posts table?

    I guess what I'm saying is, I don't really want to meddle with what constitutes a $post, I just want to filter out those posts going into the global posts table.

  14. Ovidiu
    Posted 15 years ago #

    any idea how I would modify my code to only pull posts that are not protected or private?

    my blobal posts list lists password protected posts, and although without the password you cannot read them it is quite annoying having them show up on the frontpage:

  15. suleiman
    Posted 15 years ago #

    Now that I know a little bit more about MySQL + PHP, I managed to conjure up this hack largely based on my earlier "hack," except that this one actually works :-p

    $publicblog = $wpdb->get_var("SELECT public FROM wp_blogs");

    if ($publicblog == 1){

    code to insert into wp_global_posts goes here


  16. suleiman
    Posted 15 years ago #

    Edit: This is starting to get irritating.

    Here's what I've done with my code to get only posts from public blogs to go into the category cloud:

    $publicblog = $wpdb->get_var("SELECT public FROM wp_blogs");

    if ($post_type == "post" AND $publicblog == "1") {

    For some reason even when blogs are public their posts aren't making into the category cloud!

    I've exhausted all thought as to how to do this. If anyone else has any tips I'd be much obliged.

  17. quenting
    Posted 15 years ago #

    cache issue ?

  18. suleiman
    Posted 15 years ago #

    Actually, no.

    I feel silly exclaiming "I FINALLY FIXED IT!" for the third time. But this time it's really true mommy, I swear!

    The process involved the following:

    1) add
    $public_blog = get_blog_status( $wpdb->blogid, 'public');

    2) each line that is part of the global category plugin that reads: if ($post_type == "post" ) should be changed to
    if ($post_type == "post" AND $public_blog == 1)

    That's it. I hope. I pray.

    The only remaining problem with this plugin is that it fails to insert posts into the table if the category includes an apostrophe. From what I've read/seen this should involve a quick fix via stripslashes, but after tonight I'm somewhat uneasy about presuming I have the fix in mind when in fact I do not.

About this Topic

  • Started 15 years ago by suleiman
  • Latest reply from suleiman