Solved

MySQL Duplicate Values In A Field / Take Action According To Those Values

Posted on 2009-03-31
36
264 Views
Last Modified: 2013-12-12
I have a table in a MySQL db called "insp"(create table attached).  The insp table is used to track specific types of violations.  In this table, only one violation type per PropRec is allowed. Occasionally, a situation will occur when a PropRec number needs to be merged with another PropRec number.  When this happens, it's possible that the violation info for the merged property is now incorrect.   Here's a possible scenario:

(Create the attached table first. This example uses the data from that table)

Let's say we want to replace every PropRec value of "12" with "15".
If that were to happen, duplicate violation values would be created.  
Look at RecNum 139. Its PropRec is 15, and it has a Furniture violation.
Now look at RecNum 135.  Its PropRec is 12, and it also has a Furniture violation. This is where the error occurs.
This type of error occurs with RecNums 140 and 131 too.  Its error is found in the "Other" column.

My goal is to single out the duplicate values that were created by a merge.  The value that's "singled out" would need to be based on the Date column.
Something like:

IF (Duplicate value found){
     IF (value1Date >= vaule2Date){
          echo value1}
     ELSE{
          echo value2}
}

Is there a way to loop through the db to find and return all duplicate values created by a merge?  I have some code where I've attempted a solution, so I'll post that.  I'm not sure if I was even on the right track, though.  Let me know if I need to explain myself any further.  Thanks for reading.
CREATE TABLE `insp` (

  `RecNum` int(11) NOT NULL auto_increment,

  `PropRec` int(11) NOT NULL default '0',

  `PeopleRec` int(11) NOT NULL default '0',

  `Date` date NOT NULL default '0000-00-00',

  `ReDate` date default NULL,

  `WeedGrass` smallint(6) default '0',

  `InopVeh` smallint(6) default '0',

  `LawnPark` smallint(6) default '0',

  `OpenStore` smallint(6) default '0',

  `Furniture` smallint(6) default '0',

  `ProAnimal` smallint(6) default '0',

  `NoPermit` smallint(6) default '0',

  `AnimalWaste` smallint(6) default '0',

  `StormWater` tinyint(6) default NULL,

  `Status` varchar(50) NOT NULL default '',

  `Other` varchar(255) default NULL,

  `Time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

  PRIMARY KEY  (`RecNum`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=141 ;
 

--

-- Dumping data for table `insp`

--
 

INSERT INTO `insp` (`RecNum`, `PropRec`, `PeopleRec`, `Date`, `ReDate`, `WeedGrass`, `InopVeh`, `LawnPark`, `OpenStore`, `Furniture`, `ProAnimal`, `NoPermit`, `AnimalWaste`, `StormWater`, `Status`, `Other`, `Time`) VALUES

(131, 12, 1, '2009-03-03', '2009-03-17', 1, 0, 0, 0, 0, 0, 0, 0, 1, 'Failed', 'leaves', '2009-03-26 08:04:33'),

(133, 12, 1, '2009-03-03', NULL, 0, 0, 1, 0, 0, 0, 0, 0, 0, 'Plus', '', '2009-03-30 10:17:50'),

(134, 12, 1, '2009-03-01', NULL, 0, 0, 0, 0, 1, 0, 0, 0, 0, 'Plus', 'test2', '2009-03-30 10:04:34'),

(135, 12, 1, '2009-03-13', NULL, 0, 1, 0, 1, 0, 1, 1, 1, 0, 'Plus', '', '2009-03-31 07:57:35'),

(136, 12, 1, '2009-03-01', '2009-03-19', 0, 0, 0, 0, 0, 0, 0, 0, 0, 'Failed', 'lights', '2009-03-19 12:25:31'),

(137, 13, 1, '2009-03-02', NULL, 0, 0, 0, 0, 1, 0, 0, 0, 0, 'Pass', 'test2', '2009-03-20 09:45:42'),

(138, 14, 1, '2009-03-02', '2009-03-20', 0, 0, 0, 0, 1, 0, 0, 0, 2, 'Failed', 'test2 -Passed-', '2009-03-25 08:18:00'),

(139, 15, 1, '2009-03-04', NULL, 0, 0, 0, 0, 1, 0, 0, 0, 0, 'Pass', 'test', '2009-03-30 15:32:28'),

(140, 15, 1, '2009-03-04', NULL, 0, 0, 0, 0, 0, 0, 0, 0, 0, 'Pass', 'leaves', '2009-03-26 14:27:31');
 
 
 
 

//PHP
 

<?

$database="propmain";

mysql_connect ("localhost", "root", "");

@mysql_select_db($database) or die( "Unable to select database");
 
 

$reslc = "select count(*) as co from insp where PropRec='15' and ( Other <> '' or Furniture <> '0' )  ";

$resc=mysql_query($reslc) or die(mysql_error());

		$rowc = mysql_fetch_array($resc);

$reslc2 = "select count(*) as co2 from insp where PropRec='12' and (Other <> '' or Furniture <> '0' )  ";

$resc2=mysql_query($reslc2) or die(mysql_error());

		$rowc2 = mysql_fetch_array($resc2);

		

		echo $rowc[co];echo "<br>";

		echo $rowc2[co2];echo "<br>";

		
 

$resl = "select * from insp where PropRec='15' and ( Other <> '' or Furniture <> '0' )  ";

    		$res=mysql_query($resl) or die(mysql_error());

		

		

		$resl2 = "select * from insp where PropRec='12' and (Other <> '' or Furniture <> '0' )  ";

    		$res2=mysql_query($resl2) or die(mysql_error());

		

		if($rowc[co] >= $rowc2[co2])

		{

		

		while($row = mysql_fetch_array($res)){

			while($row2 = mysql_fetch_array($res2)){

			

			

			if($row[Other]==$row2[Other] )

				{

					

					

					if($row['Date']>= $row2['Date']){

				echo $row[RecNum];echo "-";

				echo $row[Other];echo"<br>";

					}

					else{

					echo $row2[RecNum];echo "-";

					echo $row2['Other'];echo "<br>";

					}

				}

		

		

		

		}

		}

		}

		else{

			

			while($row2 = mysql_fetch_array($res2)){

			while($row = mysql_fetch_array($res)){

			

			if(($row['Other']==$row2['Other']))

				{

					

					if($row['Date']>= $row2['Date']){

				echo $row[RecNum];echo "-";

				echo $row[Other];echo"<br>";

					}

					else{

					echo $row2[RecNum];echo "-";

					echo $row2['Other'];echo "<br>";

					}

				}

				if(($row['Furniture']!="0"and $row2['Furniture']) !="0")

				{

					echo $row[Furniture];echo "-";echo $row2[Furniture];

					if($row['Date']>= $row2['Date']){

				echo $row[RecNum];echo "-";

				echo $row[Other];echo"<br>";

					}

					else{

					echo $row2[RecNum];echo "-";

					echo $row2['Other'];echo "<br>";

					}

				}

				

			

		

		

		

		}

			}

		}
 

?>

Open in new window

0
Comment
Question by:thedeal56
  • 18
  • 18
36 Comments
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 24031157
1.) How are you merging in the first place?

2.) Is there a field based on an underlying address your method of "matching"? Please describe the way you can determine that PropRec 15 and 12 are "pairs"

3.) If you are going to "replace" the dulpicate are you deleting the "old" record or the "new" record? Which of the 2 is saved; or is any deleted?

Thanks!

0
 

Author Comment

by:thedeal56
ID: 24031825
Thanks for the fast reply.  This problem is driving me crazy, and I appreciate you taking an interest in helping me.

1.) How are you merging in the first place?

The merge comes from an admin who recognizes an error with a particular property's entry.  The admin is able to search the database for the entry in error, and then provide the new, correct info.  

2.) Is there a field based on an underlying address your method of "matching"? Please describe the way you can determine that PropRec 15 and 12 are "pairs"

The column named "PropRec" contains a number value that corresponds to property info stored in another table.  

Here's a scenario where PropRec 15 and 12 would merge:

Let's say the actual address for 15 = 2615 Medical Center Parkway(This is correct)
Let's also say that the actual address for 12 = 2651 Medical Center Parkway(The street number is incorrect)

The admin catches this error, so he/she looks up the incorrect entry (12) and then inputs the correct info.
The correct info that the admin enters happens to already be in existence.  This is where 15 comes in.  
(The process of taking the users info and searching the db for it is already worked out)

Now we have our two PropRec numbers that we need to merge together.

3.) If you are going to "replace" the dulpicate are you deleting the "old" record or the "new" record? Which of the 2 is saved; or is any deleted?

Currently, I am just looking for a way to echo out the duplicate entry that has the most recent date.  Once I can isolate that(those) entry(entries), I can proceed with what comes next.  I already have the "what comes next" part worked out.  

I hope those answers help clarify what I'm trying to do. Let me know if you need additional info.  Thanks again for helping me on this.  

0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 24032013
1.) Are the correct and error PropRec #s known before comparison?

As I can see from your code only 12 and 15 are referenced.
Yet in the table dump I see 12, 13, 14, 15.
How are these pairs distinguished from this group? Manually right?

---

2.) I am presuming that 12 and 15 are known pairs at this point. Are you manually entering the correct and error PropRecs #s?

---

Thanks,
=NerdsOfTech
0
 

Author Comment

by:thedeal56
ID: 24032138
1.) Are the correct and error PropRec #s known before comparison?

They are known by the admin, and the correct info is entered in manually by the admin.

2.) I am presuming that 12 and 15 are known pairs at this point. Are you manually entering the correct and error PropRecs #s?

I have a page where the admin can lookup the property with the incorrect info.  The admin will pick from a list of returned results.  When they have selected the error entry, it will then take them to a page that will ask them for the correct info.  Most of the time(I'm guessing) the correct info entered in by the admin will not exist in the database.  In this case, the process is easy.  I will simply need to update the info in the property table.  What I am trying to do is account for the possibility where the correct info entered in by the admin already exists in the db.  If the correct info already exists, this is when the merger will need to occur.
0
 

Author Comment

by:thedeal56
ID: 24032170
I need to add some addition info to this question:

1.) Are the correct and error PropRec #s known before comparison?
The admin knows that a property is incorrect, but they may not know that an entry with the correct info also exists the db.  They enter the correct info manually, and if it happens to exist in the db, that is when the violation comparisons will come into play.  
0
 

Author Comment

by:thedeal56
ID: 24032495
Here's a real-world example:

You go out to 2615 Medical Center Parkway and do an inspection.  From this inspection, you determine that the property has a weeds and grass violation, so you enter it into the system as such.  6 months go by, and now I go out to 2615 Medical Center Parkway for an inspection.  I determine that the property has a weeds and grass violation, but when I enter it into the system, I mess up the street number.  I input the property as 2651 Medical Center Parkway.  The system accepts my incorrect info and carries on as normal.  A few days later, the admin notices that 2651 Medical Center Parkway is incorrect and attempts to change the entry.  When the admin submits the correct info, (2615 Medical Center Parkway) my system will know that 2615 Medical Center Parkway already exists in the database. What the system needs to do, is check to see if the same violation occurred on both the correct entry and the incorrect entry.  In this case, the system would find that the error entry and the correct entry have a weeds and grass violation.  I need to return the record number of the entry that has the most recent date.  
0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 24033816
Thanks for the information. Here is my solution. Please post any errors or debug output from this if this doesn't work. Thanks!

=NerdsOfTech
<?php

$database="propmain";

mysql_connect ("localhost", "root", "");

@mysql_select_db($database) or die( "Unable to select database");
 

// run self join query to find duplicate records
 

$CPropRec = 12;

$EPropRec = 15;
 

$sql = 'SELECT ';

$sql .= 'C.RecNum, C.PropRec, C.Date, C.ReDate, C.Status, ';

$sql .= 'C.WeedGrass, C.InopVeh, C.LawnPark, C.OpenStore, C.Furniture, C.ProAnimal, C.NoPermit, C.AnimalWaste, C.StormWater, C.Other ';

$sql .= 'E.RecNum as ERecNum, E.PropRec as EPropRec, E.Date as EDate, E.ReDate as EReDate, E.Status as EStatus, ';

$sql .= 'E.WeedGrass as EWeedGrass, E.InopVeh as EInopVech, E.LawnPark as ELawnPark, E.OpenStore as EOpenStore, E.Furniture as EFurniture, ';

$sql .= 'E.ProAnimal as EProAnimal, E.NoPermit as ENoPermit, E.AnimalWaste as EAnimalWaste, E.StormWater as EStormWater, E.Other as EOther, ';

$sql .= 'IF (C.Date >= E.Date) as isCMostRecent ';

$sql .= 'FROM insp C, insp E ';

$sql .= 'WHERE ';

$sql .= '(';

$SQL .= '(C.WeedGrass = E.WeedGrass)';

$sql .= ' OR ';

$SQL .= '(C.InopVeh = E.InopVeh)';

$sql .= ' OR ';

$SQL .= '(C.LawnPark = E.LawnPark)';

$sql .= ' OR ';

$SQL .= '(C.OpenStore = E.OpenStore)';

$sql .= ' OR ';

$SQL .= '(C.Furniture = E.Furniture)';

$sql .= ' OR ';

$SQL .= '(C.ProAnimal = E.ProAnimal)';

$sql .= ' OR ';

$SQL .= '(C.AnimalWastet = E.AnimalWaste)';

$sql .= ' OR ';

$SQL .= '(C.StormWater = E.StormWater)';

$sql .= ' OR ';

$SQL .= '(C.Other = E.Other)';

$sql .= ') ';

$sql .= 'AND ((c.PropRec = ' . $CPropRec . ') AND (E.PropRec = ' . $EPropRec . '))';

$sql .= 'ORDER BY C.RecNum, E.RecNum;';
 

$results = mysql_query($sql);
 

// output results
 

echo 'debug: <br />';

echo $sql . '<br />';
 

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

 echo 'Duplicate record found: RecNum = ' . $row['RecNum'] . ' & RecNum = ' . $row['ERecNum'];

 echo 'Most recent dated record is: ' . ($row['isCMostRecent'] ? $row['RecNum'] : $row['ERecNum']);

 echo 'debug: <br />';

 echo '<br /><br />';

 foreach ($row as $key => $value) {

   echo $key . ':' . $value . '<br />';

 }

}

?>

Open in new window

0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 24033827
CORRECTED SQL use this instead. Output debug.
<?php

$database="propmain";

mysql_connect ("localhost", "root", "");

@mysql_select_db($database) or die( "Unable to select database");
 

// run self join query to find duplicate records
 

$CPropRec = 12;

$EPropRec = 15;
 

$sql = 'SELECT ';

$sql .= 'C.RecNum, C.PropRec, C.Date, C.ReDate, C.Status, ';

$sql .= 'C.WeedGrass, C.InopVeh, C.LawnPark, C.OpenStore, C.Furniture, C.ProAnimal, C.NoPermit, C.AnimalWaste, C.StormWater, C.Other ';

$sql .= 'E.RecNum as ERecNum, E.PropRec as EPropRec, E.Date as EDate, E.ReDate as EReDate, E.Status as EStatus, ';

$sql .= 'E.WeedGrass as EWeedGrass, E.InopVeh as EInopVech, E.LawnPark as ELawnPark, E.OpenStore as EOpenStore, E.Furniture as EFurniture, ';

$sql .= 'E.ProAnimal as EProAnimal, E.NoPermit as ENoPermit, E.AnimalWaste as EAnimalWaste, E.StormWater as EStormWater, E.Other as EOther, ';

$sql .= 'IF (C.Date >= E.Date) as isCMostRecent ';

$sql .= 'FROM insp C, insp E ';

$sql .= 'WHERE ';

$sql .= '(';

$SQL .= '(C.WeedGrass = E.WeedGrass) ';

$sql .= 'OR ';

$SQL .= '(C.InopVeh = E.InopVeh) ';

$sql .= 'OR ';

$SQL .= '(C.LawnPark = E.LawnPark) ';

$sql .= 'OR ';

$SQL .= '(C.OpenStore = E.OpenStore) ';

$sql .= 'OR ';

$SQL .= '(C.Furniture = E.Furniture) ';

$sql .= 'OR ';

$SQL .= '(C.ProAnimal = E.ProAnimal) ';

$sql .= 'OR ';

$SQL .= '(C.AnimalWastet = E.AnimalWaste) ';

$sql .= 'OR ';

$SQL .= '(C.StormWater = E.StormWater) ';

$sql .= 'OR ';

$SQL .= '(C.Other = E.Other)';

$sql .= ') ';

$sql .= 'AND ((c.PropRec = ' . $CPropRec . ') AND (E.PropRec = ' . $EPropRec . ')) ';

$sql .= 'ORDER BY C.RecNum, E.RecNum;';
 

$results = mysql_query($sql);
 

// output results
 

echo 'debug: <br />';

echo $sql . '<br />';
 

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

 echo 'Duplicate record found: RecNum = ' . $row['RecNum'] . ' & RecNum = ' . $row['ERecNum'];

 echo 'Most recent dated record is: ' . ($row['isCMostRecent'] ? $row['RecNum'] : $row['ERecNum']);

 echo 'debug: <br />';

 echo '<br /><br />';

 foreach ($row as $key => $value) {

   echo $key . ':' . $value . '<br />';

 }

}

?>

Open in new window

0
 

Author Comment

by:thedeal56
ID: 24033911
Oh, man. That looks awesome.  I see how you're going about doing it.  It does give this error, though:

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in C:\wamp\www\Prop\record2.php on line 49

The entire output is attached in the snippet
0
 

Author Comment

by:thedeal56
ID: 24033924
that's weird, I thought I attached this
debug:

SELECT C.RecNum, C.PropRec, C.Date, C.ReDate, C.Status, C.WeedGrass, C.InopVeh, C.LawnPark, C.OpenStore, C.Furniture, C.ProAnimal, C.NoPermit, C.AnimalWaste, C.StormWater, C.Other E.RecNum as ERecNum, E.PropRec as EPropRec, E.Date as EDate, E.ReDate as EReDate, E.Status as EStatus, E.WeedGrass as EWeedGrass, E.InopVeh as EInopVech, E.LawnPark as ELawnPark, E.OpenStore as EOpenStore, E.Furniture as EFurniture, E.ProAnimal as EProAnimal, E.NoPermit as ENoPermit, E.AnimalWaste as EAnimalWaste, E.StormWater as EStormWater, E.Other as EOther, IF (C.Date >= E.Date) as isCMostRecent FROM insp C, insp E WHERE (OR OR OR OR OR OR OR OR ) AND ((c.PropRec = 12) AND (E.PropRec = 15)) ORDER BY C.RecNum, E.RecNum;
 

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in C:\wamp\www\Prop\record2.php on line 49

Open in new window

0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 24033972
fixed ;)
$result = mysql_query($sql);
<?php

$database="propmain";

mysql_connect ("localhost", "root", "");

@mysql_select_db($database) or die( "Unable to select database");

 

// run self join query to find duplicate records

 

$CPropRec = 12;

$EPropRec = 15;

 

$sql = 'SELECT ';

$sql .= 'C.RecNum, C.PropRec, C.Date, C.ReDate, C.Status, ';

$sql .= 'C.WeedGrass, C.InopVeh, C.LawnPark, C.OpenStore, C.Furniture, C.ProAnimal, C.NoPermit, C.AnimalWaste, C.StormWater, C.Other ';

$sql .= 'E.RecNum as ERecNum, E.PropRec as EPropRec, E.Date as EDate, E.ReDate as EReDate, E.Status as EStatus, ';

$sql .= 'E.WeedGrass as EWeedGrass, E.InopVeh as EInopVech, E.LawnPark as ELawnPark, E.OpenStore as EOpenStore, E.Furniture as EFurniture, ';

$sql .= 'E.ProAnimal as EProAnimal, E.NoPermit as ENoPermit, E.AnimalWaste as EAnimalWaste, E.StormWater as EStormWater, E.Other as EOther, ';

$sql .= 'IF (C.Date >= E.Date) as isCMostRecent ';

$sql .= 'FROM insp C, insp E ';

$sql .= 'WHERE ';

$sql .= '(';

$SQL .= '(C.WeedGrass = E.WeedGrass) ';

$sql .= 'OR ';

$SQL .= '(C.InopVeh = E.InopVeh) ';

$sql .= 'OR ';

$SQL .= '(C.LawnPark = E.LawnPark) ';

$sql .= 'OR ';

$SQL .= '(C.OpenStore = E.OpenStore) ';

$sql .= 'OR ';

$SQL .= '(C.Furniture = E.Furniture) ';

$sql .= 'OR ';

$SQL .= '(C.ProAnimal = E.ProAnimal) ';

$sql .= 'OR ';

$SQL .= '(C.AnimalWastet = E.AnimalWaste) ';

$sql .= 'OR ';

$SQL .= '(C.StormWater = E.StormWater) ';

$sql .= 'OR ';

$SQL .= '(C.Other = E.Other)';

$sql .= ') ';

$sql .= 'AND ((c.PropRec = ' . $CPropRec . ') AND (E.PropRec = ' . $EPropRec . ')) ';

$sql .= 'ORDER BY C.RecNum, E.RecNum;';

 

$result = mysql_query($sql);

 

// output results

 

echo 'debug: <br />';

echo $sql . '<br />';

 

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

 echo 'Duplicate record found: RecNum = ' . $row['RecNum'] . ' & RecNum = ' . $row['ERecNum'];

 echo 'Most recent dated record is: ' . ($row['isCMostRecent'] ? $row['RecNum'] : $row['ERecNum']);

 echo 'debug: <br />';

 echo '<br /><br />';

 foreach ($row as $key => $value) {

   echo $key . ':' . $value . '<br />';

 }

}

?>

Open in new window

0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 24033986
Oops one more fix needed. Wait please for reply.
0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 24033993
Fixed:

Missing comma.

$sql .= 'C.WeedGrass, C.InopVeh, C.LawnPark, C.OpenStore, C.Furniture, C.ProAnimal, C.NoPermit, C.AnimalWaste, C.StormWater, C.Other, ';
<?php

$database="propmain";

mysql_connect ("localhost", "root", "");

@mysql_select_db($database) or die( "Unable to select database");

 

// run self join query to find duplicate records

 

$CPropRec = 12;

$EPropRec = 15;

 

$sql = 'SELECT ';

$sql .= 'C.RecNum, C.PropRec, C.Date, C.ReDate, C.Status, ';

$sql .= 'C.WeedGrass, C.InopVeh, C.LawnPark, C.OpenStore, C.Furniture, C.ProAnimal, C.NoPermit, C.AnimalWaste, C.StormWater, C.Other, ';

$sql .= 'E.RecNum as ERecNum, E.PropRec as EPropRec, E.Date as EDate, E.ReDate as EReDate, E.Status as EStatus, ';

$sql .= 'E.WeedGrass as EWeedGrass, E.InopVeh as EInopVech, E.LawnPark as ELawnPark, E.OpenStore as EOpenStore, E.Furniture as EFurniture, ';

$sql .= 'E.ProAnimal as EProAnimal, E.NoPermit as ENoPermit, E.AnimalWaste as EAnimalWaste, E.StormWater as EStormWater, E.Other as EOther, ';

$sql .= 'IF (C.Date >= E.Date) as isCMostRecent ';

$sql .= 'FROM insp C, insp E ';

$sql .= 'WHERE ';

$sql .= '(';

$SQL .= '(C.WeedGrass = E.WeedGrass) ';

$sql .= 'OR ';

$SQL .= '(C.InopVeh = E.InopVeh) ';

$sql .= 'OR ';

$SQL .= '(C.LawnPark = E.LawnPark) ';

$sql .= 'OR ';

$SQL .= '(C.OpenStore = E.OpenStore) ';

$sql .= 'OR ';

$SQL .= '(C.Furniture = E.Furniture) ';

$sql .= 'OR ';

$SQL .= '(C.ProAnimal = E.ProAnimal) ';

$sql .= 'OR ';

$SQL .= '(C.AnimalWastet = E.AnimalWaste) ';

$sql .= 'OR ';

$SQL .= '(C.StormWater = E.StormWater) ';

$sql .= 'OR ';

$SQL .= '(C.Other = E.Other)';

$sql .= ') ';

$sql .= 'AND ((c.PropRec = ' . $CPropRec . ') AND (E.PropRec = ' . $EPropRec . ')) ';

$sql .= 'ORDER BY C.RecNum, E.RecNum;';

 

$result = mysql_query($sql);

 

// output results

 

echo 'debug: <br />';

echo $sql . '<br />';

 

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

 echo 'Duplicate record found: RecNum = ' . $row['RecNum'] . ' & RecNum = ' . $row['ERecNum'];

 echo 'Most recent dated record is: ' . ($row['isCMostRecent'] ? $row['RecNum'] : $row['ERecNum']);

 echo 'debug: <br />';

 echo '<br /><br />';

 foreach ($row as $key => $value) {

   echo $key . ':' . $value . '<br />';

 }

}

?>

Open in new window

0
 

Author Comment

by:thedeal56
ID: 24034013
hmmm still giving the same error.
0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 24034030
Need output debug please
0
 

Author Comment

by:thedeal56
ID: 24034050
oops, sorry.  Is the the $SQL in caps?
debug:

SELECT C.RecNum, C.PropRec, C.Date, C.ReDate, C.Status, C.WeedGrass, C.InopVeh, C.LawnPark, C.OpenStore, C.Furniture, C.ProAnimal, C.NoPermit, C.AnimalWaste, C.StormWater, C.Other, E.RecNum as ERecNum, E.PropRec as EPropRec, E.Date as EDate, E.ReDate as EReDate, E.Status as EStatus, E.WeedGrass as EWeedGrass, E.InopVeh as EInopVech, E.LawnPark as ELawnPark, E.OpenStore as EOpenStore, E.Furniture as EFurniture, E.ProAnimal as EProAnimal, E.NoPermit as ENoPermit, E.AnimalWaste as EAnimalWaste, E.StormWater as EStormWater, E.Other as EOther, IF (C.Date >= E.Date) as isCMostRecent FROM insp C, insp E WHERE (OR OR OR OR OR OR OR OR ) AND ((c.PropRec = 12) AND (E.PropRec = 15)) ORDER BY C.RecNum, E.RecNum;
 

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in C:\wamp\www\Prop\record2.php on line 49

Open in new window

0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 24034062
Found it.

Logica error
Capitalization of variable
<?php

$database="propmain";

mysql_connect ("localhost", "root", "");

@mysql_select_db($database) or die( "Unable to select database");

 

// run self join query to find duplicate records

 

$CPropRec = 12;

$EPropRec = 15;

 

$sql = 'SELECT ';

$sql .= 'C.RecNum, C.PropRec, C.Date, C.ReDate, C.Status, ';

$sql .= 'C.WeedGrass, C.InopVeh, C.LawnPark, C.OpenStore, C.Furniture, C.ProAnimal, C.NoPermit, C.AnimalWaste, C.StormWater, C.Other, ';

$sql .= 'E.RecNum as ERecNum, E.PropRec as EPropRec, E.Date as EDate, E.ReDate as EReDate, E.Status as EStatus, ';

$sql .= 'E.WeedGrass as EWeedGrass, E.InopVeh as EInopVech, E.LawnPark as ELawnPark, E.OpenStore as EOpenStore, E.Furniture as EFurniture, ';

$sql .= 'E.ProAnimal as EProAnimal, E.NoPermit as ENoPermit, E.AnimalWaste as EAnimalWaste, E.StormWater as EStormWater, E.Other as EOther, ';

$sql .= 'IF (C.Date >= E.Date) as isCMostRecent ';

$sql .= 'FROM insp C, insp E ';

$sql .= 'WHERE ';

$sql .= '(';

$sql .= '(C.WeedGrass = E.WeedGrass) ';

$sql .= 'OR ';

$sql .= '(C.InopVeh = E.InopVeh) ';

$sql .= 'OR ';

$sql .= '(C.LawnPark = E.LawnPark) ';

$sql .= 'OR ';

$sql .= '(C.OpenStore = E.OpenStore) ';

$sql .= 'OR ';

$sql .= '(C.Furniture = E.Furniture) ';

$sql .= 'OR ';

$sql .= '(C.ProAnimal = E.ProAnimal) ';

$sql .= 'OR ';

$sql .= '(C.AnimalWastet = E.AnimalWaste) ';

$sql .= 'OR ';

$sql .= '(C.StormWater = E.StormWater) ';

$sql .= 'OR ';

$sql .= '(C.Other = E.Other)';

$sql .= ') ';

$sql .= 'AND ((c.PropRec = ' . $CPropRec . ') AND (E.PropRec = ' . $EPropRec . ')) ';

$sql .= 'ORDER BY C.RecNum, E.RecNum;';

 

$result = mysql_query($sql);

 

// output results

 

echo 'debug: <br />';

echo $sql . '<br />';

 

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

 echo 'Duplicate record found: RecNum = ' . $row['RecNum'] . ' & RecNum = ' . $row['ERecNum'];

 echo 'Most recent dated record is: ' . ($row['isCMostRecent'] ? $row['RecNum'] : $row['ERecNum']);

 echo 'debug: <br />';

 echo '<br /><br />';

 foreach ($row as $key => $value) {

   echo $key . ':' . $value . '<br />';

 }

}

?>

Open in new window

0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 24034071
One more found. Wait please,
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 24034076
Okay. I think that should do it.

$sql .= 'AND ((C.PropRec = ' . $CPropRec . ') AND (E.PropRec = ' . $EPropRec . ')) ';
<?php

$database="propmain";

mysql_connect ("localhost", "root", "");

@mysql_select_db($database) or die( "Unable to select database");

 

// run self join query to find duplicate records

 

$CPropRec = 12;

$EPropRec = 15;

 

$sql = 'SELECT ';

$sql .= 'C.RecNum, C.PropRec, C.Date, C.ReDate, C.Status, ';

$sql .= 'C.WeedGrass, C.InopVeh, C.LawnPark, C.OpenStore, C.Furniture, C.ProAnimal, C.NoPermit, C.AnimalWaste, C.StormWater, C.Other, ';

$sql .= 'E.RecNum as ERecNum, E.PropRec as EPropRec, E.Date as EDate, E.ReDate as EReDate, E.Status as EStatus, ';

$sql .= 'E.WeedGrass as EWeedGrass, E.InopVeh as EInopVech, E.LawnPark as ELawnPark, E.OpenStore as EOpenStore, E.Furniture as EFurniture, ';

$sql .= 'E.ProAnimal as EProAnimal, E.NoPermit as ENoPermit, E.AnimalWaste as EAnimalWaste, E.StormWater as EStormWater, E.Other as EOther, ';

$sql .= 'IF (C.Date >= E.Date) as isCMostRecent ';

$sql .= 'FROM insp C, insp E ';

$sql .= 'WHERE ';

$sql .= '(';

$sql .= '(C.WeedGrass = E.WeedGrass) ';

$sql .= 'OR ';

$sql .= '(C.InopVeh = E.InopVeh) ';

$sql .= 'OR ';

$sql .= '(C.LawnPark = E.LawnPark) ';

$sql .= 'OR ';

$sql .= '(C.OpenStore = E.OpenStore) ';

$sql .= 'OR ';

$sql .= '(C.Furniture = E.Furniture) ';

$sql .= 'OR ';

$sql .= '(C.ProAnimal = E.ProAnimal) ';

$sql .= 'OR ';

$sql .= '(C.AnimalWastet = E.AnimalWaste) ';

$sql .= 'OR ';

$sql .= '(C.StormWater = E.StormWater) ';

$sql .= 'OR ';

$sql .= '(C.Other = E.Other)';

$sql .= ') ';

$sql .= 'AND ((C.PropRec = ' . $CPropRec . ') AND (E.PropRec = ' . $EPropRec . ')) ';

$sql .= 'ORDER BY C.RecNum, E.RecNum;';

 

$result = mysql_query($sql);

 

// output results

 

echo 'debug: <br />';

echo $sql . '<br />';

 

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

 echo 'Duplicate record found: RecNum = ' . $row['RecNum'] . ' & RecNum = ' . $row['ERecNum'];

 echo 'Most recent dated record is: ' . ($row['isCMostRecent'] ? $row['RecNum'] : $row['ERecNum']);

 echo 'debug: <br />';

 echo '<br /><br />';

 foreach ($row as $key => $value) {

   echo $key . ':' . $value . '<br />';

 }

}

?>

Open in new window

0
 

Author Comment

by:thedeal56
ID: 24034115
It's still throwing out the error.
debug:

SELECT C.RecNum, C.PropRec, C.Date, C.ReDate, C.Status, C.WeedGrass, C.InopVeh, C.LawnPark, C.OpenStore, C.Furniture, C.ProAnimal, C.NoPermit, C.AnimalWaste, C.StormWater, C.Other, E.RecNum as ERecNum, E.PropRec as EPropRec, E.Date as EDate, E.ReDate as EReDate, E.Status as EStatus, E.WeedGrass as EWeedGrass, E.InopVeh as EInopVech, E.LawnPark as ELawnPark, E.OpenStore as EOpenStore, E.Furniture as EFurniture, E.ProAnimal as EProAnimal, E.NoPermit as ENoPermit, E.AnimalWaste as EAnimalWaste, E.StormWater as EStormWater, E.Other as EOther, IF (C.Date >= E.Date) as isCMostRecent FROM insp C, insp E WHERE ((C.WeedGrass = E.WeedGrass) OR (C.InopVeh = E.InopVeh) OR (C.LawnPark = E.LawnPark) OR (C.OpenStore = E.OpenStore) OR (C.Furniture = E.Furniture) OR (C.ProAnimal = E.ProAnimal) OR (C.AnimalWastet = E.AnimalWaste) OR (C.StormWater = E.StormWater) OR (C.Other = E.Other)) AND ((C.PropRec = 12) AND (E.PropRec = 15)) ORDER BY C.RecNum, E.RecNum;
 

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in C:\wamp\www\Prop\record2.php on line 49

Open in new window

0
 

Author Comment

by:thedeal56
ID: 24034168
In order to avoid null values reporting to be equal, could I add something like this after line 37 and before 38?

$sql .= 'AND (C.WeedGrass <> 0 and E.WeedGrass <> 0 and ...etc);



0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 24034173
Try this. output debug.
<?php

$database="propmain";

mysql_connect ("localhost", "root", "");

mysql_select_db($database) or die( "Unable to select database");

 

// run self join query to find duplicate records

 

$CPropRec = 12;

$EPropRec = 15;

 

$sql = 'SELECT ';

$sql .= 'C.RecNum, C.PropRec, C.Date, C.ReDate, C.Status, ';

$sql .= 'C.WeedGrass, C.InopVeh, C.LawnPark, C.OpenStore, C.Furniture, C.ProAnimal, C.NoPermit, C.AnimalWaste, C.StormWater, C.Other, ';

$sql .= 'E.RecNum as ERecNum, E.PropRec as EPropRec, E.Date as EDate, E.ReDate as EReDate, E.Status as EStatus, ';

$sql .= 'E.WeedGrass as EWeedGrass, E.InopVeh as EInopVech, E.LawnPark as ELawnPark, E.OpenStore as EOpenStore, E.Furniture as EFurniture, ';

$sql .= 'E.ProAnimal as EProAnimal, E.NoPermit as ENoPermit, E.AnimalWaste as EAnimalWaste, E.StormWater as EStormWater, E.Other as EOther, ';

$sql .= 'IF (C.Date >= E.Date) as isCMostRecent ';

$sql .= 'FROM insp C, insp E ';

$sql .= 'WHERE ';

$sql .= '(';

$sql .= '(C.WeedGrass = E.WeedGrass) ';

$sql .= 'OR ';

$sql .= '(C.InopVeh = E.InopVeh) ';

$sql .= 'OR ';

$sql .= '(C.LawnPark = E.LawnPark) ';

$sql .= 'OR ';

$sql .= '(C.OpenStore = E.OpenStore) ';

$sql .= 'OR ';

$sql .= '(C.Furniture = E.Furniture) ';

$sql .= 'OR ';

$sql .= '(C.ProAnimal = E.ProAnimal) ';

$sql .= 'OR ';

$sql .= '(C.AnimalWastet = E.AnimalWaste) ';

$sql .= 'OR ';

$sql .= '(C.StormWater = E.StormWater) ';

$sql .= 'OR ';

$sql .= '(C.Other = E.Other)';

$sql .= ') ';

$sql .= 'AND ((C.PropRec = ' . $CPropRec . ') AND (E.PropRec = ' . $EPropRec . ')) ';

$sql .= 'ORDER BY C.RecNum, E.RecNum;';

 

$result = mysql_query($sql) OR die('Invalid query: ' . mysql_error());

 

// output results

 

echo 'debug: <br />';

echo $sql . '<br />';

 

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

 echo 'Duplicate record found: RecNum = ' . $row['RecNum'] . ' & RecNum = ' . $row['ERecNum'];

 echo 'Most recent dated record is: ' . ($row['isCMostRecent'] ? $row['RecNum'] : $row['ERecNum']);

 echo 'debug: <br />';

 echo '<br /><br />';

 foreach ($row as $key => $value) {

   echo $key . ':' . $value . '<br />';

 }

}

?>

Open in new window

0
 

Author Comment

by:thedeal56
ID: 24034185

Invalid query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') as isCMostRecent FROM insp C, insp E WHERE ((C.WeedGrass = E.WeedGrass) OR (C.' at line 1

Open in new window

0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 24034189
You have NULL values for the violation fields?
0
 

Author Comment

by:thedeal56
ID: 24034202
They act as a switch.  a value of 0 is no violation, and a value of 1 indicates a violation. They can also have a value of 2, but it won't matter for this purpose.
0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 24034239
Syntax error on IF
Try this:
<?php

$database="propmain";

mysql_connect ("localhost", "root", "");

mysql_select_db($database) or die( "Unable to select database");

 

// run self join query to find duplicate records

 

$CPropRec = 12;

$EPropRec = 15;

 

$sql = 'SELECT ';

$sql .= 'C.RecNum, C.PropRec, C.Date, C.ReDate, C.Status, ';

$sql .= 'C.WeedGrass, C.InopVeh, C.LawnPark, C.OpenStore, C.Furniture, C.ProAnimal, C.NoPermit, C.AnimalWaste, C.StormWater, C.Other, ';

$sql .= 'E.RecNum as ERecNum, E.PropRec as EPropRec, E.Date as EDate, E.ReDate as EReDate, E.Status as EStatus, ';

$sql .= 'E.WeedGrass as EWeedGrass, E.InopVeh as EInopVech, E.LawnPark as ELawnPark, E.OpenStore as EOpenStore, E.Furniture as EFurniture, ';

$sql .= 'E.ProAnimal as EProAnimal, E.NoPermit as ENoPermit, E.AnimalWaste as EAnimalWaste, E.StormWater as EStormWater, E.Other as EOther, ';

$sql .= '(IF (C.Date >= E.Date, TRUE, FALSE) as isCMostRecent ';

$sql .= 'FROM insp C, insp E ';

$sql .= 'WHERE ';

$sql .= '(';

$sql .= '(C.WeedGrass = E.WeedGrass) ';

$sql .= 'OR ';

$sql .= '(C.InopVeh = E.InopVeh) ';

$sql .= 'OR ';

$sql .= '(C.LawnPark = E.LawnPark) ';

$sql .= 'OR ';

$sql .= '(C.OpenStore = E.OpenStore) ';

$sql .= 'OR ';

$sql .= '(C.Furniture = E.Furniture) ';

$sql .= 'OR ';

$sql .= '(C.ProAnimal = E.ProAnimal) ';

$sql .= 'OR ';

$sql .= '(C.AnimalWastet = E.AnimalWaste) ';

$sql .= 'OR ';

$sql .= '(C.StormWater = E.StormWater) ';

$sql .= 'OR ';

$sql .= '(C.Other = E.Other)';

$sql .= ') ';

$sql .= 'AND ((C.PropRec = ' . $CPropRec . ') AND (E.PropRec = ' . $EPropRec . ')) ';

$sql .= 'ORDER BY C.RecNum, E.RecNum;';

 

$result = mysql_query($sql);

 

// output results

 

echo 'debug: <br />';

echo $sql . '<br />';

 

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

 echo 'Duplicate record found: RecNum = ' . $row['RecNum'] . ' & RecNum = ' . $row['ERecNum'];

 echo 'Most recent dated record is: ' . ($row['isCMostRecent'] ? $row['RecNum'] : $row['ERecNum']);

 echo 'debug: <br />';

 echo '<br /><br />';

 foreach ($row as $key => $value) {

   echo $key . ':' . $value . '<br />';

 }

}

?>

Open in new window

0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 24034245
wait up sorry try this INSTEAD:
<?php

$database="propmain";

mysql_connect ("localhost", "root", "");

mysql_select_db($database) or die( "Unable to select database");

 

// run self join query to find duplicate records

 

$CPropRec = 12;

$EPropRec = 15;

 

$sql = 'SELECT ';

$sql .= 'C.RecNum, C.PropRec, C.Date, C.ReDate, C.Status, ';

$sql .= 'C.WeedGrass, C.InopVeh, C.LawnPark, C.OpenStore, C.Furniture, C.ProAnimal, C.NoPermit, C.AnimalWaste, C.StormWater, C.Other, ';

$sql .= 'E.RecNum as ERecNum, E.PropRec as EPropRec, E.Date as EDate, E.ReDate as EReDate, E.Status as EStatus, ';

$sql .= 'E.WeedGrass as EWeedGrass, E.InopVeh as EInopVech, E.LawnPark as ELawnPark, E.OpenStore as EOpenStore, E.Furniture as EFurniture, ';

$sql .= 'E.ProAnimal as EProAnimal, E.NoPermit as ENoPermit, E.AnimalWaste as EAnimalWaste, E.StormWater as EStormWater, E.Other as EOther, ';

$sql .= '(IF (C.Date >= E.Date, TRUE, FALSE)) as isCMostRecent ';

$sql .= 'FROM insp C, insp E ';

$sql .= 'WHERE ';

$sql .= '(';

$sql .= '(C.WeedGrass = E.WeedGrass) ';

$sql .= 'OR ';

$sql .= '(C.InopVeh = E.InopVeh) ';

$sql .= 'OR ';

$sql .= '(C.LawnPark = E.LawnPark) ';

$sql .= 'OR ';

$sql .= '(C.OpenStore = E.OpenStore) ';

$sql .= 'OR ';

$sql .= '(C.Furniture = E.Furniture) ';

$sql .= 'OR ';

$sql .= '(C.ProAnimal = E.ProAnimal) ';

$sql .= 'OR ';

$sql .= '(C.AnimalWastet = E.AnimalWaste) ';

$sql .= 'OR ';

$sql .= '(C.StormWater = E.StormWater) ';

$sql .= 'OR ';

$sql .= '(C.Other = E.Other)';

$sql .= ') ';

$sql .= 'AND ((C.PropRec = ' . $CPropRec . ') AND (E.PropRec = ' . $EPropRec . ')) ';

$sql .= 'ORDER BY C.RecNum, E.RecNum;';

 

$result = mysql_query($sql);

 

// output results

 

echo 'debug: <br />';

echo $sql . '<br />';

 

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

 echo 'Duplicate record found: RecNum = ' . $row['RecNum'] . ' & RecNum = ' . $row['ERecNum'];

 echo 'Most recent dated record is: ' . ($row['isCMostRecent'] ? $row['RecNum'] : $row['ERecNum']);

 echo 'debug: <br />';

 echo '<br /><br />';

 foreach ($row as $key => $value) {

   echo $key . ':' . $value . '<br />';

 }

}

?>

Open in new window

0
 

Author Comment

by:thedeal56
ID: 24034264
ah, I thought for sure that was it
debug:

SELECT C.RecNum, C.PropRec, C.Date, C.ReDate, C.Status, C.WeedGrass, C.InopVeh, C.LawnPark, C.OpenStore, C.Furniture, C.ProAnimal, C.NoPermit, C.AnimalWaste, C.StormWater, C.Other, E.RecNum as ERecNum, E.PropRec as EPropRec, E.Date as EDate, E.ReDate as EReDate, E.Status as EStatus, E.WeedGrass as EWeedGrass, E.InopVeh as EInopVech, E.LawnPark as ELawnPark, E.OpenStore as EOpenStore, E.Furniture as EFurniture, E.ProAnimal as EProAnimal, E.NoPermit as ENoPermit, E.AnimalWaste as EAnimalWaste, E.StormWater as EStormWater, E.Other as EOther, (IF (C.Date >= E.Date, TRUE, FALSE)) as isCMostRecent FROM insp C, insp E WHERE ((C.WeedGrass = E.WeedGrass) OR (C.InopVeh = E.InopVeh) OR (C.LawnPark = E.LawnPark) OR (C.OpenStore = E.OpenStore) OR (C.Furniture = E.Furniture) OR (C.ProAnimal = E.ProAnimal) OR (C.AnimalWastet = E.AnimalWaste) OR (C.StormWater = E.StormWater) OR (C.Other = E.Other)) AND ((C.PropRec = 12) AND (E.PropRec = 15)) ORDER BY C.RecNum, E.RecNum;
 

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in C:\wamp\www\Prop\record2.php on line 49

Open in new window

0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 24034281
One last change :)
0
 

Author Comment

by:thedeal56
ID: 24034285
I have to head home.  I won't be able to post for a bit.  Thanks for sticking this out;  that is really cool of you.  
0
 
LVL 19

Accepted Solution

by:
NerdsOfTech earned 500 total points
ID: 24034322
Whenever you get back try this out

Also included NULL safe compares
<?php

$database="propmain";

mysql_connect ("localhost", "root", "");

mysql_select_db($database) or die( "Unable to select database");

 

// run self join query to find duplicate records

 

$CPropRec = 12;

$EPropRec = 15;

 

$sql = 'SELECT ';

$sql .= 'C.RecNum, C.PropRec, C.Date, C.ReDate, C.Status, ';

$sql .= 'C.WeedGrass, C.InopVeh, C.LawnPark, C.OpenStore, C.Furniture, C.ProAnimal, C.NoPermit, C.AnimalWaste, C.StormWater, C.Other, ';

$sql .= 'E.RecNum as ERecNum, E.PropRec as EPropRec, E.Date as EDate, E.ReDate as EReDate, E.Status as EStatus, ';

$sql .= 'E.WeedGrass as EWeedGrass, E.InopVeh as EInopVech, E.LawnPark as ELawnPark, E.OpenStore as EOpenStore, E.Furniture as EFurniture, ';

$sql .= 'E.ProAnimal as EProAnimal, E.NoPermit as ENoPermit, E.AnimalWaste as EAnimalWaste, E.StormWater as EStormWater, E.Other as EOther, ';

$sql .= '(IF (C.Date >= E.Date, TRUE, FALSE)) as isCMostRecent ';

$sql .= 'FROM insp C, insp E ';

$sql .= 'WHERE ';

$sql .= '(';

$sql .= '(C.WeedGrass <=> E.WeedGrass) ';

$sql .= 'OR ';

$sql .= '(C.InopVeh <=> E.InopVeh) ';

$sql .= 'OR ';

$sql .= '(C.LawnPark <=> E.LawnPark) ';

$sql .= 'OR ';

$sql .= '(C.OpenStore <=> E.OpenStore) ';

$sql .= 'OR ';

$sql .= '(C.Furniture <=> E.Furniture) ';

$sql .= 'OR ';

$sql .= '(C.ProAnimal <=> E.ProAnimal) ';

$sql .= 'OR ';

$sql .= '(C.AnimalWastet <=> E.AnimalWaste) ';

$sql .= 'OR ';

$sql .= '(C.StormWater <=> E.StormWater) ';

$sql .= 'OR ';

$sql .= '(C.Other <=> E.Other)';

$sql .= ') ';

$sql .= 'AND ((C.PropRec = ' . $CPropRec . ') AND (E.PropRec = ' . $EPropRec . ')) ';

$sql .= 'ORDER BY C.RecNum, E.RecNum;';

 

$result = mysql_query($sql) OR die('Invalid query: ' . mysql_error());

 

// output results

 

echo 'debug: <br />';

echo $sql . '<br />';

 

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

 echo 'Duplicate record found: RecNum = ' . $row['RecNum'] . ' & RecNum = ' . $row['ERecNum'];

 echo 'Most recent dated record is: ' . ($row['isCMostRecent'] ? $row['RecNum'] : $row['ERecNum']);

 echo 'debug: <br />';

 echo '<br /><br />';

 foreach ($row as $key => $value) {

   echo $key . ':' . $value . '<br />';

 }

}

?>

Open in new window

0
 

Author Comment

by:thedeal56
ID: 24034803
That's awesome.  The error is gone, and I can see the results.  It's counting everything as a duplicate, though.  It probably has everything to do with the null values. I'll have to check into more tomorrow.  
debug:

SELECT C.RecNum, C.PropRec, C.Date, C.ReDate, C.Status, C.WeedGrass, C.InopVeh, C.LawnPark, C.OpenStore, C.Furniture, C.ProAnimal, C.NoPermit, C.AnimalWaste, C.StormWater, C.Other, E.RecNum as ERecNum, E.PropRec as EPropRec, E.Date as EDate, E.ReDate as EReDate, E.Status as EStatus, E.WeedGrass as EWeedGrass, E.InopVeh as EInopVech, E.LawnPark as ELawnPark, E.OpenStore as EOpenStore, E.Furniture as EFurniture, E.ProAnimal as EProAnimal, E.NoPermit as ENoPermit, E.AnimalWaste as EAnimalWaste, E.StormWater as EStormWater, E.Other as EOther, (IF (C.Date >= E.Date, TRUE, FALSE)) as isCMostRecent FROM insp C, insp E WHERE ((C.WeedGrass <=> E.WeedGrass) OR (C.InopVeh <=> E.InopVeh) OR (C.LawnPark <=> E.LawnPark) OR (C.OpenStore <=> E.OpenStore) OR (C.Furniture <=> E.Furniture) OR (C.ProAnimal <=> E.ProAnimal) OR (C.AnimalWaste <=> E.AnimalWaste) OR (C.StormWater <=> E.StormWater) ) AND ((C.PropRec = 12) AND (E.PropRec = 15)) ORDER BY C.RecNum, E.RecNum;

Duplicate record found: RecNum = 131 & RecNum = 139Most recent dated record is: 139debug:
 
 

RecNum:131

PropRec:12

Date:2009-03-03

ReDate:2009-03-17

Status:Failed

WeedGrass:1

InopVeh:0

LawnPark:0

OpenStore:0

Furniture:0

ProAnimal:0

NoPermit:0

AnimalWaste:0

StormWater:1

Other:leaves

ERecNum:139

EPropRec:15

EDate:2009-03-04

EReDate:

EStatus:Pass

EWeedGrass:0

EInopVech:0

ELawnPark:0

EOpenStore:0

EFurniture:1

EProAnimal:0

ENoPermit:0

EAnimalWaste:0

EStormWater:0

EOther:test

isCMostRecent:0

Duplicate record found: RecNum = 131 & RecNum = 140Most recent dated record is: 140debug:
 
 

RecNum:131

PropRec:12

Date:2009-03-03

ReDate:2009-03-17

Status:Failed

WeedGrass:1

InopVeh:0

LawnPark:0

OpenStore:0

Furniture:0

ProAnimal:0

NoPermit:0

AnimalWaste:0

StormWater:1

Other:leaves

ERecNum:140

EPropRec:15

EDate:2009-03-04

EReDate:

EStatus:Pass

EWeedGrass:0

EInopVech:0

ELawnPark:0

EOpenStore:0

EFurniture:0

EProAnimal:0

ENoPermit:0

EAnimalWaste:0

EStormWater:0

EOther:leaves

isCMostRecent:0

Duplicate record found: RecNum = 133 & RecNum = 139Most recent dated record is: 139debug:
 
 

RecNum:133

PropRec:12

Date:2009-03-03

ReDate:

Status:Plus

WeedGrass:0

InopVeh:0

LawnPark:1

OpenStore:0

Furniture:0

ProAnimal:0

NoPermit:0

AnimalWaste:0

StormWater:0

Other:

ERecNum:139

EPropRec:15

EDate:2009-03-04

EReDate:

EStatus:Pass

EWeedGrass:0

EInopVech:0

ELawnPark:0

EOpenStore:0

EFurniture:1

EProAnimal:0

ENoPermit:0

EAnimalWaste:0

EStormWater:0

EOther:test

isCMostRecent:0

Duplicate record found: RecNum = 133 & RecNum = 140Most recent dated record is: 140debug:
 
 

RecNum:133

PropRec:12

Date:2009-03-03

ReDate:

Status:Plus

WeedGrass:0

InopVeh:0

LawnPark:1

OpenStore:0

Furniture:0

ProAnimal:0

NoPermit:0

AnimalWaste:0

StormWater:0

Other:

ERecNum:140

EPropRec:15

EDate:2009-03-04

EReDate:

EStatus:Pass

EWeedGrass:0

EInopVech:0

ELawnPark:0

EOpenStore:0

EFurniture:0

EProAnimal:0

ENoPermit:0

EAnimalWaste:0

EStormWater:0

EOther:leaves

isCMostRecent:0

Duplicate record found: RecNum = 134 & RecNum = 139Most recent dated record is: 139debug:
 
 

RecNum:134

PropRec:12

Date:2009-03-01

ReDate:

Status:Plus

WeedGrass:0

InopVeh:0

LawnPark:0

OpenStore:0

Furniture:1

ProAnimal:0

NoPermit:0

AnimalWaste:0

StormWater:0

Other:test2

ERecNum:139

EPropRec:15

EDate:2009-03-04

EReDate:

EStatus:Pass

EWeedGrass:0

EInopVech:0

ELawnPark:0

EOpenStore:0

EFurniture:1

EProAnimal:0

ENoPermit:0

EAnimalWaste:0

EStormWater:0

EOther:test

isCMostRecent:0

Duplicate record found: RecNum = 134 & RecNum = 140Most recent dated record is: 140debug:
 
 

RecNum:134

PropRec:12

Date:2009-03-01

ReDate:

Status:Plus

WeedGrass:0

InopVeh:0

LawnPark:0

OpenStore:0

Furniture:1

ProAnimal:0

NoPermit:0

AnimalWaste:0

StormWater:0

Other:test2

ERecNum:140

EPropRec:15

EDate:2009-03-04

EReDate:

EStatus:Pass

EWeedGrass:0

EInopVech:0

ELawnPark:0

EOpenStore:0

EFurniture:0

EProAnimal:0

ENoPermit:0

EAnimalWaste:0

EStormWater:0

EOther:leaves

isCMostRecent:0

Duplicate record found: RecNum = 135 & RecNum = 139Most recent dated record is: 135debug:
 
 

RecNum:135

PropRec:12

Date:2009-03-13

ReDate:

Status:Plus

WeedGrass:0

InopVeh:1

LawnPark:0

OpenStore:1

Furniture:0

ProAnimal:1

NoPermit:1

AnimalWaste:1

StormWater:0

Other:

ERecNum:139

EPropRec:15

EDate:2009-03-04

EReDate:

EStatus:Pass

EWeedGrass:0

EInopVech:0

ELawnPark:0

EOpenStore:0

EFurniture:1

EProAnimal:0

ENoPermit:0

EAnimalWaste:0

EStormWater:0

EOther:test

isCMostRecent:1

Duplicate record found: RecNum = 135 & RecNum = 140Most recent dated record is: 135debug:
 
 

RecNum:135

PropRec:12

Date:2009-03-13

ReDate:

Status:Plus

WeedGrass:0

InopVeh:1

LawnPark:0

OpenStore:1

Furniture:0

ProAnimal:1

NoPermit:1

AnimalWaste:1

StormWater:0

Other:

ERecNum:140

EPropRec:15

EDate:2009-03-04

EReDate:

EStatus:Pass

EWeedGrass:0

EInopVech:0

ELawnPark:0

EOpenStore:0

EFurniture:0

EProAnimal:0

ENoPermit:0

EAnimalWaste:0

EStormWater:0

EOther:leaves

isCMostRecent:1

Duplicate record found: RecNum = 136 & RecNum = 139Most recent dated record is: 139debug:
 
 

RecNum:136

PropRec:12

Date:2009-03-01

ReDate:2009-03-19

Status:Failed

WeedGrass:0

InopVeh:0

LawnPark:0

OpenStore:0

Furniture:0

ProAnimal:0

NoPermit:0

AnimalWaste:0

StormWater:0

Other:lights

ERecNum:139

EPropRec:15

EDate:2009-03-04

EReDate:

EStatus:Pass

EWeedGrass:0

EInopVech:0

ELawnPark:0

EOpenStore:0

EFurniture:1

EProAnimal:0

ENoPermit:0

EAnimalWaste:0

EStormWater:0

EOther:test

isCMostRecent:0

Duplicate record found: RecNum = 136 & RecNum = 140Most recent dated record is: 140debug:
 
 

RecNum:136

PropRec:12

Date:2009-03-01

ReDate:2009-03-19

Status:Failed

WeedGrass:0

InopVeh:0

LawnPark:0

OpenStore:0

Furniture:0

ProAnimal:0

NoPermit:0

AnimalWaste:0

StormWater:0

Other:lights

ERecNum:140

EPropRec:15

EDate:2009-03-04

EReDate:

EStatus:Pass

EWeedGrass:0

EInopVech:0

ELawnPark:0

EOpenStore:0

EFurniture:0

EProAnimal:0

ENoPermit:0

EAnimalWaste:0

EStormWater:0

EOther:leaves

isCMostRecent:0

Open in new window

0
 

Author Comment

by:thedeal56
ID: 24034892
I changed it to this, and it seems to be working.  I'll have to test it out more, though.  Do you see any potential problems with it?
<?php

$database="propmain";

mysql_connect ("localhost", "root", "");

mysql_select_db($database) or die( "Unable to select database");

 

// run self join query to find duplicate records

 

$CPropRec = 12;

$EPropRec = 15;

 

$sql = 'SELECT ';

$sql .= 'C.RecNum, C.PropRec, C.Date, C.ReDate, C.Status, ';

$sql .= 'C.WeedGrass, C.InopVeh, C.LawnPark, C.OpenStore, C.Furniture, C.ProAnimal, C.NoPermit, C.AnimalWaste, C.StormWater, C.Other, ';

$sql .= 'E.RecNum as ERecNum, E.PropRec as EPropRec, E.Date as EDate, E.ReDate as EReDate, E.Status as EStatus, ';

$sql .= 'E.WeedGrass as EWeedGrass, E.InopVeh as EInopVech, E.LawnPark as ELawnPark, E.OpenStore as EOpenStore, E.Furniture as EFurniture, ';

$sql .= 'E.ProAnimal as EProAnimal, E.NoPermit as ENoPermit, E.AnimalWaste as EAnimalWaste, E.StormWater as EStormWater, E.Other as EOther, ';

$sql .= '(IF (C.Date >= E.Date, TRUE, FALSE)) as isCMostRecent ';

$sql .= 'FROM insp C, insp E ';

$sql .= 'WHERE ';

$sql .= '(';

$sql .= '(C.WeedGrass = E.WeedGrass and C.WeedGrass <> 0 and E.WeedGrass <> 0) ';

$sql .= 'OR ';

$sql .= '(C.InopVeh = E.InopVeh and C.InopVeh <> 0 and E.InopVeh <> 0 ) ';

$sql .= 'OR ';

$sql .= '(C.LawnPark = E.LawnPark and C.LawnPark <> 0 and E.LawnPark <> 0) ';

$sql .= 'OR ';

$sql .= '(C.OpenStore = E.OpenStore and C.OpenStore <> 0 and E.OpenStore <> 0 ) ';

$sql .= 'OR ';

$sql .= '(C.Furniture = E.Furniture and C.Furniture <> 0 and E.Furniture <> 0) ';

$sql .= 'OR ';

$sql .= '(C.ProAnimal = E.ProAnimal and C.ProAnimal <> 0 and E.ProAnimal <> 0) ';

$sql .= 'OR ';

$sql .= '(C.AnimalWaste = E.AnimalWaste and C.AnimalWaste <> 0 and E.AnimalWaste <> 0) ';

$sql .= 'OR ';

$sql .= '(C.StormWater = E.StormWater and C.StormWater <> 0 and E.StormWater <> 0) ';

$sql .= 'OR ';

$sql .= "(C.Other = E.Other and C.Other <> '' and E.Other <> '' )";

$sql .= ') ';

$sql .= 'AND ((C.PropRec = ' . $CPropRec . ') AND (E.PropRec = ' . $EPropRec . ')) ';

$sql .= 'ORDER BY C.RecNum, E.RecNum;';

 

$result = mysql_query($sql) OR die('Invalid query: ' . mysql_error());

 

// output results

 

echo 'debug: <br />';

echo $sql . '<br />';

 

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

 echo 'Duplicate record found: RecNum = ' . $row['RecNum'] . ' & RecNum = ' . $row['ERecNum'];

 echo 'Most recent dated record is: ' . ($row['isCMostRecent'] ? $row['RecNum'] : $row['ERecNum']);

 echo 'debug: <br />';

 echo '<br /><br />';

 foreach ($row as $key => $value) {

   echo $key . ':' . $value . '<br />';

 }

}

?>

Open in new window

0
 

Author Comment

by:thedeal56
ID: 24039034
I have one more question, and it relates to the "Other" column.  Is it cool if I ask it on this post, or should I make a new question?
0
 

Author Comment

by:thedeal56
ID: 24039188
Never mind on that, I got it figured out
0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 24053527
Looks great good job!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now