mysql commands in php from the server/cron

[freelance84]

 

Always humbling to be knocked on your arse by what you think would be so easy...

 

How can I run a std mysql_query command like this from cron?

$query_insert_new = mysql_query("INSERT INTO members (ID, name, date)VALUES   (NULL,'$new_name',CURDATE())");

 

It is a long time since I firstt learnt how to access mysql from the command line via pUTTY or cmd... However I never learnt how to write a php script in the same fashion...

I have configured cron to run correctly and the script runs. After testing however I was wondering how my database was not getting updated... then i realised I must have to enter the commands as if command line... I tried but don't know where to start.

 

Do I have to connect to the database in the same fashion you do in cli: mysql -u username -p password  ?

 

 

This is my script. The idea is to cycle every 7 days and write over old files. It is very simple, but all i need. The table has 3 fields, ID name date.

 

<?php
$db_username = "username";
$db_password = "password";
$db_hostname = 'localhost';
$db_database = 'databasename';

//first log into the database
$db_server = mysql_connect($db_hostname, $db_username, $db_password);

//selecting databse
mysql_select_db($db_database, $db_server);

//now get the latest entry in the table 'database_backup_info'
$query_latest_entry = mysql_query("SELECT name  FROM database_backup_info  ORDER BY ID	 DESC  LIMIT 1	");

//if there are results in the table
if(mysql_num_rows($query_latest_entry)>0)
	{
		//retrieving the info
		$query_latest_entry_row = mysql_fetch_assoc($query_latest_entry);
		//now determine the new file number based on the last entry
		if($query_latest_entry_row['name'] + 1 > 7)
			{
				$new_name = $query_latest_entry_row['name'] + 1;
			}
		else
			{
				$new_name = 1;
			}

		//insert the entry into the db
		$query_insert_new = mysql_query("INSERT INTO members (ID, name, date) VALUES   (NULL,'$new_name',CURDATE())");
	}

//if no results then this will be the first entry
else{
		$new_name = 1;
		//insert the entry into the db
		$query_insert_new = mysql_query("INSERT INTO members (ID, name, date) VALUES   (NULL,'$new_name',CURDATE())");
	}


//creating the file path and name
$db_backupFile= dirname(__FILE__).'/sql_files/'.$new_name.'.sql';

//the mysql dump command
$command = "mysqldump -u$db_username -p$db_password -h$db_hostname $db_database > $db_backupFile";

//running the mysql_dump command
system($command, $result);

?>

[freelance84]

 

Ok, i think i have found it. I have to create a php/shell script, i'm still not sure if the above should work yet, or how i should change the queries to make them work.

 

A couple of pages have come up:

http://www.cyberciti.biz/faq/using-mysql-in-shell-scripts/

http://www.sitepoint.com/introducing-cron/

 

[xyph]

Your code should run exactly the same as it would in a browser, with the exception of variables like $_SERVER, which are populated by the client/webserver.

 

Does your cron look something like

 

0 0 * * * /path/to/php5/bin/php /home/myuser/mydomain.com/myscript.php

 

?

[freelance84]

Hi xyph,

 

The crontab at the moment reads:

@daily php /var/www_private/db_backup/db_backup_gen.php

 

When i run the script with cron, it dumps the .sql database into the folder it is told too, but doesn't make any changes to the table. This is what lead me to think that maybe i had to connect differently.

 

(coincidentally, i just noticed the > is wrong and should be < when determining the current file number if there are results in the table)

 

 

I'll try changing the cron path to php to the php bin in the morning.

 

Thanks for the tip

 

 

[freelance84]

 

OK, all works now. Thanks for the tip

 

Change the crontab to :

@daily /usr/bin/php5  /var/www_private/db_backup/backup_generator.php  

 

 

Fixed the script:

<?php
$db_username = "username";
$db_password = "password";
$db_hostname = 'localhost';
$db_database = 'database_name';

//first log into the database
$db_server = mysql_connect($db_hostname, $db_username, $db_password);

//selecting databse
mysql_select_db($db_database, $db_server);

//now get the latest entry in the table 'database_backup_info'
$query_latest_entry = mysql_query("SELECT * 
								 FROM database_backup_info 
								 ORDER BY ID
								 DESC
								 LIMIT 1
								");

//if there are results in the table
if(mysql_num_rows($query_latest_entry)>0)
	{
		$query_latest_entry_row = mysql_fetch_assoc($query_latest_entry);
		//now determine the new file number based on the last entry
		if($query_latest_entry_row['name'] + 1 < 7)
			{
				$new_name = $query_latest_entry_row['name'] + 1;
			}
		else
			{
				$new_name = 1;
			}

		//insert the entry into the db
		$query_insert_new = mysql_query("INSERT INTO database_backup_info  (ID, name, date) VALUES   (NULL,'$new_name',CURDATE())"); 
	}
//if no results then this will be the first entry
else{
		$new_name = 1;
		//insert the entry into the db
		$query_insert_new = mysql_query("INSERT INTO database_backup_info  (ID, name, date) 	VALUES   (NULL,'$new_name',CURDATE())"); 
	}


$db_backupFile= dirname(__FILE__).'/sql_files/'.$new_name.'.sql';

$command = "mysqldump -u$db_username -p$db_password -h$db_hostname $db_database > $db_backupFile";

system($command, $result);

?>