Hello lstsb
I have included the code that you require in this answer below.
It makes these assumptions on your database:
1) The Name field is called name (note all lower case)
2) The Type field is called type (note all lower case)
3) The Match field is called match (note all lower case)
4) The Level1 field is called level (note all lower case and no 1)
5) The Levelmatch field is called levelmatch (note all lower case)
6) These fields are within a database called `test`.
You will need to change these variables to match your database and
connect to your database in the usual way before using the script.
If you have any questions regarding this script please let me know and
I will do my best to help.
<?
// find all the types equal to 'a'
$type_query = "SELECT * FROM `test` WHERE `type`='a'";
$run_type_query = mysql_query($type_query, $conn) or die(mysql_error());
$type_array = mysql_fetch_assoc($run_type_query);
// loop through all that are found
do {
// find all the match = a results
$match_query = "SELECT * FROM `test` WHERE `match`='a'";
$run_match_query = mysql_query($match_query, $conn) or die(mysql_error());
$match_array = mysql_fetch_assoc($run_match_query);
// loop thorough all that are found
do {
// insert a row into the database with the current information
$match_query = "INSERT INTO `test` (`name`, `type`, `match`,
`level`, `levelmatch`) VALUES
('{$type_array['name']}','{$type_array['type']}','{$match_array['name']}','{$type_array['level']}','{$match_array['levelmatch']}')";
$match_query = mysql_query($match_query, $conn) or die(mysql_error());
// insert the opposite line into the database (with the names
// the other way around)
$match_query = "INSERT INTO `test`
(`name`,`type`,`match`,`level`,`levelmatch`) VALUES
('{$match_array['name']}','{$match_array['type']}','{$type_array['name']}','{$type_array['level']}','{$match_array['levelmatch']}')";
$match_query = mysql_query($match_query, $conn) or die(mysql_error());
} while ($match_array = mysql_fetch_assoc($run_match_query));
} while ($type_array = mysql_fetch_assoc($run_type_query));
// delete unwanted data from the database
$query = "DELETE FROM `test` WHERE ((`type` = 'a' AND `match` = '') OR
(`type` = '' AND `match` = 'a'))";
mysql_query($query, $conn) or die(mysql_error());
?> |
Request for Answer Clarification by
lstsb-ga
on
06 Jul 2004 22:01 PDT
Hi palitoy.
Is there a simple way to compare/match ANY values in the 'type' and
'match' fields, rather than just 'a'? I know I could create multiple
copies of the script for every expected value in the fields, but
that's not very elegant (and would speed be an issue with many
records?)
thanks
|
Clarification of Answer by
palitoy-ga
on
07 Jul 2004 01:24 PDT
This should be possible. It will just need another loop and the
beginning to discover the different type/match possibilities. I will
work on this today and post the answer later for you.
With regards to the speed of the script that really depends on the
size of the database, if it is small you will not notice any real
differences in speed and if it is large there will be a delay whilst
the script is running. I decided to insert the rows and then delete
the old ones as this I thought would mean less database accesses than
if you first had to check whether the line existed and then either
insert it or update it.
|
Clarification of Answer by
palitoy-ga
on
07 Jul 2004 04:48 PDT
This is the code you need (it should replace the original above):
<?
// loop through the different types in the database
$q = "SELECT DISTINCT `type`,`match` FROM `test`";
$run_q = mysql_query($q, $conn) or die(mysql_error());
$type_array = mysql_fetch_assoc($run_q);
$all_matches = array();
do {
// set a variable name to the "type" name
$types = $type_array['type'];
if ( $types == '' ) { $types = $type_array['match']; };
// set a variable to see if the type has already been matched
if ( $types != '' && !in_array($types, $all_matches, true) ) {
$all_matches[] = $types; };
} while ($type_array = mysql_fetch_assoc($run_q));
foreach ( $all_matches as $type_match ) {
do {
// find all the types equal to a
$type_query = "SELECT * FROM `test` WHERE `type`='{$type_match}'";
$run_type_query = mysql_query($type_query, $conn) or die(mysql_error());
$type_array = mysql_fetch_assoc($run_type_query);
// loop through all that are found
do {
// find all the match = a
$match_query = "SELECT * FROM `test` WHERE `match`='{$type_match}'";
$run_match_query = mysql_query($match_query, $conn) or die(mysql_error());
$match_array = mysql_fetch_assoc($run_match_query);
// loop thorough all that are found
do {
$match_query = "INSERT INTO `test` (`name`, `type`, `match`,
`level`, `levelmatch`) VALUES
('{$type_array['name']}','{$type_array['type']}','{$match_array['name']}','{$type_array['level']}','{$match_array['levelmatch']}')";
$match_query = mysql_query($match_query, $conn) or die(mysql_error());
$match_query = "INSERT INTO `test`
(`name`,`type`,`match`,`level`,`levelmatch`) VALUES
('{$match_array['name']}','{$match_array['type']}','{$type_array['name']}','{$type_array['level']}','{$match_array['levelmatch']}')";
$match_query = mysql_query($match_query, $conn) or die(mysql_error());
} while ($match_array = mysql_fetch_assoc($run_match_query));
} while ($type_array = mysql_fetch_assoc($run_type_query));
// delete unwanted data
$query = "DELETE FROM `test` WHERE ((`type` = '{$type_match}' AND
`match` = '') OR (`type` = '' AND `match` = '{$type_match}'))";
mysql_query($query, $conn) or die(mysql_error());
} while ($type_array = mysql_fetch_assoc($run_type_query));
}; // end foreach
?>
The difference is basically that the database is now queried for the
different values in "type" and "match" and that the original script is
then rerun for each of the found values.
|
Request for Answer Clarification by
lstsb-ga
on
11 Jul 2004 17:29 PDT
One more modification (let me know if I must post this as a new question):
the `type` and `match` values are compared as before, but matched only
if the `levelmatch` value differs by less than 2. So using the
following data -
Name Type Match Level Levelmatch
Sarah a 5 1
Fred a 3
Joe a 2 2
Only Joe would be matched with Fred, because their `levelmatch` values
differ by 1. Sarah & Fred's `levelmatch` values differ by 2, so they
fall outside the matching range. From the above data, the script
would return:
Name Type Match Level Levelmatch
Sarah a 5 1
Fred Joe 2 3
Joe a Fred 2 2
|
Clarification of Answer by
palitoy-ga
on
12 Jul 2004 01:42 PDT
This would require a significant amount of thinking and re-coding so
it probably needs to posted as a new question.
I will try to answer the question for you but I am quite busy at the
moment. If you would like a specific researcher to answer the
question you can nominate this in the question title by saying "For
XXX-ga only" where XXX-ga is the researchers name.
|
,
0 Comments
)