Index to order address by town

[Stu_Rogers]

My database (MySQL) contains UK contacts names and addresses. Each line of the address is stored in a separate varchar field. (line1, line2, line3, town, county, postcode).

To display the database, I am ordering it by the town field so that everyone in, for example, Carlisle appears together.

I would like to display a heading above the contacts for each town. Many town headings may only have one contact under them, but others like London, may have 10 or 20 contacts listed.

I know how to achieve this using a separate linked table with an ID for each town, but that is not feasible at this stage. Even if I were to start again I don’t think this would be the ideal solution because there are many hundreds of towns in the UK.

I think the solution lies in creating an index on the town field but I can’t figure out the SQL query and php code. Basically I need a while loop for towns, then a further while loop within it to select each contact for that town.

Any pointers to get me started would be useful. Or post back if you need it explaining any better.

[mcibor]

There are two ways of doing this: easier and harder. I'm writing algorithm, not the code!

1. easier:
"SELECT DISTINCT town FROM table";
while-->$town
echo $town;
"SELECT line1, line2, line3, town, county, postcode FROM table WHERE town='$town'";
while-->get-->table
echo-->table
end while; end while;

2. harder:
"SELECT line1, line2, line3, town, county, postcode FROM table ORDER BY town";
get-->first line
echo town;
echo first row
remember-->town
while-->get-->row
check if town changed
not changed--> echo row
changed-->echo town and echo row

I have both ways running. First way takes really much more time, than the latter (more than 100 times slower in my case)
Best regards
Michal Cibor

PS. What you look for is called a tree like structure

[Stu_Rogers]

Just a quick reply to thank mcibor for this answer.

I have used the first suggestion and got it working no problem. The method is so easy I am surprised I didn't figure it out myself (except I wasn't aware of the SQL word DISTINCT).

My database is not large enough to worry about speed issues but I can see how this method would slow things down.

I understand the second suggestion but don't have enough knowledge to impliment it - how do I get MySQL to return a row at a time?

[mcibor]

Here's the simple example (not working :( )

$sql = "SELECT line1, line2, line3, town, county, postcode FROM table ORDER BY town";
$result = mysql_query($sql) or die(mysql_error());
//here get row by row
while($row = mysql_fetch_assoc($result)) {
//check if 1rst row, then write TOWN and row:!isset($previous_town)
//elseif town has changed, then write TOWN and row: $previous_town!= $row["town"]
//elseif town not changed, then write row
//remember town, eg $previous_town = $row["town"];
}

And that's it!
You need only to remember what the last town name was (therefore the variable $previous_town).
This way is a way faster, because you have 1 query instead of (number_of_towns + 1)

Best regards
Michal Cibor