[RESOLVED] Really strange bug. Can't seem to kill it.

[minifairy]

Background: searching for duplicates in a contacts table. The table primary key is an auto increment since there could be more than one business with the same name in the table and still not be a duplicate.

So here is how I went about identifying duplicates:
step 1: Pull all of the contacts in bus name order for the shop in question. (this is for shop 1 that is filled by the program based on who is doing the query.

$query="select * from cust use index (busname) where shop='1' " order by bus asc";

Step 2: Step through this list testing if the business name matches the previously set business name. If it does we go to the next record if it doesn't we pull all the records with that business name and print them so the person can decide if they are true duplicates or valid businesses with the same name.

$result = querymeres($query);
while($row=$result->fetch_object()){
if($row->bus !=$bus){#check to see if this row is the same business as the last one
$bus=$row->bus;
$x=1;
$query2="select * from cust use index (busname) where shop='1' and bus='".$row->bus."' order by id";
$result2 = querymeres($query2);
if($result2->num_rows>=2){
echo "<table>\n";
while($row2=$result2->fetch_object()){
#Print out the matching records in a table here#
}
echo "</table>\n";

			}

		}
	}[/QUOTE]

THE PROBLEM: So on the surface it seems to work fine, however, every so often it will print the same group of duplicate businesses twice. Not always just sometimes. There are quite a few where there are two with the same name in the table. In most of these it will print the two matching records and then go on to the next business name. But every so often it will print the two matching ones then print the same two matching ones again. Not sure if it does it with groups of 3 (not near as many of them in the table to start with)

The really strange thing is that I can't find anything different in the table or the business names that is remarkable on the ones that it does this to but it does it every time on the same groups. Very weird :bemused:

Any ideas.

[schwim]

Tabs FTW:

$query="select * from `cust` use index (busname) where `shop`='1' order by `bus` asc";
$result = querymeres($query);
while($row=$result->fetch_object()){
 if($row->bus !=$bus){
	$bus=$row->bus;
	$x=1;
	$query2="select * from `cust` use index (busname) where `shop`='1' and `bus`='".$row->bus."' order by `id`";
	$result2 = querymeres($query2);
	if($result2->num_rows>=2){
		echo "<table>\n";
		while($row2=$result2->fetch_object()){
			#Print out the matching records in a table here#
		}
		echo "</table>\n";
	}

}

On the face of it, I don't see a mechanic in place to keep the script from trying to find a match for the first and then the second instance of a business with the same name. By that, I mean, if you have three records with the same business name, ID of 1, 5 and 9, then it loops through one, presents the match, 5, presents the match and then at 9, do it again. You can end up matching the same records more than once.

The reason it's probably not happening all the time is because of what you're doing when they are dupes(removing that dupe ID before it gets looped maybe). This is of course conjecture, as I don't know what your script is doing with those.

[minifairy]

No the dupes are not removed at the time it is just a list presented to the person to decide.

What is checking for the matching record

if($row->bus !=$bus){

As you step through the list

(assuming on your example that we have a 1, 5, and 9 ids of matching businesses.)

bear in mind that I have asked for the result of the initial data in order by bus. So records 1,5,and 9 will be grouped together.

so on the first loop of the while I ask

if($row->bus !=$bus){

(lets assume this is id 1) the answer is NO so then I set $bus=$row->bus and proceed to do the second query to pull just the records with that business name and print them all in a table.
then I check the next record in the first loop

if($row->bus !=$bus){

(this is id 5) and the answer this time is YES so we skip the printing etc and go to the third record in the first loop and do it again. and the answer should again be YES Now when we get the next row in the first While statement we are asking again and the answer should be again NO (we have already stepped through the three matching records 1,5, and 9) So we now set $bus=to the new $row->bus and continue on.

this works fine about 80% of the time but for some reason sometimes it doesn't. In the cases I've seen it has been cases of 2 matching records. For some reason it does the

if($row->bus !=$bus){

On the first record it says NO as expected, goes through and prints the two records. The on the second record it for some reason says NO again and prints the same two records again. If it did it every time I would understand but it doesn't. I can have a page of say 20 results of pairings of two duplicates that only print once along with a few with 3 duplicates that have also only printed once but one of the pairings of duplicates prints twice. Have no idea why.

[minifairy]

Here is an example of one such page of results. The duplicates are grouped together with no space then a space before the next group. You can see they are working fine down to the Nail Depot And Spa which is presented twice. The number before the business name is the id number in the table for that record. Only thing I can see is that one of the Nail Depot records has the name in all Caps but it does pick it up as a match or it wouldn't show up at all. Also on other pages where this duplicating of duplicates occurred this wasnt the case.

3579 Metro Hearing
Dan Trimble
View item Edit item Delete item
4045 E Bell Rd # 135
Phoenix , AZ 85032
[phone]
4269 Metro Hearing
Stephen Teodoro
View item Edit item Delete item
13657 W Mcdowell Rd # 200
Goodyear , AZ 85395
[phone]

3880 Montessori Rainbow School
View item Edit item Delete item
6402 E Voltaire Ave
Scottsdale , AZ 85254
[phone]
3881 Montessori Rainbow School
View item Edit item Delete item
6520 E Cactus Rd
Scottsdale , AZ 85254
[phone]

333 Nail Depot And Spa
View item Edit item Delete item
13375 W Grand Ave
Surprise, AZ 85374 Nail Salon
[phone]
644 NAIL DEPOT AND SPA
AMY BAO
View item Edit item Delete item
6030 W BEHREND DR STE 131
GLENDALE, AZ 85308 Nail Salon
[phone]

333 Nail Depot And Spa
View item Edit item Delete item
13375 W Grand Ave
Surprise, AZ 85374 Nail Salon
[phone]
644 NAIL DEPOT AND SPA
AMY BAO
View item Edit item Delete item
6030 W BEHREND DR STE 131
GLENDALE, AZ 85308 Nail Salon
[phone]

570 National Future Benefits, Inc.
Jonathan Radow
View item Edit item Delete item
7150 E. Camelback Road, Suite 333
Scottsdale, AZ 85251 Financial Advisor
480-367-1533
571 National Future Benefits, Inc.
Jonathan Radow
View item Edit item Delete item
7636 N. Oracle Road
Oro Valley, AZ 85704 Financial Advisor
480-367-1533

510 Native New Yorker Restaurant
View item Edit item Delete item
530 N. Estrella Parkway
Goodyear, AZ 85338 Restaurant
623-882-0022
511 Native New Yorker Restaurant
View item Edit item Delete item
99th Ave and McDowell
Avondale, AZ 85323 Restaurant
623-907-8181
512 Native New Yorker Restaurant
View item Edit item Delete item

Glendale, AZ 85307 Restaurant

[Weedpacket]

minifairy wrote:

Only thing I can see is that one of the Nail Depot records has the name in all Caps but it does pick it up as a match or it wouldn't show up at all.

Well, the database may recognise it as a match (depending on things like the column's collation), but if you ask PHP (as in the line [font=monospace]if($row->bus !=$bus)[/font] it would tell you that [font=monospace]"Nail Depot and And Spa" != "NAIL DEPOT AND SPA"[/font]. Maybe running [font=monospace]$row->bus = ucwords($row->bus);[/font] just before the test will normalise things properly.

I get leery at the sight of queries inside loops that might run multiple times. There probably aren't that many duplicates here so it's not likely it will run that many times, but it still suggests that some work might still be better off moved onto the DBMS.

For example, [font=monospace]SELECT bus, COUNT(*) as dups FROM cust USE INDEX(busname) WHERE shop=1 GROUP BY bus HAVING dups>1 ORDER BY bus[/font] would whittle down the original list to only those that do appear to have duplicates in the first place - and would only list each once.

That won't remove the loop, but it will get past the need for having to check the "bus" in each row with the "bus" in the previous row, so that looks like a win so far.

Since the "bus" is the only column used from the first query (so it should have been specified explicitly instead of using "", incidentally), and (after the above change) is only used within another query, one can have the DBMS query the whole table of matches at once instead of asking it to search for one row at a time.

SELECT * from cust USE INDEX(busname) WHERE shop=1 AND bus IN (SELECT bus FROM cust USE INDEX(busname) WHERE shop=1 GROUP BY bus HAVING COUNT(*)>1) ORDER BY bus,id

Which does all the required database stuff in one query, but does reintroduce the need to compare each row with the previous to see when the busname changes.

[minifairy]

I was thinking about that but not all of the instances are similar some are identical in every way at least as far as I can tell.
Here is another section of the output. Notice the top two groups. Spelling is identical as is the upper lower case and I already looked for trailing spaces as well.

193 Nakama
View item Edit item Delete item
13215 W Mcdowell Rd Ste D3
Avondale, AZ 85392 Restaurant
[phone]
4244 Nakama
Rey Maldonado
View item Edit item Delete item
13215 W Mcdowell Rd # D3
Goodyear , AZ 85395
[phone]

193 Nakama
View item Edit item Delete item
13215 W Mcdowell Rd Ste D3
Avondale, AZ 85392 Restaurant
[phone]
4244 Nakama
Rey Maldonado
View item Edit item Delete item
13215 W Mcdowell Rd # D3
Goodyear , AZ 85395
[phone]

570 National Future Benefits, Inc.
Jonathan Radow
View item Edit item Delete item
7150 E. Camelback Road, Suite 333
Scottsdale, AZ 85251 Financial Advisor
480-367-1533
571 National Future Benefits, Inc.
Jonathan Radow
View item Edit item Delete item
7636 N. Oracle Road
Oro Valley, AZ 85704 Financial Advisor
480-367-1533

2997 Nationwide Electric
Dave Tapley
View item Edit item Delete item
2707 E Michigan Ave
Phoenix , AZ 85032
[phone]
3586 Nationwide Electric
Dave Tapley
View item Edit item Delete item
2707 E Michigan Ave
Phoenix , AZ 85032
[phone]

510 Native New Yorker Restaurant
View item Edit item Delete item
530 N. Estrella Parkway
Goodyear, AZ 85338 Restaurant
623-882-0022
511 Native New Yorker Restaurant
View item Edit item Delete item
99th Ave and McDowell
Avondale, AZ 85323 Restaurant
623-907-8181
512 Native New Yorker Restaurant
View item Edit item Delete item

Glendale, AZ 85307 Restaurant
623-877-4500

3014 Neat Cleaning Systems Inc
View item Edit item Delete item
10610 N Scottsdale Rd
Scottsdale , AZ 85254
[phone]
3802 Neat Cleaning Systems Inc
View item Edit item Delete item
10610 N Scottsdale Rd
Scottsdale , AZ 85254
[phone]

3018 Nelsen Architects
Brad Nelsen
View item Edit item Delete item
15210 N Scottsdale Rd # 300
Scottsdale , AZ 85254
[phone]
3888 Nelsen Architects
Brad Nelsen
View item Edit item Delete item
15210 N Scottsdale Rd # 300
Scottsdale , AZ 85254
[phone]

4859 Nelson Holland Inc
Dan Heinz
View item Edit item Delete item
5330 N 16th St
Phoenix , AZ 85016
[phone]
5132 Nelson Holland Inc
Dan Heinz
View item Edit item Delete item
5330 N 16th St
Phoenix , AZ 85016
[phone]

[minifairy]

And another section. Notice Avondale Cleaner but above and below working fine. I'm stumped.

5192 ATV Racing
Neil Affinito
View item Edit item Delete item
22033 N 24th Ave
Phoenix , AZ 85027
[phone]
5212 ATV Racing
Neil Affinito
View item Edit item Delete item
22033 N 24th Ave
Phoenix , AZ 85027
[phone]

4169 Avondale Appliance Repair
Irving Almazan
View item Edit item Delete item
1619 N Dysart Rd # 105
Avondale , AZ 85392
[phone]
4225 Avondale Appliance Repair
Irving Almazan
View item Edit item Delete item
1619 N Dysart Rd # 105
Avondale , AZ 85392
[phone]

4403 Avondale Assisted Living LLC
Trudy A Skousen
View item Edit item Delete item
2302 S 113th Ave
Avondale , AZ 85323
[phone]
4647 Avondale Assisted Living LLC
Trudy A Skousen
View item Edit item Delete item
2302 S 113th Ave
Avondale , AZ 85323
[phone]

4378 Avondale Building Inspection
View item Edit item Delete item
1225 S 4th St
Avondale , AZ 85323
[phone]
4622 Avondale Building Inspection
View item Edit item Delete item
1225 S 4th St
Avondale , AZ 85323
[phone]

175 Avondale Cleaner
View item Edit item Delete item
1579 N Dysart Rd Ste F
Avondale, AZ 85392 Dry Cleaner
[phone]
4166 Avondale Cleaner
VI Phuong
View item Edit item Delete item
1579 N Dysart Rd # A
Avondale , AZ 85392
[phone]

175 Avondale Cleaner
View item Edit item Delete item
1579 N Dysart Rd Ste F
Avondale, AZ 85392 Dry Cleaner
[phone]
4166 Avondale Cleaner
VI Phuong
View item Edit item Delete item
1579 N Dysart Rd # A
Avondale , AZ 85392
[phone]

4376 Avondale Finance & Budget
Kevin Artz
View item Edit item Delete item
11465 W Civic Center Dr # 250
Avondale , AZ 85323
[phone]
4620 Avondale Finance & Budget
Kevin Artz
View item Edit item Delete item
11465 W Civic Center Dr # 250
Avondale , AZ 85323
[phone]

4448 Avondale Garage Doors Inc
Jeff Hanes
View item Edit item Delete item
9950 W Van Buren St # 110
Avondale , AZ 85323
[phone]
4692 Avondale Garage Doors Inc
Jeff Hanes
View item Edit item Delete item
9950 W Van Buren St # 110
Avondale , AZ 85323
[phone]

[johanafm]

This is where you'd want to be able to trace the program.

1. set a conditional breakpoint at your if condition, so that it breaks execution for one group that works like it should and one that doesn't.
2. execute program
3. when the breakpoint stops execution, step through the program statement by statement until you are through the inner loop
4. resume execution so that it runs until the next time the breakpoint condition is met.

This way you can see exactly what happens.

But I would still recommend that you follow weedpackets advice and change the logic so that you execute one (or perhaps two sql queries) which produces the final results. No php-looping to execute nested queries needed. It will be easier to get right, faster to execute, easier to understand if you one day come back to fix / change or even just read + understand.

[minifairy]

Thanks Weedpacket. Didn't see your response before. Odd. It did do the trick. I've never seen as select inside a select like that before. Never had trouble with a nested loop like this before either. At any rate its fixed now thanks a bunch.

[dalecosp]

minifairy;11047403 wrote:

Never had trouble with a nested loop like this before either.

One place that these are trouble, as Weedpacket hinted, is when you're pushing an app to scale, and start wondering why it's taking so long ....