need help comparing data between sources

[schwim]

Hi there everyone,

I'm using some classes to contact a remote server and get some information on guild members(php battlenet wow armory api). I've figured out how to grab the data from the remote server and for each remote member record, check the local db and insert new or update existing. What I'm unable to figure out is how to do the following:

If there's a local record of a member that isn't in the remote data, I need to change the local record to reflect active=0 for that row. I think I need to compare two arrays, but I don't know how to implement this.

Here's my current code:

			$members = $guild->getMembers('rank', 'asc');
			$update_count = 0;
			$insert_count = 0;
			foreach($members as $member) {

			//Get character information
			$mname = $member['character']['name'];
			$mgender = $member['character']['gender'];
			$mlevel = $member['character']['level'];
			$mrace = $member['character']['race'];
			$mclass = $member['character']['class'];
			$mrank = $member['rank'];

			$query = "SELECT name FROM addon_guild_members WHERE name='$mname'";
			$result = mysql_query ($query) or die('MySQL error: ' . mysql_error() . '<hr/>' . $query);
			$result_rows = mysql_num_rows($result);
			if($result_rows==1){
				//Match found in the local db.  Update row.
				mysql_query("UPDATE addon_guild_members SET gender = '$mgender', level = '$mlevel', race = '$race', class = '$mclass', rank = '$mrank', active = '1'  WHERE name = '$mname'") or die("Failed to update member in the database.<br><br>" . mysql_error());	
				$update_count = $update_count + 1;
			}elseif($result_rows==0){
				//No local record found.  Insert new row.
				$query2="INSERT INTO addon_guild_members (name,gender,level,race,class,rank,active) VALUES ('$mname','$mgender','$mlevel','$mrace','$mclass','$mrank','1')";
				$result2=mysql_query($query2) or die("Failed to insert new member into database.<br><br>" . mysql_error());
				$insert_count = $insert_count + 1;
			}

			// $results .= "<span style='color: " . $classColor . "'>Name: ".$mname." -> G Rank: ".$mrank." -> Gender: ".$mgender." -> Level: ".$mlevel." -> Race: ".$mrace."</span><br />";

		}

		echo("<b>The roster has been updated. ".$insert_count." new members have been added and ".$update_count." current members have been updated.</b>");

I would very much appreciate it if someone could help me figure out how to set active = 0 on the local member row if it no longer exists in the data returned from the remote data.

If you need some data from the classes I use, I'll be happy to post it, I just don't know what would be useful.

Thanks a bunch for your time!

[schwim]

Hi there Brad and thanks very much for your help!

1) I'm setting this up to run hourly with the option to force an earlier update if desired. With that time frame, it seems your suggestion would work super.

2) Could you please show me how I might convert my three queries into one? If that's possible, then I would have two in total, one setting all active = 0 and then the one combined query, correct?

[schwim]

There would be some serious issues if other processes occurred while they were flagged as inactive. I kind of understand your solution but it's fuzzy in my mind 🙂 Do you perhaps mean something like this: have the insert/update process set a time stamp and then immediately after, run another update and if the timestamp is older than say 1 minute(the update process takes seconds), then set active to 0.

Is there a better method you can think of than this?