Question 165303 in Wordpress Question posted in
The official Wordpress documentation can be found here.

Can WPDB query get info from wp_usermeta & filter specific meta_keys & meta_values?

SQL is not my strength and i am trying to get values from different tables in the WordPress Database, i need to get user and user_mail from wp_user, date and orderid from wp_wc_lottery_log and meta_key, meta_value for billing_address_1, billing_first_name, billing_last_name, billing_phone from wp_usermeta. This is the code i have but i cannot get to display those meta_values in the corresponding meta_key, is there something wrong with my Query? Thanks!

function get_data_lotto( $args ) {
    global $wpdb;
    
    $results = $wpdb->get_results( "SELECT user_login,date,orderid,user_email,meta_key,meta_value FROM wp_wc_lottery_log INNER JOIN wp_users ON wp_wc_lottery_log.userid=wp_users.id INNER JOIN wp_usermeta ON wp_wc_lottery_log.userid=wp_usermeta.user_id WHERE lottery_id=1161" );

    $content = '<table id="tabla-rifa" class="cell-border compact stripe" data-page-length="100">';
    $content .= '<thead><tr><th>ID Ticket</th><th>Usuario</th><th>E-Mail</th><th>Fecha y Hora</th><th>Nº de orden</th><th>Nombre</th><th>Dirección</th><th>Teléfono</th></tr></thead><tbody>';
    $count = 1;
    foreach ( $results AS $result ) {
        $content .= '<tr>';

        $content .= '<td>' . $count . '</td>';
        $content .= '<td>' . $result->user_login . '</td>';
        $content .= '<td>' . $result->user_email . '</td>';
        $content .= '<td>' . $result->date . '</td>';
        $content .= '<td>' . $result->orderid . '</td>';
        //ADD META_VALUE AND META_KEY
        $content .= '</tr>';

        $count++;
    }
    $content .= '</tbody></table>';

    return $content;
}
Tags:

2

Answers


  1. 0

    There was some inconsistency in your detail.

    Your basic form is reasonable, but you were missing criteria for the meta_keys:

    SELECT wp_wc_users.user_login
     , wp_wc_lottery_log.date
     , wp_wc_lottery_log.orderid
     , wp_wc_users.user_email
     , wp_usermeta.meta_key
     , wp_usermeta.meta_value
  FROM wp_wc_lottery_log
  JOIN wp_wc_users
    ON wp_wc_lottery_log.userid = wp_wc_users.id
  JOIN wp_usermeta
    ON wp_wc_lottery_log.userid = wp_usermeta.user_id
   AND wp_usermeta.meta_key IN ('billing_address_1', 'billing_first_name', 'billing_last_name')
 WHERE wp_wc_lottery_log.orderid = 1161
;

    You may need to also recheck your spelling and actual columns in the tables.

    The description mentioned columns to return, but not about the rest of the columns needed by the query.

    Provide all columns in each table needed for your query, not just those to be returned.

    Add that detail to your question.

    Login or Signup to reply.
  2. 0

    Based on what I think you mean. Try updating your query to this:

    $results = $wpdb->get_results( "
    SELECT ll.user_login,ll.date,ll.orderid,ll.user_email,um.meta_key,u.meta_value 
    FROM wp_wc_lottery_log ll 
    INNER JOIN wp_users ON ll.userid=wp_users.id 
    INNER JOIN wp_usermeta um ON ll.userid=wp_usermeta.user_id WHERE ll.lottery_id=1161" 
);

    When you’re performing joins, you have to scope out the tables in each statement, so MYSQL knows which table each field is from.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search