I have a website that auto generates odds data to mysql table via cron job every 2hrs. Over time the data in my table is exceeding the limit set from my hosting of 100mb.
I want to stop my table from overloading and to do this, I want it to delete previous odds data and only show the latest odds. The odds data is in the wp_postmeta table.
Here is a sample of data in one row:
- meta_id 3141189
- post_id 20583
- meta_key
Odd - meta_value
{"FixtureMatch_Id":"392665","Bookmaker":"18BET","UpdatedDate":"2018-12-14T20:57:46.98","Type":"Asian Handicap","HomeOdds":"1.49","AwayOdds":"2.71","Handicap":"2"}
So I have lots of these rows, and I want to know how I can use PHP to remove old odds, lets say odds that are before 5 days are deleted, how would I do this?
I have tried to do this, but heard it doesn't work like that.
global $wpdb; // Global object database variable
$updatedDate = [];
//set how many days odds will show until.. currently set to -5 days.
$endDatefind = (new \DateTime('now'))->modify("-5 day")->format('Y-m-d'); // creating datetime object
//finds all dates from 2000 to 5 days before todays date
$findquery = $wpdb->get_results ( "
SELECT *
FROM `wp_postmeta`
WHERE`meta_key` = 'Odd'
AND STR_TO_DATE(meta_value, '%Y-%m-%d') >= '2000-01-01'
AND STR_TO_DATE(meta_value, '%Y-%m-%d') =< '$endDatefind'
");
Anyone know how I can do it?