PHP/SQL problem

[mdswish]

I need to be able to run two sql scripts nightly on a database for DayZ. One is to clean up destroyed vehicles and the other is to refresh trader inventories. But, due to NFO's setup on the SQL server, my database user account does not have sufficient privileges in order to set up a recurring event, and the needed privileges cannot be given. I have been told by NFO support that it should be possible to set up a CRONtab job which should run a PHP script which will run a SQL script. The only problem with that is that the PHP file is still dependent upon the login credentials of the defined user account, which doesn't solve my problem. I know we have some skilled folks here, so I'm hoping there is somebody who can help me figure out how to execute an SQL script in PHP without having to log into the database with superadmin rights. Am I overthinking this??

Here is an example of the PHP script I found:

Code: Select all

<?php
$sqlFileToExecute = 'sqlscript.sql';
$hostname = 'db.hostname.net';
$db_user = 'dbuser';
$db_password = 'dbpassword';
$link = mysql_connect($hostname, $db_user, $db_password);
if (!$link) {
  die ("MySQL Connection error");
}

$database_name = 'databasename';
mysql_select_db($database_name, $link) or die ("Wrong MySQL Database");

// read the sql file
$f = fopen($sqlFileToExecute,"r+");
$sqlFile = fread($f, filesize($sqlFileToExecute));
$sqlArray = explode(';',$sqlFile);
foreach ($sqlArray as $stmt) {
  if (strlen($stmt)>3 && substr(ltrim($stmt),0,2)!='/*') {
    $result = mysql_query($stmt);
    if (!$result) {
      $sqlErrorCode = mysql_errno();
      $sqlErrorText = mysql_error();
      $sqlStmt = $stmt;
      break;
    }
  }
}
if ($sqlErrorCode == 0) {
  echo "Script is executed succesfully!";
} else {
  echo "An error occured during installation!<br/>";
  echo "Error code: $sqlErrorCode<br/>";
  echo "Error text: $sqlErrorText<br/>";
  echo "Statement:<br/> $sqlStmt<br/>";
}

?>

[Edge100x]

You could run an .sql file using PHP, but there's no real reason to do that, when you can just import it through the MySQL command-line client instead. You would use a command like:

Code: Select all

mysql --user=youruser --password=yourpassword --database=yourdatabase < yourfile.sql

You can easily make this happen occur on a regular basis through a crontab entry -- but make sure that it works as you want it to by executing it directly through SSH beforehand.

[mdswish]

I don't know how to do that. Beyond my skill set. Don't know anything about Linux/Unix. Where is the best way for me to see if the sql script is actually being run through the php file. I've got that set up already but I can't tell if it ran last night.

[Edge100x]

The solution I gave is simpler and faster than trying to use PHP and will give immediate output. Doing it is as simple as logging in through SSH and running the command, so it's not beyond your skill set .

If you attempt to run your PHP script through a web browser, it may give some output, but I don't recommend continuing down that path.