Delete records over 24hours old

What you are trying to do requires an action by a user. It is better to delete or archive using a scheduled job in the database. This way each night, your table will remove older records.

I am still trying to set up on how you said before. I have the archive set up after the record is deleted and if I click on delete button it does send it to the archive table. Problem is. The way I had it set up where it is filtered in that last post does not send the record to the archived table. Now I am trying to set up the auto delete and still be able to send that record to the archive table.
If I set up a scheduled job in mysql database will that still send the record to the archive table?
Thanks again Jadach for your help.

James

I do not use MySQL, I use MSSQL Server. There I create a stored procedure to move old records from one table to another based on a datetime field. I then schedule that job to run each night.
I am sure there must be some equivalent in MySQL. I think it is MySQL event scheduler.

select ... from ... where DateDiff(now(), DateField)<1
MySQL query for inspiration purposes only ..., assuming that

you don't want to display any thing elder than 24 hours but

won't mind as well to keep elder records still in place.

+1 to what @walk2fly suggests. Deleting records while you only need to hide them is an overkill. Make your query return records for last 24 hours only and if you need physically delete them this is a separate task that cannot be solved with the help of PHPRunner. You will need to write a simple PHP script that deletes data and schedule it using Windows scheduler or a cron job.

@Tandy Services - https://xlinesoft.com/phprunner/docs/query_designer.htm'>https://xlinesoft.com/phprunner/docs/query_designer.htm

... drill down to the bottom to see where the code has to be injected

@jadach - https://xlinesoft.com/phprunner/docs/query_designer.htm'>https://xlinesoft.com/phprunner/docs/query_designer.htm

For MS SQL users under "per group" session related "record hiding".

@admin - https://xlinesoft.com/phprunner/docs/customquery.htm

... in order NOT to forget DAL abilities to be mentioned here too ...

I really do not what to delete the record. I like the hide record as in the other post to replied back to me at. Problem is, If I only hide the post it does not get transferred over to my archive table. I am still trying to figure out how to have the record in the archive table before it is hidden. Was trying to have the record transferred there at the same time the record was made then it can be hidden only like it is now. But the event of transfer before delete is the only one that works for me right now.

Then do both ; Forget about delete. You can delete the archive at your convenience.
After Record Added
What you are asking to do is not show the record after you add it but archive to another table , _does not make much sense to me as after you add it , it won't show on your list page_because it is today. It will only get archived and show there. but if that's what you want see below.
global $dal;
$tbltrucks_archive = $dal->Table("trucks_archive");

$tbltrucks_archive->Value["carrier_id"]=$values["carrier_id"];

$tbltrucks_archive->Value["account_id"]=$values["account_id"];

$tbltrucks_archive->Value["submit_date"]=now("%m-%d-%Y");

$tbltrucks_archive->Value["carrier_name"]=$values["carrier_name"];

$tbltrucks_archive->Value["available_date"]=$values["available_date"];

$tbltrucks_archive->Value["origin_country"]=$values["origin_country"];

$tbltrucks_archive->Value["origin_state"]=$values["origin_state"];

$tbltrucks_archive->Value["origin_city"]=$values["origin_city"];

$tbltrucks_archive->Value["trailer_type"]=$values["trailer_type"];

$tbltrucks_archive->Value["full_partial"]=$values["full_partial"];

$tbltrucks_archive->Value["destination_country"]=$values["destination_country"];

$tbltrucks_archive->Value["destination_state"]=$values["destination_state"];

$tbltrucks_archive->Value["destination_city"]=$values["destination_city"];

$tbltrucks_archive->Add();
Then in Your Main Table Trucks or whatever tablename it is . Go to query and add where clause at end of query

Select

all your fields like it is listed in query then add after from Trucks.

From Trucks

