GROUP_COMCAT Help

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
QbertsBrother
Forum Commoner
Posts: 58
Joined: Thu Oct 11, 2007 10:12 am

GROUP_COMCAT Help

Post by QbertsBrother »

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


hello all

so i have this query.

[syntax="sql"]
SELECT webclient_client, GROUP_CONCAT(webclientsite_sitename SEPARATOR '<br>') as site_name, GROUP_CONCAT(webclientsite_location SEPARATOR '') as site_loc from webclient, webclientsite where webclient_id = webclientsite_webclient_id group by webclientsite_webclient_id order by webclient_client
it works and does everything i need it to do. it groups all the sites that i have by client. the problem is that i want to have the site name be a link to the site. here is the output now:

LRA
Arrow Buick Pontiac GMC
Bloomington Chrysler Jeep
SEV
Sever's Farm Market
Sever's Corn Maze

what is happening is that when i use the group_concat it makes the link look like this.

http://www.seversfarmmarket.com/http:// ... nmaze.com/

and both lines under the client are one link. here is how i am outputting it:[/syntax]

Code: Select all

while ($rowwebclient = mysql_fetch_array($resultwebclient)){

$outputwebclient .="<div class='contentheadercontainer'>
<div class='contentheader'>
".$rowwebclient[webclient_client]."<br/>
<a href='".$rowwebclient[site_loc]."'>".$rowwebclient[site_name]."</a>
</div>
";
}
i would like to have each site name under the clients to be its own link. i kind of remember doing something like that before but i forgot how i did it.

if anyone has any input that would be great.


feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I don't see a reason to use group_concat() here. A simple query with iteration would probably generate the output you are hoping to have.
QbertsBrother
Forum Commoner
Posts: 58
Joined: Thu Oct 11, 2007 10:12 am

Post by QbertsBrother »

would i use a loop?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

QbertsBrother wrote:would i use a loop?
Iteration would imply looping, yes.
QbertsBrother
Forum Commoner
Posts: 58
Joined: Thu Oct 11, 2007 10:12 am

Post by QbertsBrother »

feyd wrote:Iteration would imply looping, yes.
thanks for you help.

sorry i dont know all the "terms" that people use.

i am guessing that i would do a for loop with a simple query that selects the data?

what i have is two tables. one that holds the client and the other one holds the site info such as the site name and address.

when i do a basic query i get this result.

LRA
Arrow Buick Pontiac GMC
LRA
Bloomington Chrysler Jeep

SEV
Sever's Corn Maze
SEV
Sever's Farm Market

each linked to the correct address. so if i do a for loop i can get it to output like this and linked to the correct address?

LRA
Arrow Buick Pontiac GMC
Bloomington Chrysler Jeep

SEV
Sever's Corn Maze
Sever's Farm Market
[/syntax]
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

QbertsBrother wrote:i am guessing that i would do a for loop with a simple query that selects the data?
Many use a while loop.
QbertsBrother wrote:what i have is two tables. one that holds the client and the other one holds the site info such as the site name and address.

when i do a basic query i get this result.

LRA
Arrow Buick Pontiac GMC
LRA
Bloomington Chrysler Jeep

SEV
Sever's Corn Maze
SEV
Sever's Farm Market

each linked to the correct address. so if i do a for loop i can get it to output like this and linked to the correct address?

LRA
Arrow Buick Pontiac GMC
Bloomington Chrysler Jeep

SEV
Sever's Corn Maze
Sever's Farm Market
You may want to look at the first thread linked from Useful Posts. (Currently, a link to Useful Posts is in my signature.)
QbertsBrother
Forum Commoner
Posts: 58
Joined: Thu Oct 11, 2007 10:12 am

Post by QbertsBrother »

so i got it to do what i need it to do.

what i ended up doing is this:

select the clients from the client table. using a while loop i loop through them and in side that while loop i make another select statement that selects the sites from another table.

Code: Select all

$querywebclient = "SELECT * from webclient order by webclient_client";
$resultwebclient = mysql_query($querywebclient) or die (mysql_error());

$outputwebclient = "";
while ($rowwebclient = mysql_fetch_array($resultwebclient)){
$webclientid = $rowwebclient[webclient_id];

$outputwebclient .="<div>
".$rowwebclient[webclient_client]."</div>";
$querywebclientsite = "SELECT * from webclientsite where webclientsite_webclient_id = $webclientid order by webclientsite_sitename";
$resultwebclientsite = mysql_query($querywebclientsite) or die (mysql_error());

while ($rowwebclientsite = mysql_fetch_array($resultwebclientsite)){
$outputwebclient .= "<div>
<a href='".$rowwebclientsite[webclientsite_location]."'>".$rowwebclientsite[webclientsite_sitename]."</a>
</div>";
}
}

$outputwebclient .= "";

print $outputwebclient;
i am sure that this not the most efficient way of doing things but it works now. i looked thru the links that you had recommended and didnt seem to find what i needed there.

thanks for the help.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Sorry, it was the second link you wanted to look through:

viewtopic.php?t=37448
QbertsBrother
Forum Commoner
Posts: 58
Joined: Thu Oct 11, 2007 10:12 am

Post by QbertsBrother »

thanks i will give it a look
Post Reply