Why is $wpdb->get_results failing on certain tables but not others (which have data)?

Question

I'm trying to pull some custom data from a Cart66 table.

My code works whenever I pull results from standard WP table in (my array is populated and the print_r shows that data), but not with any Cart66 tables (it prints an empty array).

I am absolutely certain that I've got data in these tables (I've rechecked that I'm using the correct table names about 1,000,000 times). I've tried two Cart66 tables which have data in them.

The codex says the get_results class should work with any table, not just standard tables. I thought it might be the underscore that was causing trouble, but underscores are supposed to be fine, and indeed, standard WP tables with underscores work fine.

$rawproducts = $wpdb->get_results( "SELECT * FROM $wpdb->cart66_products" );
print_r($rawproducts);

This is maddening! Any ideas?

Thanks!

Answer 1

you do not have to specify the $wpdb again in your query, but be sure that your table uses the prefix, if it has one. if it doesn't, skip the part with the prefix. also, you should always prepare a manually added query first. this should do it:

global $wpdb;
$query = $wpdb->prepare( "SELECT * FROM %s", "{$wpdb->prefix}cart66_products" );
$rawproducts = $wpdb->get_results( $query );
print_r($rawproducts);

Answer 2

global $wpdb;
$query = $wpdb->prepare( 
    "
        SELECT * FROM %s
    ",
    "{$wpdb->prefix}cart66_products" 
);
$results = $wpdb->get_results( $query );

$wpdb->show_errors();
echo '<pre>';
    // Show results
    echo "<hr />Results:\n";
    print_r( $results );

    // Show errors
    echo "<hr />Errors:\n";
    if ( current_user_can( 'manage_options' ) AND defined( 'WP_DEBUG' ) AND WP_DEBUG AND defined( 'WP_DEBUG_DISPLAY' ) AND WP_DEBUG_DISPLAY )
    {
        $wpdb->print_error();
    }
echo '</pre>';

// Savely remove errors for guest or non admins
$wpdb->hide_errors();

More info about error handling inside WPDB can be found here in Codex.

Answer 3

<?php
global $wpdb;
$table_name = $wpdb->prefix . 'cart66_products';
$i = 0;
$obj = [];

$query = $wpdb->prepare(
    "SELECT * FROM $table_name"
);

$result = $wpdb->get_results(
    $query
);
$wpdb->show_errors();
foreach ($result as $row) {
    $obj[$i] = $row;
    $i++;  
}
    echo '<pre>';
    echo "<hr />Results:\n";
    echo json_encode($obj);
?>