MySQL Query/Display Help

[Hosting&COLO]

I'm a self-taught, weekend-warrior with PHP/MySQL that can generally handle basic queries, but I'm hoping that someone with some PHP/MySQL skills can assist me.

 

I'm trying to display the query results of the Clients and Custom Client Fields table by customer, but I'm having problems.

 

Here's my query...

$query = "SELECT tblclients.id, tblclients.firstname, tblclients.lastname, tblclients.companyname, tblclients.status, tblcustomfieldsvalues.fieldid, tblcustomfieldsvalues.relid, tblcustomfieldsvalues.value FROM tblclients,tblcustomfieldsvalues WHERE tblclients.status='active' AND tblclients.id=tblcustomfieldsvalues.relid GROUP BY tblclients.id";

$result=mysql_query($query);

Now, in order to display the results, I have this...

while ($data = mysql_fetch_array($result)) {
 $id = $data["id"];
 $firstname = $data["firstname"];
 $lastname = $data["lastname"];
 $companyname = $data["companyname"];

That part is easy, its trying to specify each "Value" from TBLCUSTOMFIELDSVALUES as "$value1", "$value2", etc... so I can display it in its own column based on the Customer ID.

 

Each row has the following columns...

ID

Firstname

Lastname

Company Name

Value1

Value2

Value3

 

If I was only trying to retrieve just 1 value from TBLCUSTOMFIELDSVALUES, I could filter that value in the query and get only that info, but because I'm retrieving multiple values, I'm running into issues.

 

Any ideas how I can make this work? Can I (or should I) code this better/different? Your help is supremely appreciated.

 

Thank you.

 

-R

[bear]

I'm no expert either, but if you try something like this within the while loop (untested) it should get you moving in the direction I think you're trying to go.

 

$customfields[$data['value']]=array(); //set the array with those values

foreach($customfields as $fieldid=>$stuff)
{
echo $stuff['value'];
}

 

[EDIT]

Spoke with a buddy of mine who's quite good at this, and he suggested using a left join for the data:

$query = "SELECT tblclients.id, tblclients.firstname, tblclients.lastname, tblclients.companyname, tblclients.status, tblcustomfieldsvalues.fieldid, tblcustomfieldsvalues.relid, tblcustomfieldsvalues.value
FROM tblclients
LEFT JOIN tblcustomfieldsvalues ON tblclients.id=tblcustomfieldsvalues.relid
WHERE tblclients.status='active'
";

Use that to populate your while loop, and initialize the array before using it.