random background with mysql instead of jquery

2019-08-21 11:35发布

问题:

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.