Page 1 of 1

Sorting a query by a field

Posted: Fri Mar 25, 2005 4:33 pm
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]

Posted: Fri Mar 25, 2005 4:36 pm
by feyd
you simply order by area and address. Track the last area used, when it changes, output a new area header.

Posted: Fri Mar 25, 2005 5:30 pm
by ra
what would that look like in php code?

Posted: Fri Mar 25, 2005 6:04 pm
by Joe
To order the addresses use:

Code: Select all

SELECT address, details FROM openhouse ORDER by address

Posted: Fri Mar 25, 2005 6:06 pm
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

Posted: Fri Mar 25, 2005 6:08 pm
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!!!.

Posted: Fri Mar 25, 2005 6:16 pm
by ra
Thanks; Any idea on how that would look in php?

Posted: Fri Mar 25, 2005 6:27 pm
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

Posted: Fri Mar 25, 2005 6:30 pm
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...

Posted: Fri Mar 25, 2005 6:51 pm
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.

Posted: Sat Mar 26, 2005 10:23 am
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

Posted: Sat Mar 26, 2005 1:58 pm
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.

Posted: Sat Mar 26, 2005 3:18 pm
by timvw
long live a basic tutorial/book on algorithms... :)