[SOLVED] Checking the results from my MySQL table against my parsed XML file

[wtfsmd]

I am trying to check the results from my MySQL table against my parsed XML file. What i want to do is if the two match i want to run the Update script, if they do not match i want to run the Insert script.

I don't want to use Truncate or Delete.

Everything i have tried has not worked and i was wondering if anyone could give a few examples, here is one of the ways i was trying to go about this.

(ill spare you all of the parsing code)

<?php

function start_tag($parser, $name, $attribs) {
   global $current;
   $current = $name;

   if (is_array($attribs)) {
      while(list($key,$val) = each($attribs)) {
        if ($name == "CHARACTER") { 
         if ($key == "NAME") { $char_name = $val; }
         if ($key == "LEVEL") { $level = $val; }
         if ($key == "CLASSID") { $class = $val;}
         if ($key == "GENDERID") { $gender = $val; }
         if ($key == "RACEID") { $race = $val; }
         if ($key == "RANK") { $rank = $val; }
        } 
       }

        $query = "SELECT * FROM roster"; 
        $result = mysql_query($query) or die(mysql_error());
        while($row = mysql_fetch_array($result)){
            $check_name = $row['name'];
            if($check_name == $char_name){ $check = "yes";}else{ $check = "no";}
        }
        
        
        if($check == "yes") {
            // Insert the data from the XML file into the table "ROSTER"
               mysql_query("UPDATE roster SET level='$level', class='$class', gender='$gender', race='$race', rank='$rank' WHERE name='$char_name'")
              or die(mysql_error());
        } 
        elseif($check == "no") {
             mysql_query("INSERT INTO roster 
              (name, level, class, gender, race, rank) VALUES
              ('$char_name', '$level', '$class', '$gender', '$race', '$rank' ) ") 
              or die(mysql_error());
        }
    }
}

?>

[wtfsmd]

Anyone have some ideas?

[Mchl]

How about INSERT ON DUPLICATE KEY UPDATE syntax? If the name in your roster table is unique, it would work.

[wtfsmd]

Okay i used the following i do not get any errors and it doesn't work, what am i missing?

<?php
function start_tag($parser, $name, $attribs) {
   global $current;
   $current = $name;

   if (is_array($attribs)) {
      while(list($key,$val) = each($attribs)) {
	if ($name == "CHARACTER") { 
		if ($key == "NAME") { $char_name = $val; }
		if ($key == "LEVEL") { $level = $val; }
		if ($key == "CLASSID") { $class = $val;}
		if ($key == "GENDERID") { $gender = $val; }
		if ($key == "RACEID") { $race = $val; }
		if ($key == "RANK") { $rank = $val; }
	} 
       }

	mysql_query("INSERT INTO roster 
	(name, level, class, gender, race, rank) VALUES ('$char_name', '$level', '$class', '$gender', '$race', '$rank' ) ON DUPLICATE KEY UPDATE level='$level', class='$class', gender='$gender', race='$race', rank='$rank', name='$char_name'")
	or die(mysql_error());

    }
}
?>

[Mchl]

Do

echo $query = "INSERT INTO roster 
      (name, level, class, gender, race, rank) VALUES ('$char_name', '$level', '$class', '$gender', '$race', '$rank' ) ON DUPLICATE KEY UPDATE level='$level', class='$class', gender='$gender', race='$race', rank='$rank'";

 

To see if your query is well formed. You might even try to feed it into phpMyAdmin or other tool to see if it works.

 

Is `name` column declared as UNIQUE in `roster`?

 

[wtfsmd]

I figured it out and it works now, apparently i made a noob move. Here was the fix:

 

Here is my old code

<?php
mysql_query("INSERT INTO roster 
      (name, level, class, gender, race, rank) VALUES ('$char_name', '$level', '$class', '$gender', '$race', '$rank' ) ON DUPLICATE KEY UPDATE level='$level', class='$class', gender='$gender', race='$race', rank='$rank', name='$char_name'")
      or die(mysql_error());
?>

 

Here is the fixed code

<?php
mysql_query("INSERT INTO roster 
      (name, level, class, gender, race, rank) VALUES ('$char_name', '$level', '$class', '$gender', '$race', '$rank' ) ON DUPLICATE KEY UPDATE name='$char_name', level='$level', class='$class', gender='$gender', race='$race', rank='$rank'")
      or die(mysql_error());
?>

 

It didn't work when i had the (name='$char_name') on the end but when i switched it around it works.

Thanks so much for you help.

[Mchl]

You could drop the name from UPDATE part all together.