Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
ra
Forum Commoner
Posts: 58 Joined: Fri Mar 25, 2005 4:25 pm
Post
by ra » Fri Mar 25, 2005 4:33 pm
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
tags. Read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]
feyd
Neighborhood Spidermoddy
Posts: 31559 Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA
Post
by feyd » Fri Mar 25, 2005 4:36 pm
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 » Fri Mar 25, 2005 5:30 pm
what would that look like in php code?
Joe
Forum Regular
Posts: 939 Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow
Post
by Joe » Fri Mar 25, 2005 6:04 pm
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 » Fri Mar 25, 2005 6:06 pm
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
Joe
Forum Regular
Posts: 939 Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow
Post
by Joe » Fri Mar 25, 2005 6:08 pm
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 » Fri Mar 25, 2005 6:16 pm
Thanks; Any idea on how that would look in php?
Joe
Forum Regular
Posts: 939 Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow
Post
by Joe » Fri Mar 25, 2005 6:27 pm
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
ra
Forum Commoner
Posts: 58 Joined: Fri Mar 25, 2005 4:25 pm
Post
by ra » Fri Mar 25, 2005 6:30 pm
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...
John Cartwright
Site Admin
Posts: 11470 Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:
Post
by John Cartwright » Fri Mar 25, 2005 6:51 pm
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 » Sat Mar 26, 2005 10:23 am
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 » Sat Mar 26, 2005 1:58 pm
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 » Sat Mar 26, 2005 3:18 pm
long live a basic tutorial/book on algorithms...