Need assistance pulling tblcustomfieldsvalues

[ramiss]

Hi there,

 

Can someone please check my logic and SQL below for pulling the tblcustomfieldsvalues for a product.

 

My goal is to have a SQL statement retreive a tblcustomfieldsvalues.value given just the tblcustomfieldsvalues.relid and the tblcustomfields.fieldname. But I can't seem to get my head wrapped around it in a way that makes me feel confident about it.

 

Here is what I have so far. Can anyone expand on this:

 

SELECT value FROM tblcustomfieldsvalues INNER JOIN tblcustomfields ON fieldname = '".$Fieldname."' AND tblcustomfieldsvalues.relid = tblcustomfields.relid AND tblcustomfields.relid = ".$HostingID." LIMIT 1;

 

I want to point out that I know most people pull these value by hard coding the tblcustomfieldsvalues.fieldid. But I need something that can work across multiple similar products even if we add/remove from the Product table.

 

Thanks for any help!

 

Richard

[ramiss]

Ok, I figured it out. For anyone that needs it here is how to pull the Custom Field Values for any Customer Product:

 

The variable $HostingID comes from tblhosting.id

 

SELECT tblcustomfields.fieldname,tblcustomfieldsvalues.value FROM tblcustomfields,tblcustomfieldsvalues WHERE tblcustomfields.id=tblcustomfieldsvalues.fieldid AND tblcustomfieldsvalues.relid='".$HostingID."'

[xauen]

Hi,

 

I have almost the same situation as yours. The only difference is i want the tblcustomfieldsvalues.value to be changed as the user updates the tblhosting.password field.

However, while the tblhosting.password field is change thru update_query() function, I cannot seem to do it on the tblcustomfieldsvalues.value coz of multilple table referencing.

See attached code below:

 

$sql2 = mysql_query("UPDATE tblcustomfieldsvalues SET tblcustomfieldsvalues.value = '".$params["password"]."' WHERE (tblcustomfields.id = tblcustomfieldsvalues.fieldid) AND (tblcustomfieldsvalues.relid = tblhosting.id) AND (tblcustomfields.fieldname = 'Password')", $whmcsmysql);

if (!$sql2) {

return 'Could not change user password '.mysql_error($whmcsmysql);

}

 

Its not working. Can you please tell me whats wrong with my code?