Only print item once inside a while loop

[dowzer]

I have a table with approx 10,000 addresses including the first part of the postcodes - the postcodes are in the format AB12 and there is significant duplication.

For example if I have four postcodes:

AB12, AB12, AB12, AC12, AC12, AD12

when I print the start of the postcode I end up with a list showing:

AB AB AB AC AC AD

whereas I would like:

AB AC AD

and so on. Can anyone help? I assume I need to put the print inside some sort of logic which checks if it has printed before but I cannot figure out how to do it?

<?php 

$sql=mysql_query("SELECT DISTINCT postcode FROM areas ORDER BY postcode");
$results=mysql_fetch_array($sql);
$count=mysql_num_rows($sql);

if ($count>0) {
while ($results) {
$postcode=$results['postcode'];
$postcode_start = ereg_replace("[^a-zA-Z]", "", $postcode);
print $postcode_start;
print "<br/>";
$results=mysql_fetch_array($sql);
}
}

?>

[coopster]

How about using a SUBSTRING on the postcode in your query? SUBSTRING it to just the first two characters. You are already using DISTINCT so then your list will be trimmed down to what you are describing.

[dowzer]

The challenge is there are some postcodes which only have one letter e.g. A1 2BC or even a few with three letters at the beginning so, if I understand substring correctly, it may not be suitable?

[coopster]

Are you saying you only want the letters from the beginning? Or would you grab the "A1", "2B" in this case? If so, SUBSTRING would work fine. Even if the string was only 1 character long like "A" it would still return the string even though it is not 2 characters long:
SELECT SUBSTRING('A', 1, 2); // returns A

[dowzer]

Hi Cooper,

Yes I only want the letters - so if the postcode is B1 I only want B, if it is AB1 I want AB and if it is ABC1 I want ABC. Does that make sense?

Thanks
Jason

[coopster]

Yes, it does. Off the top of my head I'm thinking a CASE control structure using REGEXP to determine which option to return. Basically you will start at postion 1 and see if it is alpha and if the next character is a digit. If so grab only the first character and return it. Then check for two alpha chars followed by a digit and if that is a match, grab the first two chars. And so on. If none of them match (would could mean it started with a number instead), then return nothing.

SELECT 
  CASE postcode 
    WHEN REGEXP '^[a-z]\d'    THEN SUBSTRING(postcode, 1, 1) 
    WHEN REGEXP '^[a-z]{2}\d' THEN SUBSTRING(postcode, 1, 2) 
    WHEN REGEXP '^[a-z]{3}\d' THEN SUBSTRING(postcode, 1, 3) 
    WHEN REGEXP '^[a-z]{4}\d' THEN SUBSTRING(postcode, 1, 4) 
    WHEN REGEXP '^[a-z]{5}\d' THEN SUBSTRING(postcode, 1, 5) 
    WHEN REGEXP '^[a-z]{6}\d' THEN SUBSTRING(postcode, 1, 6) 
    ELSE '' 
    END AS postcode 
FROM areas 
ORDER BY postcode 
;

I didn't test this, just threw it down out of my head. Perhaps somebody has a better idea? If not, that should work. If you don't have an index over that column, you may want to create one.

[coopster]

Oh yeah, I forgot the DISTINCT keyword in there. Don't forget to SELECT DISTINCT.

[dowzer]

Will give it a try now and let you know, thanks :)

[rocknbil]

group by will do the distinct selects without any special tricks. Remove the output code from below and test it, it's a direct copy and paste. You'll still have to do a regexp in the select OR in PHP (and in that case don't use ereg, use preg, ereg is deprecated.)

create temporary table grouptest(id int(11) primary key auto_increment, postcodes varchar(4) not null);
insert into grouptest (postcodes) values('AB12');
insert into grouptest (postcodes) values('AB12');
insert into grouptest (postcodes) values('AB12');
insert into grouptest (postcodes) values('AC12');
insert into grouptest (postcodes) values('AC12');
insert into grouptest (postcodes) values('AD12');

select distinct postcodes from grouptest group by postcodes order by postcodes asc;

(which outputs)
| postcodes |
| AB12 |
| AC12 |
| AD12 |
(end output)

drop table grouptest;

A-Za-z is synonymous with a-z and the case-insensitive modifier, i.

$postcode_start = preg_replace('/[^a-z]/i', '', $postcode);

... but it would/should be the same thing, and maybe less confusing, to say "remove all digits" rather than "remove anything not a letter" unless there's a potential for spaces or special characters. Which there shouldn't be. :-P

$postcode_start = preg_replace('/\d+/', '', $postcode);