Wordpress $wpdb no result

Question

I have the following code in my functions.php

$code = 'EUR';
global $wpdb;
$query = 'SELECT * FROM ';
$query .= $wpdb->get_blog_prefix() . 'fxbase ';
$query .= 'WHERE code= '.$code;
$data = $wpdb->get_row( $wpdb->prepare( $query ), ARRAY_A );

The problem is the above query does not give any result. Just to test the following query in mysql is working fine:

SELECT * FROM `1621_fxbase` WHERE `code` = 'EUR'

Not really able to figure out what is the issue here.

Answer 1

Your specific issue in that code is that you're missing the quote marks around the $code.

$query .= 'WHERE code= '.$code;

Should be:

$query .= 'WHERE code= "'.$code.'"';

In the long run, you should indeed use prepare() properly, but this is the specific problem with the code you posted.

Answer 2

prepare() the right way

Things that don't need prepare: Default objects from $wpdb (make a dump to see what it got)

• Everything that's built into core: Table names, "Field", Row & Column names

Everything else must get prepared. And this isn't only user input, but also everything else like custom table, "field", row & column names.

Rule of thumb: Prepare as late as possible and let come nothing between the preparation and the query (if possible).

You can only prepare two things:

• %s = strings
• %d = digits

Example

Try it like this:

// functions.php
function wpse73348_custom_table_query_test( $dump = false )
{
    global $wpdb;
    $wpdb->flush();
    $result = $wpdb->query( $wpdb->prepare(
         "
            SELECT * FROM %s
            WHERE `code` = %s
         "
        ,$wpdb->get_blog_prefix().'fxbase '
        ,$code
    ), ARRAY_A );
    0 >= $wpdb->num_rows AND $result = 'No result from query';

    if ( ! $dump )
        return $result;

    // Debug result
    echo '<pre>'.var_export( $result, true )
         "\n Addt. Info: \n".var_export( $wpdb->last_result, true ).var_export( $wpdb->last_query, true ).var_export( $wpdb->col_info, true ).'</pre>';
}

// In some template.php
wpse73348_custom_table_query_test( true );