how to use mysql instr function in wordpress

  • Resolved kc11

    (@kc11)


    13 years, 5 months ago

    Hello,

    I am trying to write a mysql query using the $wpdb object, but so far I am getting a mysql syntax error and empty returned record set. My code is

    $postID = $wpdb->get_results( "SELECT ID FROM $wpdb->posts where instr(post_content, ']') > 0");

    Thank you,

    KC

Viewing 4 replies - 1 through 4 (of 4 total)
  • Mark / t31os

    (@t31os_)

    13 years, 5 months ago

    Your query works perfectly fine for me, have you considered that the query might simply not be finding any matches?

    I get 8 results from your query on my test install..

    NOTE: If you only want the IDs, use get_col…

    $wpdb->get_col( "SELECT ID FROM $wpdb->posts where instr(post_content, ']') > 0");

    This way you’ll get a flat array of IDs, instead of an array of objects..

    Thread Starter kc11

    (@kc11)

    13 years, 5 months ago

    Thanks Mark,

    I had originally used :

    $start = '[';
$postID = $wpdb->get_results( "SELECT ID FROM $wpdb->posts where instr(post_content, $start) > 0");

    which does not work. However,

    $postID = $wpdb->get_results( "SELECT ID FROM $wpdb->posts where instr(post_content, '[') > 0");

    does work.

    KC

    Mark / t31os

    (@t31os_)

    13 years, 5 months ago

    Ah i see the problem, when you use the sting method, you actually lose the single quotes you need around the value inside the query..

    The instr call ends up like this..

    instr(post_content, [ )

    You could use the original method, you just need to include the single quotes inside the string..

    $start = "'['";

    This way the single quotes get passed along into to the SQL statement..

    🙂

    Thread Starter kc11

    (@kc11)

    13 years, 5 months ago

    Hi Mark,

    That’s very helpful.

    KC

Viewing 4 replies - 1 through 4 (of 4 total)
  • The topic ‘how to use mysql instr function in wordpress’ is closed to new replies.