2

I'm trying to select some user data from a client table and some custom field values from another. My problem seems to be looping through the results. I keep getting duplicate client rows per one custom value field.

this is the SQL query to get the clients address details and the correct values per client from a different table tblcustomfieldvalues.

SELECT
   tblhosting.userid,
   tblclients.id,
   tblclients.firstname,
   tblclients.city,
   tblclients.state,
   tblclients.country,
   tblclients.postcode,
   tblclients.companyname,
   tblclients.ip,
   tblcustomfields.id,
   tblcustomfields.fieldname,
   tblcustomfieldsvalues.fieldid,
   tblcustomfieldsvalues.value 
FROM tblhosting,tblclients,tblcustomfields,tblcustomfieldsvalues
WHERE tblclients.id IS NOT NULL 
AND tblclients.id = tblhosting.userid 
AND tblcustomfields.id=tblcustomfieldsvalues.fieldid 
AND tblcustomfieldsvalues.relid=tblhosting.id
AND tblcustomfieldsvalues.fieldid between 291 and 292

My problem is how to get the two row values from tblcustomfieldsvalues for each client and the tblclients address and so on.

I've tried splitting the query and using while and forech loops but keep getting duplicate values from the clients table. or only one customfieldvalue per client.

nickhar
  • 19,981
  • 12
  • 60
  • 73
user1797390
  • 21
  • 2

1 Answers1

0

"SELECT tblhosting.userid,tblclients.id,tblclients.firstname,tblclients.city,tblclients.state,tblclients.country,tblclients.postcode,tblclients.companyname,tblclients.ip,tblcustomfields.id,tblcustomfields.fieldname,tblcustomfieldsvalues.fieldid,tblcustomfieldsvalues.value FROM tblhosting,tblclients,tblcustomfields,tblcustomfieldsvalues"

Your code is like horrible looking. This is unreadable for someone, who has no view to your structure. You should use synonyms and 'join'keyword for fetching between tables.

@edit now its looks okey.

Dariss
  • 1,258
  • 1
  • 12
  • 27
  • i managed to solve the issue with a while loop explode then a foreach loop. Any who, the query above is good for getting custom field values from any client there is no documentation on whmcs database table association. – user1797390 Nov 05 '12 at 08:49