Page 1 of 1

GROUP_COMCAT Help

Posted: Thu Oct 11, 2007 10:17 am
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]

Posted: Thu Oct 11, 2007 10:25 am
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.

Posted: Thu Oct 11, 2007 10:34 am
by QbertsBrother
would i use a loop?

Posted: Thu Oct 11, 2007 11:09 am
by feyd
QbertsBrother wrote:would i use a loop?
Iteration would imply looping, yes.

Posted: Thu Oct 11, 2007 11:31 am
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]

Posted: Thu Oct 11, 2007 12:06 pm
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.)

Posted: Thu Oct 11, 2007 1:28 pm
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.

Posted: Fri Oct 12, 2007 9:58 am
by feyd
Sorry, it was the second link you wanted to look through:

viewtopic.php?t=37448

Posted: Tue Oct 16, 2007 5:31 pm
by QbertsBrother
thanks i will give it a look