Sorting a query by a field

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
ra
Forum Commoner
Posts: 58
Joined: Fri Mar 25, 2005 4:25 pm

Sorting a query by a field

Post by ra »

I have 4 fields in a mysql table: id, area, address, details. I want to call the address and details, but display them in rows seperated, grouped and labeled by their respective Area. ie:

Code: Select all

Address             Details
Area 1
address 123 my st   details blah bla
address 124 my st   details blah bla
address 125 my st   details blah bla
Area 2
address 127 my st   details blah bla
address 128 my st   details blah bla
address 129 my st   details blah bla
Can this be done? Here is my code thus far:

Code: Select all

<td colspan="3">
	<?php
   mysql_connect("xxxx","xxxx","xxxx");
   mysql_select_db("xxxx");
   echo ("<table width=740 border=1 cellpadding=1 cellspacing=1 >
<tr>
<td width=50%><b>Address</b></td>
<td width=50%><b>Details</b></td>
</tr>");

$sql = mysql_query("SELECT address, details FROM openhouse") or die ( mysql_error() );
while ($row = mysql_fetch_array($sql)) {
echo "<TR>"."<TD>".$row['address']."</TD>"."<TD>".$row['details']."</TD>";
 }
echo "</TABLE>";
 ?></td>

feyd | Please review how to post code using

Code: Select all

and

Code: Select all

tags. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

you simply order by area and address. Track the last area used, when it changes, output a new area header.
ra
Forum Commoner
Posts: 58
Joined: Fri Mar 25, 2005 4:25 pm

Post by ra »

what would that look like in php code?
User avatar
Joe
Forum Regular
Posts: 939
Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow

Post by Joe »

To order the addresses use:

Code: Select all

SELECT address, details FROM openhouse ORDER by address
ra
Forum Commoner
Posts: 58
Joined: Fri Mar 25, 2005 4:25 pm

Post by ra »

i need to sort them by area, and group them into rows seperated and titled by their respective area...

area 1
listing
listing

area 2
listing
listing
User avatar
Joe
Forum Regular
Posts: 939
Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow

Post by Joe »

I think you would need to create a loop which uses LIKE queries to detect which addresses should be put into the particular area. Im thinking!!!.
Last edited by Joe on Fri Mar 25, 2005 6:09 pm, edited 1 time in total.
ra
Forum Commoner
Posts: 58
Joined: Fri Mar 25, 2005 4:25 pm

Post by ra »

Thanks; Any idea on how that would look in php?
User avatar
Joe
Forum Regular
Posts: 939
Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow

Post by Joe »

Hmm it would involve a fair bit of work, however there is a solution. You can add a new field within the database table titled 'area' from which you can proceed to plan out area's.

For example,

All record from the UK, Area1 - US, Area2 etc

If you do that then im sure I can help :D
ra
Forum Commoner
Posts: 58
Joined: Fri Mar 25, 2005 4:25 pm

Post by ra »

I have a field in the database for area, and it's options are in the form... (area 1, area 2, etc...), now i just need to diplay the areas in sections and add the listing to each appropriate section...
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

feyd wrote:you simply order by area and address. Track the last area used, when it changes, output a new area header.
Joe wrote:Hmm it would involve a fair bit of work
pretty easy to do actually

Code: Select all

$last = '';
while ($row = mysql_fetch_array($sql)) 
{
     if ($row['somefield'] != $last)
     {
          echo $row['somefield'];
          $last = $row['somefield'];
     }
}
That is the basics of it ;) Good luck.
ra
Forum Commoner
Posts: 58
Joined: Fri Mar 25, 2005 4:25 pm

Post by ra »

So in this case I would enter:

Code: Select all

$last = '';
while ($row = mysql_fetch_array($sql)) 
{
     if ($row['area'] != $last)
     {
          echo $row['area'];
          $last = $row['area'];
     }
}


and that would create:
Area 1
address details
address details
Area 2
address details
address details
magicrobotmonkey
Forum Regular
Posts: 888
Joined: Sun Mar 21, 2004 1:09 pm
Location: Cambridge, MA

Post by magicrobotmonkey »

try it and see why don't you?

that snippet will obviously only print the areas - what you want to do is print every address you find and then only print the area if its a new one - thats what the snippet does.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

long live a basic tutorial/book on algorithms... :)
Post Reply