Custom Query to Return usermeta information

  • Resolved EzySetup

    (@ezysetup)


    1 year ago

    Please redirect me if this is not appropriate for this forum. And FYI, more than a decade ago, I used PL-SQL quite a lot using the application PL-SQL Developer. My experience with php is quite limited.

    What I wish to do is have a custom page (for example, “dbtest.php”) and pull meta values from the WP database. I’m only being partially successful. All the data I need is in the usermeta table. At this time, I don’t need to join any other tables.

    I need to return the meta values for the following meta keys:

    • first_name
    • last_name
    • nickname
    • textarea_zknsk
    • textarea_o0nn8
    • textarea_fjy8e

    I only want to return the records where one, two, or three of the three textarea fields have values.

    Following is the code I’m using that partially works. I’m having two major issues.

    • I need to use a loop. At the start, you will see the query that returns the max user_id in the table. But when I substitute in “IDmax” for the “$x” in the loop, it does not work. How do I increment the loop and then stop at at the IDmax?
    • The loop pulls records for every user_id. I need a CASE (IF) set that will constrain the results to only user_ids where one or more of the textarea fields are populated. If the three textarea field’s meta values are empty then I don’t want the record. But I’m at a loss as to how to construct it.
    //  Return Metadata ONLY.
	    $IDmax = $wpdb->get_results("select max(user_id) FROM $wpdb->usermeta");
for ($x = 0; $x <= 18; $x++) {
$results1 = get_user_meta( $x, 'first_name', true );
$results2 = get_user_meta( $x, 'last_name', true );
$results3 = get_user_meta( $x, 'nickname', true );
$results4 = get_user_meta( $x, 'textarea_zknsk', true );
$results5 = get_user_meta( $x, 'textarea_o0nn8', true );
$results6 = get_user_meta( $x, 'textarea_fjy8e', true );
echo "<pre><strong>First Name: </strong>";
  print_r($results1);echo "</br>";
echo "<strong>Last Name: </strong>";
  print_r($results2);echo "</br>";
echo "<strong>Nickname: </strong>";
  print_r($results3);echo "</br>";
echo "<strong>Board Member Notes: </strong>";
  print_r($results4);echo "</br>";
echo "<strong>Board Secretary Notes: </strong>";
  print_r($results5);echo "</br>";
echo "<strong>Board Treasurer Notes: </strong>";
  print_r($results6);echo "</pre>";
}

    Any help will be greatly appreciated.

Viewing 2 replies - 1 through 2 (of 2 total)
  • Thread Starter EzySetup

    (@ezysetup)

    UPDATE:

    I was able to use an IF statement in the PHP rather than a CASE statement in the SQL. I am returning the data I need. But I still can’t figure out how to use the max user_id in the loop. Of course, I’ll be happy to accept any suggestions on a better way to go about it.

    //  Return Metadata ONLY.
/* $IDmax = $wpdb->get_results("select max(user_id) FROM $wpdb->usermeta"); */
for ($x = 0; $x <= 18; $x++) {
  $results1 = get_user_meta( $x, 'first_name', true );
  $results2 = get_user_meta( $x, 'last_name', true );
  $results3 = get_user_meta( $x, 'nickname', true );
  $results4 = get_user_meta( $x, 'textarea_zknsk', true );
  $results5 = get_user_meta( $x, 'textarea_o0nn8', true );
  $results6 = get_user_meta( $x, 'textarea_fjy8e', true );
if (!empty($results4) 
  or !empty($results5) 
  or !empty($results6)) {
echo "<pre><strong>First Name: </strong>";
  print_r($results1);echo "</br>";
echo "<strong>Last Name: </strong>";
  print_r($results2);echo "</br>";
echo "<strong>Nickname: </strong>";
  print_r($results3);echo "</br>";
echo "<strong>Board Member Notes: </strong>";
  print_r($results4);echo "</br>";
echo "<strong>Board Secretary Notes: </strong>";
  print_r($results5);echo "</br>";
echo "<strong>Board Treasurer Notes: </strong>";
  print_r($results6);echo "</pre>";
  } 
}
    Thread Starter EzySetup

    (@ezysetup)

    Update:

    After about 8 hours of work, I have something working. Whether it is optimal code or not, I have no idea. But it returns the information I need. If anyone has any input on a better way to go about returning and formatting the data, I’m certainly open to suggestions.

    <?php
    //  Require_Once, so no conflicts if previously loaded
        require_once('wp-load.php');
    
    //  Declaring $wpdb as global and using it to execute an SQL query statement that returns a PHP object
	    global $wpdb;
    
    //  Primary Query    
        echo "<p>Primary Query:</p>";
        $results0 = $wpdb->get_var("select max(user_id) FROM $wpdb->usermeta");
        for ($x = 0; $x <= $results0; $x++) {
            $results1 = $x;
            $results2 = $wpdb->get_var("SELECT a.user_login FROM wp_users a WHERE a.id = $x");
            $results3 = get_user_meta( $x, 'first_name', true );
            $results4 = get_user_meta( $x, 'last_name', true );
            $results5 = get_user_meta( $x, 'nickname', true );
            $results6 = get_user_meta( $x, 'textarea_zknsk', true );
            $results7 = get_user_meta( $x, 'textarea_o0nn8', true );
            $results8 = get_user_meta( $x, 'textarea_fjy8e', true );
            if (!empty($results6) or !empty($results7) or !empty($results8)) {
                echo "<pre><strong>User ID: </strong>";print_r($results1);echo "</br>";
                echo "<strong>User Name: </strong>";print_r($results2);echo "</br>";
                echo "<strong>First Name: </strong>";print_r($results3);echo "</br>";
                echo "<strong>Last Name: </strong>";print_r($results4);echo "</br>";
                echo "<strong>Nickname: </strong>";print_r($results5);echo "</br>";
                echo "<strong>Board Member Notes: </strong>";print_r($results6);echo "</br>";
                echo "<strong>Board Secretary Notes: </strong>";print_r($results7);echo "</br>";
                echo "<strong>Board Treasurer Notes: </strong>";print_r($results8);echo "</pre>";
            } 
        }
?>
Viewing 2 replies - 1 through 2 (of 2 total)
  • The topic ‘Custom Query to Return usermeta information’ is closed to new replies.