Welcome to WebmasterWorld Guest from 54.167.157.247

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Only print item once inside a while loop

   
1:20 pm on May 27, 2011 (gmt 0)

5+ Year Member



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);
}
}

?>
1:39 pm on May 27, 2011 (gmt 0)

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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.
2:00 pm on May 27, 2011 (gmt 0)

5+ Year Member



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?
2:05 pm on May 27, 2011 (gmt 0)

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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
2:14 pm on May 27, 2011 (gmt 0)

5+ Year Member



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
2:34 pm on May 27, 2011 (gmt 0)

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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.
2:35 pm on May 27, 2011 (gmt 0)

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member



Oh yeah, I forgot the DISTINCT keyword in there. Don't forget to SELECT DISTINCT.
2:39 pm on May 27, 2011 (gmt 0)

5+ Year Member



Will give it a try now and let you know, thanks :)
4:22 pm on May 27, 2011 (gmt 0)

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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);
 

Featured Threads

Hot Threads This Week

Hot Threads This Month