Support » Plugins » Hacks » need hep writing specific SQL query for $WPDB object

  • Resolved kc11

    (@kc11)


    Hi Everyone,

    I am trying to write a query that will replace the current first post content with another text string. I’m using a standard wordpress database with some of the fields in wp_posts table as follows:

    ID
    post_author
    post_date
    post_date_gmt
    post_content
    post_title

    I am using the following query.( the ID I’m trying to replace = 1 )

    UPDATE 'wp_posts' SET 'post_content' = replace( 'post_content', '*', 'string_to_find' ) WHERE ID ='1';

    I’m getting a syntax error. Can anyone explain this?

    Thank you,

    KC

Viewing 8 replies - 1 through 8 (of 8 total)
  • Try this:

    global $wpdb;
    $wpdb->query( $wpdb->prepare( "UPDATE $wpdb->posts SET post_content = %s WHERE ID = 1", $string ) );

    where $string is the string to replace with.

    Thread Starter kc11

    (@kc11)

    Hi Joseph,

    That works! Thank you.

    However I now have another problem. Using your code the following works:

    $wpdb->query( $wpdb->prepare( "UPDATE $wpdb->posts SET post_content = %s WHERE ID = 7", 'hello world! ' ) );

    I am now trying to insert an image into this post by replacing ‘hello world’ with “<img border=’0′ alt=’$photo[title]’ “.”src=” . $x . “>”;echo “” ( which works elsewhere ) ( $x contains the image info ). So I am using:

    $wpdb->query( $wpdb->prepare( "UPDATE $wpdb->posts SET post_content = %s WHERE ID = 7", '"<img border='0' alt='$photo[title]' "."src=" . $x . ">";echo "</a>"' ) );

    But I get the following error:

    Parse error: syntax error, unexpected T_LNUMBER in ..wp-content\plugins\test2\test2.php on line 59

    Can you give me any direction?

    Thanks again,

    KC

    You can’t echo inside a query, echo is for outputting data..

    Try..

    $wpdb->query( $wpdb->prepare( "UPDATE $wpdb->posts SET post_content = %s WHERE ID = 7", "<img border='0' alt='$photo[title]' src='$x'>" ) );

    Thread Starter kc11

    (@kc11)

    Mark,

    Thank you. That worked.

    One last question on this topic: I would never have thought of nesting

    ‘$wpdb->prepare’ inside ‘$wpdb->query’ as you and Joseph do in your SQL statements. I couldn’t find any reference to this in the codex. How did you know to do this , and can you point me to some reference on this syntax?

    Thank you,

    KC

    Covered on the codex page for the $wpdb class..
    http://codex.wordpress.org/Function_Reference/wpdb_Class#Protect_Queries_Against_SQL_Injection_Attacks

    It’s preferable to use an existing function where possible and avoid the need to do direct queries.

    For updating a post i wonder if wp_update_post() might cover what you’re doing(and avoid the need to write your own query).
    http://codex.wordpress.org/Function_Reference/wp_update_post

    And additionally if you need to change a post’s terms(eg. category, tags).
    http://codex.wordpress.org/Function_Reference/wp_get_post_terms – Get a post’s terms
    http://codex.wordpress.org/Function_Reference/wp_set_post_terms – Set a post’s terms

    Meta data.
    http://codex.wordpress.org/Function_Reference/get_post_meta – Get a post’s meta
    http://codex.wordpress.org/Function_Reference/update_post_meta – Update or Add meta

    Thread Starter kc11

    (@kc11)

    Wow!

    There’s obviously a lot more in the codex than I thought. Thank you Mark. I’m going to close this thread and read these over .

    Regards,

    KC

    Hey,

    maybe you guys can help. i wanna insert the more tag into a couple thousand posts.

    what command would i need to insert the more tag after the first 50 words.

    I don’t know much, here’s where i’m stuck:

    update wp_posts set post_content
    now need someway to count the post words then insert tag <!–more–>.

    I think that’s beyond what you’ll be able to do in a MySQL query(at least beyond my mysql know-how), i’d imagine you’ll need to first query for posts, run over the content, counting upto the 50 word point, and sending an updated version back… You should be able to use wp_update_post() to update the necessary post(s) though..

Viewing 8 replies - 1 through 8 (of 8 total)
  • The topic ‘need hep writing specific SQL query for $WPDB object’ is closed to new replies.