WordPress.org

Ready to get started?Download WordPress

Forums

[resolved] need hep writing specific SQL query for $WPDB object (9 posts)

  1. kc11
    Member
    Posted 3 years ago #

    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

  2. Joseph
    Member
    Posted 3 years ago #

    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.

  3. kc11
    Member
    Posted 3 years ago #

    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

  4. Mark / t31os
    Moderator
    Posted 3 years ago #

    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'>" ) );
  5. kc11
    Member
    Posted 3 years ago #

    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

  6. Mark / t31os
    Moderator
    Posted 3 years ago #

    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

  7. kc11
    Member
    Posted 3 years ago #

    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

  8. Klark0
    Member
    Posted 3 years ago #

    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-->.

  9. Mark / t31os
    Moderator
    Posted 3 years ago #

    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..

Topic Closed

This topic has been closed to new replies.

About this Topic

Tags

No tags yet.