Requesting a little help sorting my thoughts

[gmacleod]

I am currently stumped on a particular feature I am trying to implement into a database updating function.

This is the flow of the current application:

Check to see if a valid action has been requested (works)
  If no action specified, build table from database information (works)
  If invalid action requested; throw an error code and exit application (works)
  If valid action requested; execute function (works)
    Connect to external website and generate a query, resultant information (Unserialized XML data) is used to create a multi dimensional array. (works)
    Count the number of entries (sub arrays) and loop for each one to define the data into variables for database entry (works)
      If valid action requested is install; populate table (works)
      If valid action requested is update; update information currently in database (works)

Here is where I wish to add the feature. The XML data supplied by the query changes based on when the database it is pulled from is updated. Usernames and related information are automatically updated, and are reflected when the array is built.

What I want to do is, compare the information from the array to what is in the table.

Scenarios:

User entries in the Local database are not in the array (Ie, username deleted) -- delete entry from local database
User entries in the array are not in the local database (Ie, new username added to remote database) -- insert values
User entries exist in both the Local database and the remote database, but other values have changed -- update changed values.

I know of INSERT INTO, and DELETE FROM, but its the actual logic in comparing the two datasets that I am stuck on.

If there is any information you would require such as the Table schema, or the structure of the actual routines in the code, I will be happy to supply links to the source code.

Thank you in advance,

Gabrial MacLeod

[sneakyimp]

gmacleod wrote:

User entries in the Local database are not in the array

if your array is like this:

$arr = array('alice', 'bob', 'carol');

then you can do something like this

$escaped = array();
foreach($arr as $value) {
  $escaped[] = "'" . mysql_real_escape_string($value) . "'";
}
$sql = "DELETE FROM someTable WHERE someField NOT IN (" . implode(',', $escaped) . ")";
mysql_query($sql)
  or die('delete query failed');

gmacleod wrote:

User entries in the array are not in the local database (Ie, new username added to remote database) -- insert values

You have to find out which items in your array are not in the database. Similar to the problem above:

$escaped = array();
foreach($arr as $value) {
  $escaped[] = "'" . mysql_real_escape_string($value) . "'";
}
// find names already in db
$sql = "SELECT someField FROM someTable WHERE someField IN (" . implode(',', $escaped) . ")";
$res = mysql_query($sql)
  or die('select query failed');
