random background with mysql instead of jquery

2019-08-21 11:42发布

First of all, I know that I can make this through jquery, but the purpose of my answer is to overcome the default behavior of my theme/page builder at the database level, so take my request as a simple attempt, cause I'm not good with sql.

I just want to make a query that randomize the background of a particular wordpress post with ID = 222102. The background path is declared into the table wp_posts at the column post_content in this way:

background_video_mp4="/wp-content/uploads/media/background.mp4"

obviously this is only a part of the entire post_content, not the entire record.

let's say that I have a list of backgrounds like this one:

/wp-content/uploads/media/background-1.mp4
/wp-content/uploads/media/background-2.mp4
/wp-content/uploads/media/background-3.mp4

I know that I can make this query to replace the background, but I'm trying to understand how to randomize this query from the list above.

update wp_posts set post_content =
replace(post_content,
'background_video_mp4="/wp-content/uploads/media/background.mp4"',
'background_video_mp4="/wp-content/uploads/media/background-1.mp4"');

then I want to add this query into my functions.php so it will run on every page request. many thanks

UPDATE: I wrote this code into my functions.php and is working good

//mysql query for random home background video from db
function random_mp4_background() {
if ( is_front_page() && !wp_is_mobile() ) {
global $wpdb;
$wpdb->query(
    "
    UPDATE $wpdb->posts
    SET post_content = REGEXP_REPLACE(post_content, 
'background_video_mp4=\"\/wp-content/uploads/media/background(.*?)mp4', 
ELT(FLOOR(1 + (RAND() * (3-1))), 'background_video_mp4=\"\/wp- 
content/uploads/media/background-1.mp4', 
'background_video_mp4=\"\/wp-content/uploads/media/background-2.mp4'))
    WHERE ID = 222102
    "
);
}
$wpdb->flush();
}
add_action( 'wp_head', 'random_mp4_background');

Unfortunately if you want to cache your page with a caching plugin, this solution in comparision with a jquery one, is not a good choice, cause you will always view the same background even if the query is correctly executed.. you have to clear manualy the cache or wait until is flushed.

0条回答
登录 后发表回答