Categories > TinyButStrong general >

How to eliminate duplicate records

The forum is closed. Please use Stack Overflow for submitting new questions. Use tags: tinybutstrong , opentbs
By: Tom
Date: 2005-11-05
Time: 22:03

How to eliminate duplicate records

Hello,
[[newbie warning]]
Background:
My contacts database has field for first letter of named contacts -- so there are many duplicates when sql = "SELECT lettername FROM contacts ORDER BY lettername" ((yes I know that I could do "SELECT DISTINCT lettername FROM contacts ORDER BY lettername" and in fact when I do the "SLECT DISTINCT..." way  there are no duplicates and the result is as needed - but that would not help me learn to solve this ;-) )

My .html looks like:
<tr><td valign=bottom> [blk2.lettername;block=td;ondata=f_distinct_blk2]
</td></tr>

My .php looks like:
function f_distinct_blk2($BlockName,&$CurrRec,$RecNum) {
       // this iten is diff from previous - so keep it
  if( $CurrRec['lettername'] != $CurrRec[($RecNum-1)]) {
       $CurrRec['lettername']="(".$CurrRec['lettername'].")";
   }else{
       // this one is not different - so get rid of it
        $CurrRec['lettername']=false;
   }
}

The results are like (see duplicates):
(C) (C) (G) (G) (G) (G) (H) (H) (H) (S) (W) (W)

Obviously - from the results -     I am not succeeding to compare the current val to the previous val. But I have no understanding of a way to do it otherwise.

Any help to learn what I am missing - or failing to comprehend - would be greatly apprecialed.

[[Thanks for TBS]]
By: Tom
Date: 2005-11-07
Time: 16:45

Solved! Re: How to eliminate duplicate records

Using idea from (yet another Skrol29 great post), to strip initial caps from last name of contact instead of having to do and additional query to get the  result like $sql="SELECT DISTINCT ...":
From another post
$sql= "SELECT *, upper( left(lname,1) ) AS lettername FROM mycontacts ORDER BY lname";
The .php file looks like:
function f_distinct_blk2($BlockName,&$CurrRec,$RecNum) {
// bring in from global scope
  global $distinct_array;

// do other stuff here that uses the sql query
  Blah blah blah

// add 'lettername' value to  global array
// eliminate dupes by adding new vals to existing keys

  $distinct_array[$CurrRec['lettername']]= $CurrRec['lettername'];

// push this instance into the 'global' name space
  return $distinct_array;
}

Now just do a Merge :
$TBS->MergeBlock('blk5',$distinct_array);

HTH,
By: Skrol29
Date: 2005-11-08
Time: 02:02

Re: Solved! Re: How to eliminate duplicate records

Hello Tom,

Parameter "ondata" doesn't hallow you to delete or mask a record. You can just add/delete/modify columns of the current record.
The syntax of the custom function associated to parameter "ondata" doesn't give you access to other records but the current one.

That's why your first function cannot work.
$CurrRec[($RecNum-1)] is not the previous record but a new column in the curent record.

Your second idea is strange because I undersant you must merge two blocks.

One thing you can try is simply to use a block which has only a "headergrp" section. That is a section which is shown each time a colum of the record set takes a different value.
Of course the best way i to use a "GROUP BY" SQL query, because is is always better to let the DataBas System doing the data formatting.