Pulling Data from MySQL *Solved*

[SkyRanger]

Ok, with a little help I got the combine 2 tables worked out, but now I have discovered a problem that I never thought of.  I tried a number of things..ie limit but unfortionatly it will not work.

Here is the code:

[code]
SELECT * FROM client, addserve WHERE client.username = addserve.username AND client.username = '$username' limit 1
[/code]

That will allow me to pull the info that is required out of client but there is a number of listing for $username in addserve that I want to display but when I remove the limit one it makes a new display for each entry from addserve which i don't want.  I want 1 display from client but want to show all the entries for username from addserve

ex:
[u]with limit 1 added[/u]
Full Name: John Smith
Username: jsmith
address: 123 my street
Services:  Package 1

[u]without limit 1 added[/u]
Full Name: John Smith
Username: jsmith
address: 123 my street
Services:  Package 1

Full Name: John Smith
Username: jsmith
address: 123 my street
Services:  Package 2

Full Name: John Smith
Username: jsmith
address: 123 my street
Services:  Package 3

etc

What I want it to do is:

Full Name: John Smith //output from client
Username: jsmith //output from client
address: 123 my street //output from client
Services:  Package 1,Package 2, Package 3 etc //output from addserve

Is this possible?

[trq]

You'll need to use a GROUP BY clause. Somthing like...

[code]
SELECT * FROM client, addserve WHERE client.username = addserve.username AND client.username = '$username' GROUP BY addserve.username
[/code]

[SkyRanger]

[quote author=thorpe link=topic=116079.msg472770#msg472770 date=1164317308]
You'll need to use a GROUP BY clause. Somthing like...

[code]
SELECT * FROM client, addserve WHERE client.username = addserve.username AND client.username = '$username' GROUP BY addserve.username
[/code]
[/quote]

Ok I did that but doesn't seem to work for me, not sure If i did something wrong:

[code]

SELECT * FROM client, addserve WHERE client.username = addserve.username AND client.username = '$username' GROUP BY addserve.username

$wordnum = mysql_num_rows( $result );
while( $row = mysql_fetch_array( $result ) )
{

echo $row['fullname'];  //from client table
echo $row['address'];  //from client table
echo $row['phone'];    //from client table
echo $row['serv'];      //from addserve table
echo "<br>";  //part of the serv output to put next serv on new line

*Didn't want to put useless table code to loose what I need help on   
[/code]

It is only showing 1 entry for serv and I need it to display all the entries for serv where it matches the username.

It show right now:
Fullname:  John Smith
Address: 123 My Street
Phone:  555-555-5555
Service: Package 1

I need it to show all that but for Service:
Service: Package 1, Package 2 etc

[SkyRanger]

ok, i even tried this

[code]SELECT * FROM client INNER JOIN addserve ON client.username=addserve.username
WHERE addserve.username = '$username' GROUP BY addserve.username[/code]

Still no luck.  I can get all the data to show that is required minus the stuff from the second table.  It will only show 1 entry for username when there is more than 1 that needs to be displayed.

Shows this:
Fullname:  John Smith
Address: 123 My Street
Phone:  555-555-5555
Service: Package 1

when I need this:
Full Name: John Smith //output from client
Username: jsmith //output from client
address: 123 my street //output from client
Services:  Package 1, Package 2, Package 3 etc //output from addserve

[btherl]

That's tough to express in SQL.  I think it's easier just to fetch all the rows without the GROUP BY, and merge them in PHP.

GROUP BY is great when you want to find sums and counts, but when you're merging text data.. the rules for merging duplicate entries get messy.  In this case you want to append all the packages together (the duplicate merging rule), while grouping by name, username and address.  Just use php :)

[SkyRanger]

Yeah, that is what I ended up doing.  Thanks