$existing_names = array();
while ($row = mysql_fetch_assoc($res) {
  $existing_names[] = $row['fieldName'];
}

foreach($arr as $value) {
  if (!in_array($value, $existing_names)) {
    $sql = "INSERT INTO someTable (someField) VALUES '" . mysql_real_escape_string($value) . "'";
  }
}

Not sure about that last one...

[gmacleod]

Thanks for your reply 🙂

Here is the source for the script from A to Z oh and I should have noted that the 3rd scenario works as is.

<?php
header('Content-Type: text/html; charset=utf-8');
include("phpArmory.class.php");
include("dbconnect.php");
$site = "zero";
$f = $_GET['f'];

if (!isset($f)) {
   buildRoster($site);
} elseif ($f == "populate" || $f == "update") {
   populateDB($f, $site);
} else {
   Echo "Invalid error numbnuts, try again!";
}

function populateDB($f, $site) { // Begin Populate Database Function

dbconnect($site);
$check_sql = mysql_query("SELECT * FROM roster");
$check = mysql_num_rows($check_sql); // counts the rows in the table

if ($check > 0 && $f == "populate") {
	echo "You have already populated the database numbnuts. Try updating!";
} elseif ($check == 0 && $f == "update") {
	echo "Nothing in the database numbnuts. Try populating first!";
// } elseif (($check > 0 && $f == "update") || ($check == 0 && $f = "populate")) {

} else {

$realm = "Suramar"; $guild = "Crucifiction";
$armory = new phpArmory();
$armory->setlang('en');

$guildinfo = $armory->guildFetch($guild, $realm);
$guildinfo = $guildinfo['guildinfo'];

$members = $guildinfo['guild']['members']['character'];
$count = count($members);

$array = array();

for ($i = 0; $i < $count; $i++) { // Begin Roster Generation For Loop

    $memberID = $guildinfo['guild']['members']['character'][$i];
    $rankID = $guildinfo['guild']['members']['character'][$i]['rank'];

    switch ($rankID) // Begin Rank Title Switch
    {
    case 0:
      $rank = "Guild Master";
      break;
    case 1:
      $rank = "GM Alts";
      break;
    case 2:
      $rank = "Officer";
      break;
    case 3:
      $rank = "Officer Alts";
      break;
    case 4:
      $rank = "Veteran";
      break;
    case 5:
      $rank = "Veteran Alts";
      break;
    case 6:
      $rank = "Member";
      break;
    case 7:
      $rank = "Member Alts";
      break;
    case 8:
      $rank = "Initiate";
      break;
    } // End Rank Title Switch

    $character = $memberID['name'];

    $charInfo = $armory->characterFetch($character, $realm);
    $charInfo = $charInfo['characterinfo'];
    $tree = $charInfo['charactertab']['talentspec'];
    $classID = $memberID['classid'];

	switch ($classID) // Begin Talent Tree Switch
		{
		// Warrior
		case 1: switch(max($tree['treeone'],$tree['treetwo'],$tree['treethree']))
		    { 
			case $tree['treeone']: $spec = "Arms"; $img = 1; break;
			case $tree['treetwo']: $spec = "Fury"; $img = 2; break;
			case $tree['threethree']: $spec = "Protection"; $img = 3; break;
		    }
		break;

		// Paladin
		case 2: switch(max($tree['treeone'],$tree['treetwo'],$tree['treethree']))
		    { 
			case $tree['treeone']: $spec = "Holy"; $img = 1; break;
			case $tree['treetwo']: $spec = "Protection"; $img = 2; break;
			case $tree['treethree']: $spec = "Retribution"; $img = 3; break;
		    }
		break;

		// Hunter
		case 3: switch(max($tree['treeone'],$tree['treetwo'],$tree['treethree']))
		    {
			case $tree['treeone']: $spec = "Beast Mastery"; $img = 1; break;
			case $tree['treetwo']: $spec = "Marksmanship";  $img = 2; break;
			case $tree['treethree']: $spec = "Survival"; $img = 3; break;
		    }
		break;

		//Rogue
		case 4: switch(max($tree['treeone'],$tree['treetwo'],$tree['treethree']))
		    {
			case $tree['treeone']: $spec = "Assassination"; $img = 1; break;
			case $tree['treetwo']: $spec = "Combat"; $img = 2; break;
			case $tree['treethree']: $spec = "Subtlety"; $img = 3; break;
		    }
		break;

		// Priest
		case 5: switch(max($tree['treeone'],$tree['treetwo'],$tree['treethree']))
		    {
			case $tree['treeone']: $spec = "Discipline"; $img = 1; break;
			case $tree['treetwo']: $spec = "Holy"; $img = 2; break;
			case $tree['treethree']: $spec = "Shadow"; $img = 3; break;
		    }
		break;

		// Shaman
		case 7: switch (max($tree['treeone'],$tree['treetwo'],$tree['treethree']))
		    {
			case $tree['treeone']: $spec = "Elemental"; $img = 1; break;
			case $tree['treetwo']: $spec = "Protection"; $img = 2; break;
			case $tree['treethree']: $spec = "Retribution"; $img = 3; break;
		    }
		break;

		// Mage
		case 8: switch(max($tree['treeone'],$tree['treetwo'],$tree['treethree']))
		    {
			case $tree['treeone']: $spec = "Arcane"; $img = 1; break;
			case $tree['treetwo']: $spec = "Fire"; $img = 2; break;
			case $tree['treethree']: $spec = "Frost"; $img = 3; break;
		    }
		break;

		// Warlock
		case 9: switch (max($tree['treeone'],$tree['treetwo'],$tree['treethree']))
		    {
			case $tree['treeone']: $spec = "Affliction"; $img = 1; break;
			case $tree['treetwo']: $spec = "Demonology"; $img = 2; break;
			case $tree['treethree']: $spec = "Destruction"; $img = 3; break;
		    }
		break;

		// Druid
		case 11: switch (max($tree['treeone'],$tree['treetwo'],$tree['treethree']))
		    {
			case $tree['treeone']: $spec = "Balance"; $img = 1; break;
			case $tree['treetwo']: $spec = "Feral Combat"; $img = 2; break;
			case $tree['treethree']: $spec = "Restoration"; $img = 3; break;
		    }
		break;
		} // End Talent Tree Switch

    $prof1 = $charInfo['charactertab']['professions']['skill']['0'];
    $prof2 = $charInfo['charactertab']['professions']['skill']['1'];

	switch ($f) {

	    case "populate":
		$insert = mysql_query("INSERT INTO roster (memberName, rankID, rank, level, classID, class, race, gender, spec, img, prof1name, prof1value, prof1max, prof2name, prof2value, prof2max)
		VALUES ('$character', '$rankID', '$rank', '$memberID[level]', '$classID', '$memberID[class]', '$memberID[race]', '$memberID[gender]', '$spec', '$img', '$prof1[name]', '$prof1[value]', '$prof1[max]', '$prof2[name]', '$prof2[value]', '$prof2[max]')")
			or die (mysql_error($con));
		$array[] = "INSERT INTO roster (memberName, rankID, rank, level, classID, class, race, gender, spec, img, prof1name, prof1value, prof1max, prof2name, prof2value, prof2max)
		VALUES ('$character', '$rankID', '$rank', '$memberID[level]', '$classID', '$memberID[class]', '$memberID[race]', '$memberID[gender]', '$spec', '$img', '$prof1[name]', '$prof1[value]', '$prof1[max]', '$prof2[name]', '$prof2[value]', '$prof2[max]')";
		break;

	    case "update":
		$update = mysql_query("UPDATE roster SET 
			membername = '$character', 
			rankid = '$rankID', 
			rank = '$rank', 
			level = '$memberID[level]', 
			classid = '$classID', 
			class = '$memberID[class]', 
			race = '$memberID[race]', 
			gender = '$memberID[gender]', 
			spec = '$spec', 
			img = '$img', 
			prof1name = '$prof1[name]', 
			prof1value = '$prof1[value]', 
			prof1max = '$prof1[max]', 
			prof2name = '$prof2[name]', 
			prof2value = '$prof2[value]', 
			prof2max = '$prof2[max]' WHERE membername = '$character'")
				or die (mysql_error($con));
		$array[] = "UPDATE roster SET 
			membername = '$character', 
			rankid = '$rankID', 
			rank = '$rank', 
			level = '$memberID[level]', 
			classid = '$classID', 
			class = '$memberID[class]', 
			race = '$memberID[race]', 
			gender = '$memberID[gender]', 
			spec = '$spec', 
			img = '$img', 
			prof1name = '$prof1[name]', 
			prof1value = '$prof1[value]', 
			prof1max = '$prof1[max]', 
			prof2name = '$prof2[name]', 
			prof2value = '$prof2[value]', 
			prof2max = '$prof2[max]' WHERE membername = '$character'";
		break;
		}

    } // End Roster Generation For Loop

switch ($f) {
    case "populate":
	foreach ($array as $sql_line)
	{
	    echo $sql_line . "<br /><br />\n";
	}
	echo "All entries insterted into database.\n";
	break;
    case "update":
	foreach ($array as $sql_line)
	{
	    echo $sql_line . "<br /><br />\n";
	}
	echo "All entries in database updated.\n";
	break;
}

} // end $check

} // End Populate Database Function

function buildRoster($site) {

dbconnect($site);

$query = mysql_query("SELECT * from roster ORDER BY level DESC");
$numrows = mysql_num_rows($query);

if ($numrows == 0) {
    echo "Import the data first numb nuts! Theres nothing here!\n";
} else {

echo "<table border=\"1\" cellspacing=\"1\" cellpadding=\"0\" width=\"600\">\n";
echo "<tr>\n";
echo "<td>Member Name</td><td>Guild Rank</td><td>Level</td><td>Class</td><td>Race</td><td>Gender</td><td>Spec</td><td colspan=\"2\">Professions</td>\n";
echo "</tr>\n";

while ($row = mysql_fetch_assoc($query)) {

echo "<tr>\n";
echo "<td>". $row['membername'] ."</td><td>". $row['rank'] . "</td><td>". $row['level'] ."</td><td>". $row['class'] ."</td><td>". $row['race'] ."</td><td>". $row['gender'] ."</td><td><img title=\"".$row['spec']."\" vpsace=\"0\" hspace=\"0\" align=\"middle\" src=\"/images/icons/class/".strtolower($row['class'])."/talents/".$row[img].".gif\"></td>";
	if (!$row['prof1name']) { 
	  echo "<td>No Profession</td>\n";
	} else { 
	  echo "<td><img title=\"".$row['prof1name']." ".$row['prof1value']." / ".$row['prof1max']."\" vpsace=\"0\" hspace=\"0\" align=\"middle\" src=\"/images/icons/professions/".strtolower($row['prof1name'])."-sm.gif\">&nbsp;". $row['prof1value'] . "of". $row['prof1max'] ."</td>\n";
	}

	if (!$row['prof2name']) { 
	  echo "<td>No Profession</td>\n";
	} else { 
	  echo "<td><img title=\"".$row['prof2name']." ".$row['prof2value']." / ".$row['prof2max']."\" vpsace=\"0\" hspace=\"0\" align=\"middle\" src=\"/images/icons/professions/".strtolower($row['prof2name'])."-sm.gif\">&nbsp;". $row['prof2value'] . "of". $row['prof2max'] ."</td>\n";
	}
echo "</tr>\n";
}

echo "</table>\n";
}

}

?>