WordPress.org

Ready to get started?Download WordPress

Forums

Easy: Query that searches all authors into MySQL (13 posts)

  1. Nikolas
    Member
    Posted 4 years ago #

    hello there.
    i am looking for a query to be used into my script that searches MySQL and finds all authors that have the value 1 into their user meta tag with name support.
    I have this as a sample. Any help is appreciate.

    <?php
    
     $querystr = "
        SELECT wposts.*
        FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
        WHERE wposts.ID = wpostmeta.post_id
        AND wpostmeta.meta_key = 'tag'
        AND wpostmeta.meta_value = 'email'
        AND wposts.post_status = 'publish'
        AND wposts.post_type = 'post'
        AND wposts.post_date < NOW()
        ORDER BY wposts.post_date DESC
     ";
    
     $pageposts = $wpdb->get_results($querystr, OBJECT);
    
    ?>
  2. vtxyzzy
    Member
    Posted 4 years ago #

    Sorry, I don't understand what you want.

    What information do you want about authors?

    Can you explain what you mean by 'user meta tag with name support'?

    Is there a particular meta_key and meta_value that you want?

  3. Nikolas
    Member
    Posted 4 years ago #

    thanks for your reply.

    Sorry for being not clear. However, yes, I created a new meta_key with the name "support". I want to list all authors that have value 1 into the "support" meta_key.
    Addionaly is there any possibility to create a new different field inside MySQL, containing the ID's of the authors with meta_value =1 , instead of just listing them ?

    Thank you for your support.

  4. vtxyzzy
    Member
    Posted 4 years ago #

    I understand a little better, but I still need to know what information you want to list about authors. The user information is kept in two separate tables: users and usermeta. I need to know what you want from each table.

  5. Nikolas
    Member
    Posted 4 years ago #

    Hey my friend I did it!

    SELECT user_id from wp_usermeta WHERE meta_key = 'support' AND meta_value = '1'

    this is what i run through phpmyadmin.
    How can I turn this into a wordpress query and show the result?

  6. vtxyzzy
    Member
    Posted 4 years ago #

    I think this will give you a start. Since I don't know exactly the way you want to display the id's, you will need to modify the echo to do what you want.

    <?php
     global $wpdb;
     $querystr = "
       SELECT user_id from $wpdb->usermeta WHERE meta_key = 'support'
          AND meta_value = '1'
       ";
    
     $userids = $wpdb->get_results($querystr, ARRAY_N);
     foreach ($userids as $userid) {
         echo "<p>User ID:$userid</p>";
     }
    ?>
  7. Nikolas
    Member
    Posted 4 years ago #

    hey there. I think i got it right twice and it is working!

    <?php
    $lastnames = $wpdb->get_col("SELECT user_id FROM $wpdb->usermeta WHERE $wpdb->usermeta.meta_key = 'support'  AND $wpdb->usermeta.meta_value ='1'");
      foreach ($lastnames as $userid) {
        $user = get_userdata($userid);
        $post_count = get_usernumposts($user->ID);
        $author_posts_url = get_author_posts_url($user->ID);
        echo '' . $user->ID . '';
      }
    ?>
    
    <?php
    $ud = $wpdb->get_col("SELECT user_id FROM $wpdb->usermeta  WHERE $wpdb->usermeta.meta_key = 'suppoer' AND $wpdb->usermeta.meta_value ='1'");
    echo print_r($ud) ; 
    
    ?>

    I have an error with yours, it gives me User ID: ARRAY.
    But THANKS you gave me the idea!!

    However, this is the first part.

    What if i want to show 10 random posts ( let's say 10 posts, it does not matter), but those posts are written only from authors that have the number 1 as a meta_value of "support" ?

    It is like a special box into my template, that displays 10 random posts, only from authors that have the number 1 as a meta_value into the support meta_key.
    All other authors ( with meta_value lets say 0) will not have any visible posts into this post.

    I guess its a JOIN, something that will merge the previous sql query. I can't get it clear though.

    maybe if there is a way to use this query
    query_posts('caller_get_posts=1&author=1&post_type=page&post_status=publish&orderby=title&order=ASC');
    and exclude or include the authors...

  8. vtxyzzy
    Member
    Posted 4 years ago #

    The query below HAS NOT BEEN TESTED, but it (or at least one similar) should get you an array of post IDs for the correct authors. You will need to select 10 of these at random into an array ($randomids) and use that in a query_posts('post__in' => $randomids).

    Good Luck!

    <?php
    
     $querystr = "
        SELECT wposts.ID
        FROM $wpdb->posts wposts, $wpdb->usermeta usermeta
        WHERE wposts.post_author = usermeta.user_id
        AND usermeta.meta_key = 'support'
        AND usermeta.meta_value = '1'
        AND wposts.post_status = 'publish'
        AND wposts.post_type = 'post'
        AND wposts.post_date < NOW()
        ORDER BY wposts.post_date DESC
     ";
    
     $postids = $wpdb->get_col$querystr);
    
    ?>
  9. Nikolas
    Member
    Posted 4 years ago #

    Haha it worked!
    I combined it with this to give me the IDS.

    <?php
    
     $querystr = "
        SELECT wposts.ID
        FROM $wpdb->posts wposts, $wpdb->usermeta usermeta
        WHERE wposts.post_author = usermeta.user_id
        AND usermeta.meta_key = 'support'
        AND usermeta.meta_value = '1'
        AND wposts.post_status = 'publish'
        AND wposts.post_type = 'post'
        AND wposts.post_date < NOW()
        ORDER BY wposts.post_date DESC
     ";
    
     $postids = $wpdb->get_col($querystr);
    
    foreach ($postids as $postid) {
         echo "<p>POST ID:$postid</p>";
     }
    
    ?>

    so now I have all the ids of posts that have the meta_value 1 into the support meta_key.

    I have to think about that now

    You will need to select 10 of these at random into an array ($randomids) and use that in a query_posts('post__in' => $randomids).

    You are great. I will get in contact with my tests.

  10. Nikolas
    Member
    Posted 4 years ago #

    vtxyzzy, do you know how to find the content of a custom field named "Thumbnail" depending on posts ID?
    It is in the wp_postmeta but I can make it working it with
    FROM $wpdb->posts wposts, $wpdb->usermeta usermeta, $wpdb->postmeta
    and then
    $thumb = get_post_meta($postid, "thumb", true);

    but if i chenge the postid to a number and echo it, it gives me a result, but not dynamically

    Because in the end, I want to extract the title, link and thumbnail of the post.
    I will donate you if you want ....

  11. Nikolas
    Member
    Posted 4 years ago #

    Hey i did it!! To anyone who ever want the code, this is it (the code below is tested if they hav a last_name for a bigger sample) :
    So, how can I random those 5 posts...

    <ul>
    <?php
    global $wpdb;
    
    $sql = "SELECT wposts.*
    	FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta, $wpdb->usermeta usermeta
    	WHERE wposts.ID = wpostmeta.post_id
    	AND usermeta.meta_key = 'last_name'
    	AND wpostmeta.meta_key = 'Thumbnail'
    	AND wposts.post_status = 'publish'
    	AND wposts.post_author = usermeta.user_id
    	AND wposts.post_type = 'post'
    	ORDER BY wposts.id DESC LIMIT 8";
    
    $pageposts = $wpdb->get_results($sql, OBJECT);
    
    foreach ($pageposts as $post) : setup_postdata($post);  ?>
    
    <li><a href="<?php echo get_post_meta($post->ID, "gallery-url", $single = true); ?>" title="Link to <?php the_title(); ?>">
    <img src="<?php echo get_post_meta($post->ID, "image", $single = true); ?>" alt="<?php the_title(); ?>" /></a></li>
    
    <?php endforeach; ?>
    </ul>
  12. vtxyzzy
    Member
    Posted 4 years ago #

    Assuming you have an array $postids, and want 5 of them, try this (TOTALLY UNTESTED!):

    $randomids = $postids;
    while (sizeof($randomids) > 5) {
       $randidx = mt_rand(0, sizeof($randomids) - 1);
       unset($randomids[$randidx]);
    }

    There are probably more efficient ways to do this, especially if there are a large number of postids.

  13. vtxyzzy
    Member
    Posted 4 years ago #

    Thanks for you offer of a donation, but it will be enough if you just help someone else in the future.

Topic Closed

This topic has been closed to new replies.

About this Topic

Tags

No tags yet.