Manually changing post status via MySQL command

  • Hi All,

    I recently paid a developer to code a system for my new website that does some stuff when a post is changed from ‘Draft’ to ‘Published’.

    I now have a problem, that he’s left us with an incomplete script, that is no good to us unless we can fix the problem below.

    The system he created adds a button in the admin panel that i press to ‘approve and publish’ the post.
    I DO NOT press the default WP Publish button.

    His script then does it’s bit of processing and then updates the database directly to change the post status to ‘Publish’.

    However, what i’m finding is, when the post is created (via Gravity forms) and saved as a draft the post slug (that forms part of the permalink) is in tact as it should be.
    Then when i ‘approve and publish’ the post, after is script has worked (and successfully published the post) the post slug is actually blank.

    If i look at the database for that post i can see that the ‘post_name’ field is actually blank too.

    Does anybody know why this would be happening… there’s obviously something in the default publish function that inserts the ‘post_name’ data in to that field in the database… i’m guessing by manually updating the post status in the DB we’re missing this step.

    Hope this makes sense and somebody is able to help!?

Viewing 4 replies - 1 through 4 (of 4 total)
  • Thread Starter bjarvis2785

    (@bjarvis2785)

    Ok, so after a few more tests… i’ve once again used Gravity forms to create a ‘draft’ post on my WP site.

    Then i checked the DB and noticed that the post_name field isn’t actually filled in at this stage.
    So when i then went back and hit the default WP ‘Publish’ button it filled in the field as it should with the ‘slug’.

    So, the script i mentioned above isn’t technically doing anything wrong as i initially thought.

    So, my next question is… is it even possible to manually change the ‘post_status’ to ‘publish’ via a MySQL command or other method and actually have it fill in the post_name filed (and also the post_date_gmt field – which i also noticed is left blank at draft status)??

    It is. You just need to find where the update is being done and add the slug in there as well.

    What you (probably) have there:

    $query = "UPDATE ".$wpdb->posts." SET post_status = 'publish' WHERE id = ".$post->ID;

    What you’d need to change it to:

    UPDATE ".$wpdb->posts." SET post_status = 'publish', post_name = '".sanitize_title ($post->post_title)."' WHERE id = ".$post->ID;

    That doesn’t have aht error checking so I’m sure that there’s more ot it then just that, but that’s the basics of how it’s done.

    Thread Starter bjarvis2785

    (@bjarvis2785)

    Thanks for the reply catacaustic…

    This is where it may get a bit trickier.
    The script this guy wrote is in Python.

    So, the code that takes care of updating the DB is as follows:

    debug("Connecting to MySQL databse")
            db = MySQLdb.connect(**sql_cred)
            cur = db.cursor()
            debug("Setting publish status")
            cur.execute(r'UPDATE tm1_posts SET post_status="publish" WHERE ID = %s;' % post_id)
            debug("Setting ZIP URL")
            cur.execute(r'UPDATE tm1_postmeta SET meta_value="%s" WHERE post_id = %s AND meta_key = "file_url";' % (url, post_id))
            debug("Setting zipping_status to processed")
            cur.execute(r'UPDATE tm1_postmeta SET meta_value="processed" WHERE post_id = %s AND meta_key = "zipping_status";' % post_id)
            debug('Reset post_name')
            cur.execute(r'UPDATE tm1_posts SET post_name="test" WHERE ID = %s;' % post_id)
            cur.close()
            db.commit()
            # Save our changes to the database
            debug("Post published")

    You can see above in the line cur.execute(r'UPDATE tm1_posts SET post_name="test" WHERE ID = %s;' % post_id) i actually edited this and just tried inserting plain text “test” in to the field instead of trying to insert the text from a variable set earlier in the code.
    Even this doesn’t update the ‘post_name’ field.

    I then went on and combined the above line with the query that sets the publish status (like in your code example) and ran the script… it set the publish status correctly but didn’t update the post_name field…

    i’m stumped with this one!

    I can’t offer much assistance with this apart form saying that doing this in Python was perhaps the wrong thing to do. You can’t use any of the built-in WordPress functions, and you can’t use the built-in $wpdb object for database interaction. If it was me, I’d build it the correct way – in PHP, connected to the WordPress functions, and using the WordPress functions. That way you’ll get the script to do what you want it to.

    Looking at the code above I don’t like the queries. I’ve always been taught that you should not use double-quotes inside SQL strings. That might just be my own ideas, but it could also be causing some issues. Without knowing Python, I can’t say much though.

Viewing 4 replies - 1 through 4 (of 4 total)
  • The topic ‘Manually changing post status via MySQL command’ is closed to new replies.