SQL query to duplicate a meta field, and properly insert into php WordPress
- SOLVED
I need to run two SQL queries in a cron php.
Firstly, I have a custom field called 'variations' on each post. (see the Red square in the image)
I want that field to be duplicated and called 'variations2' ( see the Green square in the image)
http://i.imgur.com/JNbbh9x.png
So for the above, i want the correct SQL Query.
For the second SQL query, i already have it. It is to replace the text in variations2 that was just created.
But i'm just not sure how to put it together in the cron.php file.
SELECT meta_value, REPLACE(
REPLACE( meta_value,
'Black',
'Noir'
),
'Geen',
'Vert')
FROM wp_postmeta WHERE meta_key LIKE <strong>variations2</strong>;
So to finish it off, i want both those queries to run on a php file cron.php. This file already has <strong>another </strong>working function that just resets some post views every day. So i just want to add these two new ones to the list of queries to run.
The format of the cron.php is as follows:
<?php
$dbName = "xxxxxxxxxx" ;
$dbHost = "localhost" ;
$dbUser = "xxxxxxxxxxx" ;
$dbPass = "xxxxxxxxxxxxxxxx" ;
$dbh = new PDO ( 'mysql:dbname=' . $dbName . ';host=' . $dbHost, $dbUser, $dbPass ) ;
$query = "UPDATE `xxxxxxxxxxxxxxxxx`.`wp_post_views_realtime` SET `post_views_year` = '1' WHERE `wp_post_views_realtime`.`view_type` ='normal'" ;
$count = $dbh->exec($query) ;
// print $count if you want to see affected rows
?>
So please add the 2 new sql queries into this file.
Answers (1)
Arnav Joy answers:
you do not need to run your query in any other cron file , because using wordpress cron functions you can do that in your functions.php
if query is correct then you can use following code in functions.php to achieve what you want ,
<?php
add_action('my_daily_event', 'do_this_daily');
function my_activation() {
if ( !wp_next_scheduled( 'my_daily_event' ) ) {
wp_schedule_event( time(), 'daily', 'my_daily_event');
}
}
add_action('wp', 'my_activation');
function do_this_daily() {
global $wpdb;
$wpdb->query("SELECT meta_value, REPLACE(REPLACE( meta_value,'Black','Noir'),'Geen','Vert')FROM wp_postmeta WHERE meta_key LIKE variations2;");
}
?>
see here for more information
http://codex.wordpress.org/Function_Reference/wp_schedule_event
monitor comments:
OK, but I need the SQL command for the first task, to duplicate the post's 'variations' field into 'variations2' . And include that sql in the daily cron function. (A new post only has a 'variations' field in which i put colors, so this daily function will just make a second variations field 'variations2' with the same content. It saves me time to fill in two double fields everytime i make a post.
Arnav Joy comments:
I have a question , if you want to duplicate "variations" fields value to "variations2" then why you need a cron function for it?
what if , this values is saved at the same time when post is created means when ever you insert posts with "variations" field then at same time it will create a new field "variations" with the same content .
let me know what you think about it.
monitor comments:
Yes, that would be even better.
Arnav Joy comments:
ok , so how are you creating posts ?
monitor comments:
This is a quite long/complex function already as it is, but you can just add to it without altering or disturbing the rest of the code. http://pastebin.com/wr5cXMhZ
Arnav Joy comments:
try this
http://pastebin.com/GS6Z03BQ
monitor comments:
It works very good thank you.
This will work good on new published post. However, I now have about 1000 old posts that does not yet have a variations2 field. Could you please give me the SQL query that i can run one time in phpmyadmin that will duplicate variations into variations2 for all my <strong>old </strong>posts as well. Then this question would be concluded.
Arnav Joy comments:
call this function in any header.php or anywhere else in your template
<?php add_extra_meta(); ?>
and this function in functions.php
function add_extra_meta(){
global $wpdb;
$query = "SELECT DISTINCT post_id FROM wp_postmeta";
$row = $wpdb->get_col($query);
foreach($row as $r ){
$var1 = get_post_meta($r, 'variations',true);
if( !empty( $var1 ) ){
$var2 = get_post_meta($r, 'variations2',true);
if( empty( $var2 ) )
add_post_meta($r, 'variations2',$var1);
}
}
}