$wpdb not returning data

Question

I have the feeling that the answer to my problem is going to be very simple. I have written the following code to access a table that I have loaded into my WordPress database. I can run the query in phpMyAdmin. It runs as expected. I have tried everything that I found in an attempt to get it to work.

My Function:

function plcoa_email_address_list () {
    global $wpdb;

    $user_count = $wpdb->get_var( "SELECT COUNT(*) FROM tblLots" );
    echo "<p>Lot count is " . $user_count . "</p>";
    $wpdb->show_errors( true );
    $result = $wpdb->get_results($wpdb->prepare("SELECT LotNum FROM tblLots;" ));
    $wpdb->print_error();
    if ($result->num_rows > 0) {
        // output data of each row
        while($row = $result->fetch_assoc()) {
            echo $row["email"] . "<br>";
        }
    } else {
        echo " <br>  0 results";
    }
}  
function plcoa_short_code( $atts=null, $content=null ){
    extract(shortcode_atts(array('id'=>''),$atts));
    plcoa_email_address_list ();
}
add_shortcode('listemails','plcoa_short_code');

The result that I get is:

Lot count is 369

WordPress database error: [] SELECT LotNum FROM tblLots;

0 results

Answer 1

Here's your problem:

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {

$result is not a mysqli_result object, it's literally the results of the query. Since you never passed a second parameter to the get_results method specifying what format you wanted them in, it'll assume "OBJECT", so the correct usage would be:

$results = $wpdb->get_results("SELECT * FROM tblLots" );
if ( !empty( $results ) ) {
    // output data of each row
    foreach ( $results as $row ) {
        echo $row['email'];

Notice I also corrected an error in the SQL statement, you only asked the database for the LotNum field, then tried to grab the email expecting a full database row, when you only asked for a single column

Notice as well that all of this is detailed in the codex and in the dev hub, on the official wp.org docs