1

So, I have been working on an SQL query involving the users and usermeta tables in wordpress. I am not getting the correct results. I have the following query written

SELECT 
    * 
FROM 
    IL9_users 
LEFT JOIN 
    ( 
    SELECT 
        user_id, 
        MAX(case when meta_key = 'first_name' then meta_value end) first_name, 
        MAX(case when meta_key = 'last_name' then meta_value end) last_name
    FROM 
        IL9_usermeta 
    ) AS um 
ON 
    IL9_users.ID = um.user_id

This code returns all of the results except the correct values for first_name and last_name. The first value returned is incorrect, but at least it is being returned. All of the other values are null. Does anyone have any insight on this?

  • I was able to solve it on my own by using the basic WP query and then running a secondary query that I knew worked on a table I had created via the plugin I built. I cannot post the code as it is too long to fit in this response, but I essentially built 2 foreach loops, first I took the data from the table I needed the original data from and then I did a foreach loop using the data from the basic WP User Query based on the ID of the first foreach loop in order to get the right data needed. – hosker Jan 29 '16 at 3:04
  • You should post an answer if you've solved it, instead of adding a comment It's OK to answer your own question, and you can even choose to accept it after 48 hours. – Gabriel Jan 29 '16 at 3:06
-1

Here is my answer in code form:

<?php

// Customer Details

$args = array(
'blog_id'      => $GLOBALS['blog_id'],
'role'         => 'customer',
'meta_key'     => 'last_name',
'meta_value'   => '',
'meta_compare' => '',
'meta_query'   => array(),
'date_query'   => array(),        
'include'      => array(),
'exclude'      => array(),
'orderby'      => 'last_name',
'order'        => 'ASC',
'offset'       => '',
'search'       => '',
'number'       => '',
'count_total'  => false,
'fields'       => 'all',
'who'          => ''
);


$bowlers = get_users( $args );

global $wpdb;

$games_left = $wpdb->get_results("SELECT id, user_id, sum(total_games) AS total_games, sum(games_played) AS games_played FROM `bowling_games` GROUP BY user_id");


?>


<div class="col-xs-12 col-sm-4">
    <h2>Users with 0 Games left</h2>
    <button><a href="" title="">Send Reminder to All</a></button>

    <?php

        foreach ( $games_left as $game_left ) {

            $total_games = $game_left->total_games;
            $games_played = $game_left->games_played;
            $user_id = $game_left->user_id;


            foreach ( $bowlers as $bowler ) {

                if ( $user_id == $bowler->ID ) {


            if ( ( $total_games - $games_played ) == 0 ) { ?>

                <p><?php echo "User ID: " . $user_id . " Name: " . $bowler->first_name . " " . $bowler->last_name . " Email: " . $bowler->user_email  . " Games Left: " . ( $total_games - $games_played ); ?></p>

        <?php       
            }}

        }
        }


    ?>





</div>
  • Please add an explanation to your answer: why could that solve the problem? – fuxia Jan 29 '16 at 18:16
-1

WordPress defines a class called wpdb, which contains a set of functions used to interact with a database.

First to initialize a global object variable, $wpdb, which is an instantiation of the wpdb class defined in /wp-includes/wp-db.php.

<?php
    require_once( '../../../wp-config.php' );
    global $wpdb;

if ( is_user_logged_in() ) {
    $current_user = wp_get_current_user();
    $ID = $current_user->ID;
    $data = $wpdb->get_results("SELECT * FROM `wp_usermeta` WHERE `user_id` = ".$ID);
    // Where $ID current user id and you can get it by wp_get_current_user() function    
        foreach($data as $k=>$v){
            if($data[$k]->meta_key == 'wp_capabilities'){
                $roles = unserialize($data[$k]->meta_value);
                foreach($roles as $key => $val){
                    echo "Key: ".$key."--"."Value: ".$val;
                    echo "<br />";
                }
            }
        }
    }
?>
  • Please take your time and properly format your posts. We cannot continue to clean up your mess. – Pieter Goosen Jan 29 '16 at 6:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.