Cron job problem..

[leenster]

Hey guys. im having a little problem with a cron job.. this cron job is supposed to get stats of our players and put it in a table, when i run this script manually by hitting the run now button in the scheduled task manager it works great. but when it is supposed to run every ten minutes it looks like only part of the script works.... it seems to stop where all entries get deleted from the table before all new data gets re-entered and i end up with an empty table...

any ideas?

<?php require_once('../../Connections/donations.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
  $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

// Delete existing Data
$deleteSQL = sprintf("DELETE FROM roster");
mysql_select_db($database_donations, $donations);
$Result1 = mysql_query($deleteSQL, $donations) or die(mysql_error());

// Get new data
$clanid="13778";

function startTag($parser, $name, $attrs) {
 global $stack;

 $tag=array("name"=>$name,"attrs"=>$attrs);
 array_push($stack,$tag);
}

function cdata($parser, $cdata) {
 global $stack;

 $stack[count($stack)-1]['cdata'] .= $cdata;  
}

function endTag($parser, $name) {
 global $stack;

 $stack[count($stack)-2]['children'][] = $stack[count($stack)-1];
 array_pop($stack);
}

// Parse XML

$stack = array();
$claninfo = array();
$clanstats = array();
$playerstats = array();

$xml_parser = xml_parser_create();
xml_set_element_handler($xml_parser, "startTag", "endTag");
xml_set_character_data_handler($xml_parser, "cdata");

$xmllink="http://aaotracker.com/livefeed/xml_clanprofile.php?clanid=$clanid";
$data = xml_parse($xml_parser,file_get_contents($xmllink));
if(!$data) die(sprintf("XML error: %s at line %d", xml_error_string(xml_get_error_code($xml_parser)), xml_get_current_line_number($xml_parser)));

xml_parser_free($xml_parser);

// Get Data

 // Get Clan Profile Data
 for($i = 0; $i < sizeof($stack[0][children][0][children]); $i++) {
  $valname=$stack[0][children][0][children][$i][name];
  $claninfo[$valname]=$stack[0][children][0][children][$i][cdata];
 }

 // Get Clan Stats Data
 for($i = 0; $i < sizeof($stack[0][children][1][children]); $i++) {
  $valname=$stack[0][children][1][children][$i][name];
  $clanstats[$valname]=$stack[0][children][1][children][$i][cdata];
 }

 // Get Player Data
 for($i = 0; $i < sizeof($stack[0][children][2][children]); $i++) {
  for($x = 0; $x < sizeof($stack[0][children][2][children][$i][children]); $x++) {
   $valname=$stack[0][children][2][children][$i][children][$x][name];
   $value=$stack[0][children][2][children][$i][children][$x][cdata];
   if($valname=="PLAYERID") $pid=$value;
   $playerstats[$pid][$valname]=$value;
  }
 }

// Put new Data in the Table
 
foreach($playerstats as $key => $value) {
 $playername=$playerstats[$key][PLAYERNAME];
 $playerhonor=$playerstats[$key][PLAYERHONOR];
 $playerurl=$playerstats[$key][PLAYERSTATSURL];
 $playertime=($playerstats[$key][PLAYERTIME]/60)/60;
 $playerscore=$playerstats[$key][PLAYERSCORE];
 $playergoalscore=$playerstats[$key][PLAYERGOALSCORE];
 $playerleaderscore=$playerstats[$key][PLAYERLEADERSCORE];
 $playerkills=$playerstats[$key][PLAYERKILLS];
 $playerdeaths=$playerstats[$key][PLAYERDEATHS];
 $playerfragrate= @round($playerstats[$key][PLAYERKILLS]/$playerstats[$key][PLAYERDEATHS], 2);
 $playerstatus=$playerstats[$key][PLAYERSTATUS];
  
 $query= "INSERT INTO roster SET playername='$playername', playerhonor='$playerhonor', playerurl='$playerurl',
playertime='$playertime', playerscore='$playerscore', playergoalscore='$playergoalscore', playerleaderscore='$playerleaderscore', playerkills='$playerkills', playerdeaths='4playerdeaths', playerfragrate='$playerfragrate', playerstatus='$playerstatus'";
  
  mysql_select_db($database_donations, $donations);
  $Result2 = mysql_query($query, $donations) or die(mysql_error());
}
?>

[petteyg359]

Not sure why the cron wouldn't like it, but deleting everything before updating seems rather inefficient. Try using 'TRUNCATE TABLE roster' instead. Much friendlier to resources.

[leenster]

its not updating, it is inserting. i did this to make sure any new members would get added too. i figured Updating would only update existing entries and not any new ones.

[petteyg359]

In that case, assuming that every user has a unique ID... This is the code I use for inserting MMO characters of registered users into a database of mine. $nummac is set before this, after reading login information from the vB database. $charid is an array of all the unique character IDs. Would obviously have to be modified a bit to work in your script. You would then use 'UPDATE blahtable SET blahattribute = "blue" WHERE blahusername = "IAMBLAH"' to update userdata.

$charinsqa = 'INSERT INTO userskills (characterid) VALUES ';
for ($i = 0; $i < $nummac; $i++) {
 $existquerya = 'SELECT characterid FROM userskills WHERE characterid = "'.$charid[$i].'"';
 $existresulta = mysql_query($existquerya);
 $existrowa = mysql_num_rows($existresulta);
 mysql_free_result($existresulta);
 if (!$existrowa) {
  $charinsqab = $charinsqa.'("'.$charid[$i].'")';
  mysql_query($charinsqab) or die('Insert failed: '.mysql_error());
 }
}

unset($charinsqa);
unset($charinsqab);

My code:
Doesn't perform any INSERTs unless new users are found.
Old inactive users must be removed from the table manually.

Your code:
Performs many INSERTs every time.
Removes old inactive users by rewriting the entire table every time.

If you continue to use your code, that delete statement should still be changed to 'TRUNCATE TABLE tablename'. TRUNCATE is rather more efficient than DELETEing every row.

[leenster]

changed it to TRUNCATE TABLE, im still getting the same results

[petteyg359]

Well, yeah, I did say I'm not sure why the cron part isn't working. You should still use TRUNCATE, though, simply for efficiency. Is there a reason for having "?><?php" first/second line?

[leenster]

Thanx for the truncate tip. no reason for the double <php tags either, i must have missed that when cleaning up the code...

--------------- Added [DATE]1202103661[/DATE] at [TIME]1202103661[/TIME] ---------------

It does about 75 inserts, would UPDATE be much faster?

[VBDev]

To diminish queries, you could perhaps try it like that :

PHP Code:

$charinsqa = 'INSERT INTO userskills (characterid) VALUES ';
$insert = '';
/* Check if users exist */
$existquerya  = 'SELECT characterid FROM userskills WHERE characterid IN(' . implode(',',$charid). ')';
$existresulta  = mysql_query($existresulta); 
/* Removing elements from $charid if retrieved from database */
if (mysql_num_rows($existresulta))
{
 /* Fetch request result */
 while($row = mysql_fetch_array($existresulta))
 {
  /* Search if element is in $charid */
  $position = array_search($row['characterid'],$charid);
  
  /* Found */
  if ($position)
  {
   unset($charid["$position"]);
  }
 }
}
/* At this point $charid contains only new values or is empty, let's build the insert */
if ($charid)
{
 for ($i = 0; $i < sizeof($charid); $i++)
 {
  /* Coma needed only after the first value */
  if ($i != 0)
  {
   $insert .= ',';
  }
  
  $insert .= '("'.$charid[$i].'")'; 
 }
 
 /* Let's insert new values in one query into the database */
 $charinsqab = $charinsqa . $insert;
 mysql_query($charinsqab) or die('Insert failed: '.mysql_error()); 
}

unset($charinsqa);
unset($charinsqab); 


I have not tested it and not sure there are no errors, but with this code you would have only one select and one insert

[leenster]

not sure how to apply that to my code though......

my question is still:

why does it work when i press the run now button from the "scheduled task manager" and why do it not work when the cron job executes......

im puzzled..

it still does the same after i removed the delete code and insert was replaced with update....

[Lynne]

I had a similar problem on my site with a custom cron job I wrote. It would run fine if I hit the Run button in the Admin CP, but it wouldn't run on it's own as a scheduled task. It was something to do with no longer being able to define any variable as global - I had to actually make sure every variable I used in my function was passed. I'm not sure if that is a problem at all with your functions. I don't remember if this happened after my server move or after my upgrade to 3.6.8.