Re: i need some help with extracting data from mysql
| From: | Steve Werby | Date: | Thu, 10 May 2001 16:47:29 +0000 |
| Subject: | Re: i need some help with extracting data from mysql | ||
| References: | 1 | Groups: | php.general |
| Request: | Send a blank email to php-general+get-52321@lists.php.net to get a copy of this message | ||
"Sandeep Hundal" <Sunny@mviva.net> wrote:
> > my query is "SELECT group, name FROM $tablename WHERE approved='yes'
> > GROUP BY folder ORDER BY folder;
>
> Sorry, substitute folder for Group. I just wrote that by mistake.
>
> Data is such:
> 1 | friends | name1 | yes
> 2 | misc | name2 | yes
> 3 | friends | name3 | yes
> 4 | work | name4 | no
>
> I want to extract the ones approves, but list them by Group first, and all
> the relevant names after. But I dont want to run a query for each GROUP to
> output the data underneath it.
You don't need to GROUP the records b/c you're not performing any kind of
aggregate operation on them. Use the following query:
SELECT group, name
FROM tablename
WHERE approved = 'yes'
ORDER BY group
Use PHP to loop through each record like you normally would. Since you want
the group on a line by itself and then all of the names that are members of
the group one per line below it you should do something like the following
(I assume you're outputting in HTML, if not replace all '<br>' tags with
"\n"):
while ( $row = mysql_fetch_array( $sql ) )
{
$group = $row['group'];
$name = $row['name'];
if ( $group != $group_previous )
{
echo $group . '<br>';
}
echo $name . '<br>';
$group_previous = $group;
}
By setting $group_previous equal to $group *after* looping through each row
it can be compared to $group so that you only display the group name when it
doesn't match the $group of the previous row. I think this is what you
wanted. If I misinterpreted what you were trying to do let me know.
--
Steve Werby
President, Befriend Internet Services LLC
http://www.befriend.com/