homepage Welcome to WebmasterWorld Guest from 54.196.196.108
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
Only print item once inside a while loop
dowzer




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

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




msg:4318548
 1:39 pm on May 27, 2011 (gmt 0)

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




msg:4318567
 2:00 pm on May 27, 2011 (gmt 0)

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




msg:4318573
 2:05 pm on May 27, 2011 (gmt 0)

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




msg:4318574
 2:14 pm on May 27, 2011 (gmt 0)

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




msg:4318593
 2:34 pm on May 27, 2011 (gmt 0)

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




msg:4318595
 2:35 pm on May 27, 2011 (gmt 0)

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

dowzer




msg:4318599
 2:39 pm on May 27, 2011 (gmt 0)

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

rocknbil




msg:4318667
 4:22 pm on May 27, 2011 (gmt 0)

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved