PHP script won't update MySQL tables

[jimvy]

The script I need to get working should upload 3 data files, update 3 table in the database with the new information. Then it should compare the registered forum users with the updated tables and return the names of any members who are are no longer in the tribe.

When I try to run this script in my browser I get a blank white page, so I have no clue as to why it won't work. The tables are made and empty. Can someone tell me what I am not doing right?

I used this code to create the tables:

Koodi [Valitse] Laajenna



CREATE TABLE `village` (
  `id` int(11) NOT NULL default '0',
  `name` varchar(255) default NULL,
  `x` int(5) default NULL,
  `y` int(5) default NULL,
  `player_id` int(11) default NULL,
  `points` int(5) default NULL,
  `rank` int(11) default NULL,
  PRIMARY KEY  (`id`),
  KEY `player_id` (`player_id`),
  KEY `x` (`x`,`y`),
  KEY `y` (`y`,`x`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


CREATE TABLE `player` (
  `id` int(11) NOT NULL default '0',
  `name` varchar(255) default NULL,
  `tribe_id` int(11) default NULL,
  `villages` int(7) default NULL,
  `points` int(11) default NULL,
  `rank` int(11) default NULL,
  PRIMARY KEY  (`id`),
  KEY `name` (`name`),
  KEY `tribe_id` (`tribe_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `tribe` (
  `id` int(11) NOT NULL default '0',
  `name` varchar(255) default NULL,
  `tag` varchar(60) default NULL,
  `members` int(5) default NULL,
  `villages` int(9) default NULL,
  `points` int(11) default NULL,
  `all_points` int(11) default NULL,
  `rank` int(7) default NULL,
  PRIMARY KEY  (`id`),
  KEY `name` (`name`),
  KEY `tag` (`tag`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1


Here's the script I'm trying to run.

Koodi [Valitse] Laajenna


<?php

set_time_limit(600);

$host = "localhost";
$user = "twfamily_update";
$pass = "";
$db = "twfamily_tw13data";

if (!$mysql_connection = mysql_connect("$host", $user, $pass))
    die("Could not connect to MySQL database: ".mysql_error());
else
    echo "Connected successfully!<br />";

//upload twdata files
$village_info = "http://en13.tribalwars.net/map/village.txt.gz";
$player_info  = "http://en13.tribalwars.net/map/tribe.txt.gz";
$ally_info  = "http://en13.tribalwars.net/map/ally.txt.gz";

mysql_query("USE $database");

//replace old player data
mysql_query("DELETE FROM $prefix"."player");
$lines = gzfile($player_info);
if (!is_array($lines))
    die("Could not open player info file."); 
foreach($lines as $line) {
    list ($id, $name, $ally, $villages, $points, $rank) = explode(',', $line);
    $name = prepare($name);
    mysql_query("INSERT INTO $prefix"."player VALUES ($id, '$name', $ally, $villages, $points, $rank)");
}
echo "Inserted player info.<br />";

//replace old ally data
mysql_query("DELETE FROM $prefix"."ally");
$lines = gzfile($ally_info);
if (!is_array($lines))
    die("Could not open ally info file."); 
foreach($lines as $line) {
    list ($id, $name, $tag, $members, $villages, $points, $all_points, $rank) = explode(',', $line);
    $name = prepare($name);
    $tag  = prepare($tag);
    mysql_query("INSERT INTO $prefix"."ally VALUES ($id, '$name', '$tag', $members, $villages, $points, $all_points, $rank)");
}
echo "Inserted ally info.<br />";

//replace old village data
mysql_query("DELETE FROM $prefix"."village");
$lines = gzfile($village_info, "r");
if (!is_array($lines))
    die("Could not open village info file."); 
foreach($lines as $line) {
    list ($id, $name, $x, $y, $player_id, $points, $rank) = explode(',', $line);
    $name = prepare($name);
    if (!mysql_query("INSERT INTO $prefix"."village VALUES ($id, '$name', $x, $y, $player_id, $points, $rank)")) {
            echo mysql_error();
            break;
    }
}
echo "Inserted village info.<br />";

//check forum users for tribe membership
$members = mysql_query("SELECT memberName FROM smf_members");
while ($member = mysql_fetch_row($members)) {
    mprint_r($member);
    $member_stat = mysql_query("SELECT ally, name FROM $prefix"."player WHERE name = '$member[0]'");
    $member_info = mysql_fetch_row($member_stat);
    if (!$member_info[1]) {
        echo "** <b>$member[0]</b> is not an Aggression Family Member.<br />";
        continue;
    }
    mprint_r($ally_id);
    $ally = mysql_query("SELECT name FROM $prefix"."ally WHERE id = '$member_info[0]'");
    $ally_name = mysql_fetch_row($ally);
    mprint_r($ally_name);
    if (!$ally_name[0]) {
        echo "*<b>$member[0]</b> is not in any tribe<br />";
        continue;
    }
    if (($ally_name[0] != "R@") && ($ally_name[0] != "D@") && ($ally_name[0] != "R@A") && ($ally_name[0] != "D@A"))
        echo "<b>$member[0]</b> is in $ally_name[0]<br />";
}

function prepare($string) {
    return addslashes(urldecode($string));
}

function mprint_r($array) {
    echo "<pre>";   
    print_r($array);
    echo "</pre>";   
}
?>


[kryptmachines]

At the beginning of the code, I see $db as the variable name but when actually using the database name, you're using: '$database' .

Replace:

Koodi [Valitse] Laajenna


mysql_query("USE $database");


to:

Koodi [Valitse] Laajenna


mysql_query("USE $db");


It should now use the database name accordingly.

I also noticed you're using '$prefix' variable but its source is nor indicated anywhere in the code ... is it a global variable from another PHP file ? If so, there doesn't seem to be any indications of any inclusions in the code.

[jimvy]

This code was written by a friend on a site we used a year ago. This is the only code, the prefix part isn't from a global code.

I also realized that the database structure is different. So I'll try to give you all the details of this and maybe that will help more.

The database is called twfamily_smf1. There are 3 tables I added called tw_player, tw_tribe and  tw_village.  Right now the tables have nothing in them since I can't get the code to run and I also can't seem to import the data in phpmyadmin (it imports the first line and stops). It shows in MySQL Account Maintenance twfamily_update is a user in the twfamily_smf1 and has all privileges. The file is called update.php and is saved in /public_html/Aggression/update. When I try to run the file I put http://aggression.tw-family.us/update/update.php into the address bar and get nothing at all.

I have made changes to the file in an attempt to get it to work, but have had no success so far. Here's what I have as of now:

Koodi [Valitse] Laajenna


<?php

set_time_limit(600);

$host = "localhost";
$username = "twfamily_update";
$password = "XXXXX";
$database = "twfamily_smf1";
$prefix = "tw_";

if (!$mysql_connection = mysql_connect($host, $username, $password))
    die("Could not connect to MySQL database: ".mysql_error());
else
    echo "Connected successfully!<br />";

$village_info = "http://en13.tribalwars.net/map/village.txt.gz";
$player_info  = "http://en13.tribalwars.net/map/tribe.txt.gz";
$ally_info    = "http://en13.tribalwars.net/map/ally.txt.gz";

mysql_query("USE $database");

mysql_query("DELETE FROM $prefix"."player");
$lines = gzfile($player_info);
if (!is_array($lines))
    die("Could not open player info file."); 
foreach($lines as $line) {
    list ($id, $name, $ally, $villages, $points, $rank) = explode(',', $line);
    $name = prepare($name);
    mysql_query("INSERT INTO $prefix"."player VALUES ($id, '$name', $ally, $villages, $points, $rank)");
}
echo "Inserted player info.<br />";

mysql_query("DELETE FROM $prefix"."ally");
$lines = gzfile($ally_info);
if (!is_array($lines))
    die("Could not open ally info file."); 
foreach($lines as $line) {
    list ($id, $name, $tag, $members, $villages, $points, $all_points, $rank) = explode(',', $line);
    $name = prepare($name);
    $tag  = prepare($tag);
    mysql_query("INSERT INTO $prefix"."ally VALUES ($id, '$name', '$tag', $members, $villages, $points, $all_points, $rank)");
}
echo "Inserted ally info.<br />";

mysql_query("DELETE FROM $prefix"."village");
$lines = gzfile($village_info, "r");
if (!is_array($lines))
    die("Could not open village info file."); 
foreach($lines as $line) {
    list ($id, $name, $x, $y, $player_id, $points, $rank) = explode(',', $line);
    $name = prepare($name);
    if (!mysql_query("INSERT INTO $prefix"."village VALUES ($id, '$name', $x, $y, $player_id, $points, $rank)")) {
            echo mysql_error();
            break;
    }
}
echo "Inserted village info.<br />";

$members = mysql_query("SELECT memberName FROM smf_members");
while ($member = mysql_fetch_row($members)) {
    mprint_r($member);
    $member_stat = mysql_query("SELECT ally, name FROM $prefix"."player WHERE name = '$member[0]'");
    $member_info = mysql_fetch_row($member_stat);
    if (!$member_info[1]) {
        echo "** <b>$member[0]</b> is not an Aggression Family Member.<br />";
        continue;
    }
    mprint_r($ally_id);
    $ally = mysql_query("SELECT name FROM $prefix"."ally WHERE id = '$member_info[0]'");
    $ally_name = mysql_fetch_row($ally);
    mprint_r($ally_name);
    if (!$ally_name[0]) {
        echo "*<b>$member[0]</b> is not in any tribe<br />";
        continue;
    }
    if (($ally_name[0] != "R@") && ($ally_name[0] != "D@") && ($ally_name[0] != "R@A") && ($ally_name[0] != "D@A"))
        echo "<b>$member[0]</b> is in $ally_name[0]<br />";
}

function prepare($string) {
    return addslashes(urldecode($string));
}

function mprint_r($array) {
    echo "<pre>";   
    print_r($array);
    echo "</pre>";   
}
?>


If you need any additional information I'll be happy to give it to you. I have taken some computer programming classes so I have a general idea of how it works but have not taken anything on php or mysql.

[kryptmachines]

Check this topic:

hxxp:ca.php.net/manual/en/function.fgetcsv.php#56968 [nonactive]

it has helped me a couple of times before for extracting each CSV files with each CSV lines and code out some specific actions on these events once they were extracted.

I also just found out about this function:

hxxp:ca.php.net/manual/en/function.fscanf.php [nonactive]