WHERE submit_date < DATE_SUB(NOW(),INTERVAL 1 DAY) // mysql
This is it . You must have two tables ex... TrucksArchive and Trucks which would be main table that you work with. YOU have to realize that after add it will not show on list page because you have changed your query to 24 hours ago. Don't make much sense._
This will show before the last 24 hours from submit date and your archive table will show all records ever added. You can always query the archive table too. with the same query for submit_date. I would just filter it with past month , day , year etc..... on properties tab for submitdate filter as INTERVAL LIST_ so you can see any dated records.

Thanks John, I will give that a shot. But I want it to show in trucks for 24 to 48 hours. Then be hidden off trucks and only show in the truck_archive after that. I am trying to add a button in the truck_archive after that so the poster can re-list the post if need be for another 24 to 48 hours.

The only reason I want it to auto hide or delete is that if the load or truck gets moved and the poster does not come back to remove it. Then it does not stay on the list page for more then I am thinking 24 hours but might make it 48 hours, Still in the thinking part of that one. If the truck or load has not moved in that time frame. They can go into the archive and re-list it.

If I can get it to post in both spots at the same time. Then I will have to replace the delete button with a hide button that says archive or something along those lines.

WHERE submit_date < DATESUB(NOW(),INTERVAL 2 DAY_)
Just do the reverse in archive - after record deleted use deleted_values to add it back to trucks. This is really not a good structure for what you are trying to do. In Archive where delete button is go into html mode and change the name to re-post for the button delete. What you are doing here is just sending records back and forth between tables .
$tbltrucks_archive = $dal->Table("trucks");

$tbltrucks_archive->Value["carrier_id"]=$delted_values["carrier_id"];

$tbltrucks_archive->Value["account_id"]=$delted_values["account_id"];

$tbltrucks_archive->Value["submit_date"]=now("%m-%d-%Y"); // notice this line starts again today

$tbltrucks_archive->Value["carrier_name"]=$delted_values["carrier_name"];

$tbltrucks_archive->Value["available_date"]=$delted_values["available_date"];

$tbltrucks_archive->Value["origin_country"]=$delted_values["origin_country"];

$tbltrucks_archive->Value["origin_state"]=$delted_values["origin_state"];

$tbltrucks_archive->Value["origin_city"]=$delted_values["origin_city"];

$tbltrucks_archive->Value["trailer_type"]=$delted_values["trailer_type"];

$tbltrucks_archive->Value["full_partial"]=$delted_values["full_partial"];

$tbltrucks_archive->Value["destination_country"]=$delted_values["destination_country"];

$tbltrucks_archive->Value["destination_state"]=$delted_values["destination_state"];

$tbltrucks_archive->Value["destination_city"]=$delted_values["destination_city"];

$tbltrucks_archive->Add();
This all can be avoided by using function BeforeProcessRowList($data, $pageObject)
something like :
$date=Now();

if($data["submit_date"] < date_sub($date,date_interval_create_from_date_string("2 days"))) {

{

return true;

}

}else

{

return false;

}

Contact support if you need help on this
A couple lines of code can avoid this whole process by hiding records.

Thank you again John.

I do not want it to delete from the archive table at all.. That is why I was thinking a new button added to the truck_archive table. But that is for a different post if I can not figure out how to add the button and open up the add page.
Just so there is a better understanding of what I am trying to do is at Freight.Tools website I am making up different kinds of tools for the transportation industry(Trucking). What I am working on here is what they call a load board. Shippers post there freight that trucks are looking for in that area. The trucks also post that they are empty in whatever area there in or looking for a load at.
So the reason behind the auto hide is the truck might find the load and forget to archive there listing. Same as the shipper might forget to archive there load.
I have solved the moving records to the archive. The thing I am trying to figure out is how to change your code to not hide the post for 24 hours but hide the post after being listed for 24 hours.

WHERE (submit_date > DATE_SUB(CURDATE(),INTERVAL 1 DAY))

This code hides the record for one day then lists the record. I need it the other way around. which the admin solved in my other post.
So basically this problem is solved with your help John and the help of the admin on the other post.
Thank you all for your advice and help on teaching me this.