I have a simple MySql database, and I need a PHP script to compare
values in different fields and update a field if the values match. 
The fields are: type, name, and match.  For example, the data might be
as follows:

Type     Name     Match
a        Joe      
         Tina     a
         Fred     b
b        Sarah

I need the script to see if any values in Type are the same as Match,
then change Match to the name it matches.  So the above data would be
changed to:

Type     Name     Match
a        Joe      Tina
         Tina     Joe
         Fred     Sarah
b        Sarah    Fred

Hopefully this makes sense.  There must be some kind of "foreach" or
"while" statement or loop that could step through the array and
compare & update.

Hello lstsb

The code I believe you require is below.  You will need to add the
database connection using the standard method.

The script assumes that the data is held in a table called "test" with
columns called "type", "name" and "match".  You may need to change
these in the script (notice the casing of the names too!).

I have tried to make the script as readable as possible and tried to
comment it where I can.  If you have any questions or queries on this
please ask for clarification and I will do all I can to help.

This is my solution to your problem:

// select all the different "types" from the database
$query = "SELECT DISTINCT `type` FROM `test`;";
$run_type_query = mysql_query($query, $conn) or die(mysql_error());
$type_array = mysql_fetch_assoc($run_type_query);
// loop through the different "type"'s
do {
  // if the "type" has a name then process it
  if ( strlen($type_array['type']) > 0 ) {
    // set a variable name to the "type" name
    $matched_type = $type_array['type'];
    // add the "type" name to an array of all the "type" names
    $types[] = $matched_type;
    // select all the rows in the database where the "type" or "match" are the
    // same as the current "type"
    $name_query = "SELECT * FROM `test` WHERE `type`='$matched_type'
OR `match`='$matched_type';";
    $run_name_query = mysql_query($name_query, $conn) or die(mysql_error());
    $name_array = mysql_fetch_assoc($run_name_query);
    // add a prefix to this variable
    $matched_type = "name_" . $matched_type;
	do {
	  // create a dynamic variable in the form of $name_a or $name_b 
	  // that holds a list of all the matched names separated by a space
	  $$matched_type .= $name_array['name'] . " ";
	} while ($name_array = mysql_fetch_assoc($run_name_query));
    };
} while ($type_array = mysql_fetch_assoc($run_type_query)); 

// begin a loop through the different "type"'s to update the database
foreach ( $types as $name_update ) {
  // set the current variable name and begin processing
  $var_name = "name_" . $name_update;
  $list_of_names = $$var_name;
  // put the list of names "joe kate mary" into an array
  $name_list = split(" ", chop($list_of_names));
  // loop through the names and update the database
  foreach ( $name_list as $the_name ) {
	// find all the other names not equal to the current name
	$other_names = "";
	for ( $i=0;$i<count($name_list);$i++) {
	  if ( $name_list[$i] != $the_name ) { $other_names = $othernames . "
" . $name_list[$i]; };
	};
	// perform the update on the database
	$query = "UPDATE `test` SET `match` = '$other_names' WHERE `name` =
'$the_name'; ";
	mysql_query($query, $conn) or die(mysql_error());
  }
}

---

Request for Answer Clarification by lstsb-ga on 04 Jul 2004 15:36 PDT

Hi, I realized I need it to do something slightly different.  The
Match is modified to the name ONLY when Type equals Match, not when
Type equals Type.  For instance, now the script will match people who
have the same Type in addition to those whose Types are the same as
Matches.

In the above data set, if Sarah is Type 'a' rather than 'b', I don't
want her matched with Joe.  Joe would be matched with Tina, and Sarah
with Tina.

Does this request make sense, and is it possible to modify the script
slightly to do what I've described?

thanks

---

Clarification of Answer by palitoy-ga on 05 Jul 2004 02:59 PDT

I am not sure I fully understand the problem but I think the solution
is to change the following line:

$name_query = "SELECT * FROM `test` WHERE `type`='$matched_type'
OR `match`='$matched_type'";

to:

$name_query = "SELECT * FROM `test` WHERE `type`='$matched_type'";

Does that do what you need?  If not I think this should probably be
posted as another question as it will require reworking the code.

---

Request for Answer Clarification by lstsb-ga on 05 Jul 2004 17:19 PDT

Hi, I modified the question and re-posted it under "PHP: match values
& modify data".  Hopefully that doesn't increase the difficulty of the
task by a huge factor...

---

Clarification of Answer by palitoy-ga on 06 Jul 2004 00:49 PDT

I will try and take a look at the question, if I do not have the
opportunity or time to answer it I am sure there will be another
Google Researcher who will pick it up.

Thanks, works perfectly!  FYI, you had an extra semicolon in the
queries, but when I took them out, it does exactly what I want.

thanks again.