executing a mysql query on publishing of post

The tricky part is identifying an action that fires only when the post is first published. ‘save_post’ also fires when the post is updated, so is no good. The action you want is actually ‘draft_to_publish’, which I determined by experimenting with the ‘transition_post_status’ action. See the source code for wp_transition_post_status() for insight into what’s going on with various actions. You may wish to experiment yourself instead of taking my word for it, I’ve been wrong before.

So you hook into the appropriate action with add_action(). Your action callback function is passed the post object. From that you get the post ID which you can use in wp_get_post_categories() to determine if the categories meet your criteria. If it all checks out, run your query.

thank you I’ll give it a go

Thanks for your help. I’m still having some problems though. I’ve put the below code into the functions.php file. I’m trying to run the query when I make a post with both categories 2 and 3 selected. But it’s now breaking wordpress and not working. Do you know what I’m doing wrong? I’m pretty new to this stuff aren’t very familar with if statements. any further help would be amazing.

function add_point( $query ) {
if ( $post_categories = array( ‘category__and’=>array(2,3) )) {
mysqli_query($con,”UPDATE persons SET Age=Age+1
WHERE FirstName=’Peter’ AND LastName=’Griffin'”);
}
}

add_action( ‘draft_to_publish’, ‘add_point’ );

Um, yeah, there’s several things wrong ??

One is your function is passed a post object, not a query object. Sure, $query is just a label and can be used, but it confuses my feeble brain. I can think better if it were $post. OK, that one is not technically wrong, just confusing.

Two, you are using the assignment operator ‘=’ inside the if() parameter, which can have it’s place, but we normally see the equivalence operator ‘==’ used in this situation.

Third, you are using a query syntax for the if() parameter, nice try, but if() does not understand it. It needs some help. First get the categories using wp_get_post_categories() and assign the result to a variable. Then you can use the in_array() function as if() parameters.

You can logically connect if parameters with the AND operator ‘&&’. So you end up with the first part like this:

function add_point($post) {
   $cats = wp_get_categories($post->ID);
   if(in_array(2, $cats) && in_array(3, $cats)) {
   // do the rest

Fourth, your connection variable $con is now out of scope and your update query will fail. Either establish the connection as a global or initiate the connection within the function’s scope. If your table is in the same DB as WP, you can use the global $wpdb connection object, which has its own methods, to run your update query.

Finally, you may want to collect the query return value and throw some sort of error if it fails. Not required if failure is of no consequence.

Try addressing these issues and see if it works for you. Good luck!

thank you I think I’m almost there. Cheers