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 1202103661 at 1202103661 ---------------

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

[VBDev]

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


$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.

[Marco van Herwaarden]

2 major reasons for such behaviour:
- Variable scope as Lynne mentioned
- Code that depends on the current user. (if you run with Run Now it will run under your admin account, if it gets ran automatic it can be triggered by any user/guest visiting your board)

[leenster]

i still cant figure it out..

my code is independent of vbulletin, i even made sure there is a connection to the database from my script, there are no globals in use as far as i know. my script could run standalone without vbulletin installed so its user independent.....

the complete code is listed above (first post in the thread).

any other ideas?

[Lynne]

i still cant figure it out..

my code is independent of vbulletin, i even made sure there is a connection to the database from my script, there are no globals in use as far as i know. my script could run standalone without vbulletin installed so its user independent.....

the complete code is listed above (first post in the thread).


any other ideas?
My cron job was totally independent of vb also - it was for my tracker. But, when I talk about the global problem, I'm talking about ones like this:

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

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

I was no longer able to declare something as global like that in my functions. I had to change all my code to pass the variable. So, in the example there, I had to pass $stack, not declare it as global like I had been before.

[leenster]

ah.....gotcha ... im gonna see what i can do

--------------- Added 1202587096 at 1202587096 ---------------

thanx for pointing me in the right direction guys....

the problem was with the globals :)