PHP/MySQL dump.

[daryll1]

I am attempting to dump a portion of my table after 7 days. This table will hold access logs so will fill up pretty quickly.
I'm not too sure if this is entirely possible but this is what I'm trying to use.
Any help or a push in the right direction would be amazing.

function log_dump() {
	$week = (date("Ymd") - 7); 
	
	$dbhost = 'hostname';
	$dbuser = 'username';
	$dbpass = 'pass';
	$dbname = 'db_name.table_name';
	
	$backupFile = $dbname . "." . date("Y-m-d") . '.sql';
	$command = "mysqldump --opt -h $dbhost -u $dbuser -p $dbpass $dbname --where=\"date_in='$week'\" > $backupFile";
	system($command);
	
}

Thanks,
Daryll.

[diafol]

Perhaps running a cron job every 7 days may be easier?

[daryll1]

Perhaps but it would still require me to get this dump right. I don't want to dump the whole database you see, just the day that was 7 days ago. I want the admin of the site to be able to look over the last 7 days of logs but nothing more.

[diafol]

<?php
$week = (date("Ymd") - 7); 
$bkfile = 'path/to/backup/folder/logs_' . date('Ymd') . '.sql';

$r  =  mysql_query("SELECT * INTO OUTFILE '$bkfile' FROM `table1` WHERE `date_in` >= $week");

?>

RUN this with cron job every 7 days.

[daryll1]

It has to be ran through PHP as I don't have access to their cron but I didn't think about SELECT INTO OUTFILE.
I should be able to adapt this to make it work.
Cheers.

[daryll1]

Just in case anyone else looks at this thread, I managed to sort it with these two functions.

function run_once($fu_name){
	$result_once = mysql_query("SELECT * FROM configuration WHERE function = '$fu_name'");
	$row_once = mysql_fetch_array( $result_once );
	$last = $row_once['last_run'];
	if($last == (date('Ymd') - 1)){
		print $fu_name();
	
	$date = date('Ymd');
	$y = "UPDATE configuration SET last_run = '$date' WHERE function = '$fu_name'";
	mysql_query($y);
	
	}
}


function log_dump() {
	$week = (date("Ymd") - 7);
	$bkfile = '../../logs/' . date('Y-m-d') . '.sql';
	$table = 'page_log';
	$r  =  "SELECT * INTO OUTFILE '$bkfile' FROM `$table` WHERE `date_in` = $week";
	mysql_query( $r ) or die(mysql_error()); ;
	
	$t = "DELETE FROM `$table` WHERE `date_in` = $week";
	mysql_query($t);
	
}

and calling it in the PHP using

run_once(log_dump);

this checks against a new table in my database which is set up as follows

function         |    last_run
------------------------------------
function name    |    date it was last run

[samanthakumara]

$command ="mysqldump --host=$host --user=$user_name --password=$password $database > $pathdump/$filename";
exec($command);

give 0kb file . what is the reson