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.

  • you're not using prepare correctly, see the examples here and here – Milo Nov 20 '12 at 19:09
  • @Milo I am using the same code in my plugin file where it works as expected. Is there any difference as to how wordpress works with my_plugin.php and functions.php – Deepak Kundu Nov 20 '12 at 19:14

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.

  • 1
    BTW, note that if $code isn't hardcoded like you have there, and is subject to user modification, then your code has an SQL Injection Exploit in it. – Otto Nov 20 '12 at 20:05

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


Try it like this:

// functions.php
function wpse73348_custom_table_query_test( $dump = false )
    global $wpdb;
    $result = $wpdb->query( $wpdb->prepare(
            SELECT * FROM %s
            WHERE `code` = %s
        ,$wpdb->get_blog_prefix().'fxbase '
    ), 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 );
  • The above code still does not show any result. var_dump on $result gives NULL bool(true). If however, I search in the column 'id' (primary), I do get a result, which I was getting from the original code as well that I was using. The column 'code' in mysql is of datatype varchar if that could be causing any error. – Deepak Kundu Nov 20 '12 at 19:39
  • @kaiser: You should not have the backticks around the %s, nor should you need run the table name through prepare (it won't work if you do). I'll edit your post to correct. – Otto Nov 20 '12 at 19:42
  • @Otto Yeah, you're right. I copy/pasted most of his code - my wrong. Just edited to ... damn. – kaiser Nov 20 '12 at 19:45
  • @Otto We were editing in parallel. There's much more crap in there. echo '<pre>'.var_dump(... won't work either. – kaiser Nov 20 '12 at 19:45
  • I'm done, feel free to fix it. – Otto Nov 20 '12 at 19:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.