Populate SQL query VALUES with Custom field values from Post scrn WordPress

• SOLVED

I run an SQL query in my functions.php file that populates fields automatically when a post is published.
The following command, runs successfully in phpMyAdmin- SQL, and it also reflects correctly in my wordpress backend as a new product added.

INSERT INTO `databasename`.`wp_xxxxxx` (
`id` ,
`name` ,
`item_number` ,
`price` ,
`options_2`) VALUES (
'xxxxxx1', 'xxxx2', 'xxxx3', 'xxxxx4', 'xxxx5'
);



So the php function for the above in my functions.php file is as follows:

function do_my_stuff($post_ID) {

mysql_select_db("databasename") or die(mysql_error());
mysql_query("INSERT INTO `databasename`.`wp_xxxxxxx` (
`id` ,
`name` ,
`item_number` ,
`price` ,
`options_2`) VALUES (
'xxxxx', 'xxxxxxx', 'xxxxxxxxx', 'xxxxxxxxx', 'xxxxxxxx, xxxxxx')");

return $post_ID;

}

add_action('publish_post', 'do_my_stuff');

Now, the issue i need resolved are the VALUES that need to be dynamically populated based on whatever i entered as a custom field in the post section.
Here are my desired VALUES for the sql query to populate.

<strong>id</strong> = the postID number of the post (or it can be random, auto incremental, doesn't really matter).
<strong>name</strong> = same as the post title
<strong>item_number </strong>= it should be a custom field post meta value, coming from a field named 'scode', at the moment i echo it on my single page template via:

<?php if ( get_post_meta($post->ID, 'scode', true) ) echo do_shortcode(get_post_meta($post->ID, 'scode', $single = true)); ?>


<strong>price </strong>= the price, which i also manually enter via a custom field called 'price' which i currently echo via :

<?php $values = get_post_custom_values("price"); echo $values[0]; ?>

<strong>options_2</strong>, which i also manually enter via a custom field value called 'variations'.

Please note that i do not know much about PHP and SQL etc. If it is possible, please try to modify my code and give it back how i should overwrite my existing one.

Answers (3)

2012-09-16

Dbranes answers:

You might try this


function do_my_stuff($post_ID) {
global $post,$wpdb,$flag;

if($flag==0 && $post->post_type == "post" && ( $post->post_status=="publish" || $post->post_status=="draft") ){

$tablename="tfl_cart66_products";
$id = $wpdb->get_var("SELECT id FROM ".$tablename." WHERE id=".$post_ID);
$data=array(
'id'=>$post_ID,
'item_number'=>get_post_meta($post_ID, 'scode', true),
'name'=>$_REQUEST["post_title"],
'price'=>get_post_meta($post->ID, 'price', true),
'options_2'=>get_post_meta($post_ID, 'variations', true)
);
$where = array("id" => $post_ID);

// Possible format values: %s as string; %d as decimal number; and %f as float.
$format=array( '%d', '%s', '%s', '%s', '%s');
$where_format = array( '%d' );

if($id>0){
// update
$wpdb->update( $tablename,$data, $where, $format, $where_format);
}else{
// insert
$wpdb->insert( $tablename,$data,$format);
}
$flag=1;
}
return $post_ID;
}
add_action('save_post', 'do_my_stuff');


2012-09-16

Hardeep Singh answers:

Hello,

Action to be used is "publish_<post type>"

This means that publish event for the specific post type.

E.g. If your post type is "product" then publish event will be "publish_product".


So your code will be something like:


function do_my_stuff($post_ID) {
mysql_query("INSERT INTO `databasename`.`wp_xxxxx` (
`id` ,
`name` ,
`item_number` ,
`price` ,
`options_2`) VALUES (
'9', 'xxxx1', 'xxxx2', 'xxxx3', 'xxxxx4'");
return $post_ID;
}
add_action('publish_product', 'do_my_stuff');



Thanks!

2012-09-16

paul de wouters answers:

you should use the wpdb class:

http://codex.wordpress.org/Class_Reference/wpdb#INSERT_rows

---

monitor comments:

Thanks Paul, that seems to look in the right direction. But i don't know how to construct that function so it runs without an error. I'm really bad at PHP. Is there any way you can construct it for me and let me overwrite my existing function?