Need help with a Update Query variables

[elentz]

I have a select and an update query that work perfectly if I manually enter the record numbers for cf_565, and id. The fields get updated as they should. as soon as I try to use the variables $cf_565 and $id it fails. I get no error messages. I would also like to use an IF statement to ignore the above code and continue using another variable such as $cf_581 which is a checkbox and it would be true with a 1
Thanks for suggestions where I am going wrong

Here is my code

$result15 = mysql_query("SELECT
vtiger_crmentity.description,
vtiger_troubletickets.solution,
vtiger_troubletickets.ticketid
FROM
vtiger_troubletickets
Inner Join vtiger_crmentity ON vtiger_troubletickets.ticketid = vtiger_crmentity.crmid
WHERE
vtiger_troubletickets.ticketid =  $cf_565");
$row = mysql_fetch_array($result15);
$trouble= $row['description'];
$fix =$row['solution'];
$sql = "Update communiq_vtcrm1.vtiger_invoice x, communiq_vtcrm1.vtiger_crmentity z Set x.terms_conditions= '$fix' , z.description='$trouble' Where z.crmid = $id and x.invoiceid = $id";
$result = mysql_query($sql);

 

[vacunita]

Where are you setting $cf_565 and $id? Have you checked their contents prior to using them in the query?

IF they aren't numerical, as "cf_565" is not, they'd need to be surrounded by single quotes inside the query.

Also adding explicit error checking may provide more information:

$row = mysql_fetch_array($result15)[red]or die(mysql_error())[/red];

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[jpadie]

I would also add an error check to the query itself.

if ($result15 === false) die (mysql_error);

 

[jpadie]

re-reading the question, it is your contention that the problem is with the variable. Very possibly true but you have not told us anything about how the variable gets created and used, so there is little we can advise.

 

[elentz]

The $id is used elsewhere within the script I am about to upload. So I know it is OK. I tried to use a known good number instead of $cf_565 leaving the $id and I still didn't get the table update I am looking for. So here is my code, I am sure it is very sloppy but until now it appears to work. The previous code I showed in an earlier post is the only code that doesn't work.

Thanks

<?php

require_once('modules/Invoice/Invoice.php');
require_once('include/logging.php');
require_once('include/database/PearDatabase.php');
include("modules/Emails/mail.php");

$local_log =& LoggerManager::getLogger('index');

$focus = new Invoice();
//added to fix 4600
$search=vtlib_purify($_REQUEST['search_url']);

global $current_user;
setObjectValuesFromRequest($focus);

$focus->column_fields['currency_id'] = $_REQUEST['inventory_currency'];
$cur_sym_rate = getCurrencySymbolandCRate($_REQUEST['inventory_currency']);
$focus->column_fields['conversion_rate'] = $cur_sym_rate['rate'];

if($_REQUEST['assigntype'] == 'U')  {
        $focus->column_fields['assigned_user_id'] = $_REQUEST['assigned_user_id'];
} elseif($_REQUEST['assigntype'] == 'T') {
        $focus->column_fields['assigned_user_id'] = $_REQUEST['assigned_group_id'];
}
$focus->save("Invoice");

$return_id = $focus->id;

$parenttab = getParentTab();
if(isset($_REQUEST['return_module']) && $_REQUEST['return_module'] != "") $return_module = vtlib_purify($_REQUEST['return_module']);
else $return_module = "Invoice";
if(isset($_REQUEST['return_action']) && $_REQUEST['return_action'] != "") $return_action = vtlib_purify($_REQUEST['return_action']);
else $return_action = "DetailView";
if(isset($_REQUEST['return_id']) && $_REQUEST['return_id'] != "") $return_id = vtlib_purify($_REQUEST['return_id']);

$local_log->debug("Saved record with id of ".$return_id);


//Code added by Ed Lentz to update the invoiceCF table with tax info on 9-13-09****

//ini_set("display_errors", "1");
//error_reporting(E_ALL);
$id= $_POST['record'];
// connect to db
$conn = mysql_connect('localhost','communiq','xxxxxxx');
$db = mysql_select_db('communiq_vtcrm1',$conn);

//**********************************************************************************

//Update Query to do the math for the extended prices of Products sold
$sql = "Update vtiger_inventoryproductrel SET extlistprice = quantity * listprice";
      $result = mysql_query($sql, $conn);

//****************************Get Sum of the Products*******************************
$sql1="SELECT
Sum(vtiger_inventoryproductrel.extlistprice)
FROM
vtiger_inventoryproductrel
Inner Join vtiger_products ON vtiger_inventoryproductrel.productid = vtiger_products.productid
WHERE
vtiger_inventoryproductrel.id =  $id";
$result = mysql_query($sql1, $conn);
$productstotal = mysql_fetch_row($result);
//*************Update query to insert the Product total into the invoiceCF table****

$sql2="Update vtiger_invoicecf SET cf_603 = $productstotal[0] where vtiger_invoicecf.invoiceid = $id";
$result = mysql_query($sql2, $conn);
$salestax = $productstotal[0] * .06;
//************Math to set the Sales Tax for the products Sold***********************
$sql2="Update vtiger_invoicecf SET cf_604 = $salestax where invoiceid =$id";
$result = mysql_query($sql2, $conn);

//**********Query to get the services total*****************************************
$sql3 = "SELECT
Sum(vtiger_inventoryproductrel.extlistprice)
FROM
vtiger_inventoryproductrel
Inner Join vtiger_service ON vtiger_inventoryproductrel.productid = vtiger_service.serviceid
WHERE
vtiger_inventoryproductrel.id =  $id";
$result = mysql_query($sql3, $conn);
$servicestotal=mysql_fetch_row($result);

//*****************Update to insert the Service total into the invoiceCF table********
$sql4="Update vtiger_invoicecf SET cf_602 = $servicestotal[0] where invoiceid = $id";
$result = mysql_query($sql4, $conn);
mysql_close();
// Make a MySQL Connection******For the automatic entry of Trouble ticket Problem/solution***************
mysql_connect("localhost", "communiq", "xxxxxxx") or die(mysql_error());
mysql_select_db("communiq_vtcrm1") or die(mysql_error());

// Get all the data from the table
$result15 = mysql_query("SELECT
vtiger_crmentity.description,
vtiger_troubletickets.solution,
vtiger_troubletickets.ticketid
FROM
vtiger_troubletickets
Inner Join vtiger_crmentity ON vtiger_troubletickets.ticketid = vtiger_crmentity.crmid
WHERE
vtiger_troubletickets.ticketid =  7629")or die(mysql_error());

$row = mysql_fetch_array($result15);
$trouble= $row['description'];
$fix =$row['solution'];
$sql = "Update communiq_vtcrm1.vtiger_invoice x, communiq_vtcrm1.vtiger_crmentity z Set x.terms_conditions= $fix , z.description= $trouble Where z.crmid =$id and x.invoiceid =$id"or die(mysql_error());
$result = mysql_query($sql);

mysql_close();

//*************End Trouble Ticket code ***************************************************
//}
//else
//{

//code added for returning back to the current view after edit from list view
if($_REQUEST['return_viewname'] == '') $return_viewname='0';
if($_REQUEST['return_viewname'] != '')$return_viewname=vtlib_purify($_REQUEST['return_viewname']);

header("Location: index.php?action=$return_action&module=$return_module&parenttab=$parenttab&record=$return_id&viewname=$return_viewname&start=".vtlib_purify($_REQUEST['pagenumber']).$search);

?>

 

[vacunita]

Again we ask, where are you setting the "variable" $cf_565.

Something like: $cf_565="something here";

Just like $id is being set here:

$id= $_POST['record'];

(this is of course very dangerous, you should clean the variable before sticking it into a query as it can lead to sql injection attacks.

$id=mysql_real_escape_string($_POST['id']);
)


Without setting $cf_565 your query's WHERE clause may be unable to find any relevant records to update.

Neither piece of code shows you setting the variable.

And this is of course very wrong:

$sql = "Update communiq_vtcrm1.vtiger_invoice x, communiq_vtcrm1.vtiger_crmentity z Set x.terms_conditions= $fix , z.description= $trouble Where z.crmid =$id and x.invoiceid =$id"or die(mysql_error());

The "or die" part should not be placed inside the string that holds the query but after you actually call mysql_query() function to execute the query. Like you have in your other query calls.








----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[elentz]

Thanks for the speedy reply! I will write a query to make sure I get that cf_565 info. I'll try a $cf_565 = xxxx to see if that works and I 'll clean up the other things you mentioned. I'll report back

Thanks Again

 

[elentz]

Well, I tried $cf_565="7629"; Where I knew the 7629 had something in its records for the query. No luck , I got nothing in the update query. Any other suggestions? Do I have the queries right considering the other queries I have going on, could they be interfering somehow?

 

[jpadie]

that's horrible code.

i have rewritten most of it for you. i have not included an update of the rel table to update the productprice * quantity column as I do not know whether it is necessary.

the reason your update was failing is that you cannot update two tables at once. updates can only be made on one table at a time.

// connect to db
$conn = mysql_connect('localhost','communiq','xxxxxxx') or die ('cannot connect to the database server');
$db = mysql_select_db('communiq_vtcrm1',$conn) or die('cannot selected db.  Error was '. mysql_error());

//escape the record value
$id= mysql_real_escape_string($_POST['record']);
//**********************************************************************************

$query = <<<QUERY
UPDATE
	vtiger_invoicecf i
SET
	cf_603 = (
				SELECT
					SUM(ipr.quantity * ipr.listprice)
				FROM
					vtiger_invetoryproductrel ipr
					INNER JOIN
					vtiger_product p
					ON
						(p.productif = ipr.productid)
				WHERE
					ipr.productid='$id' 
			),
	cf_604 = '$salestax',
	cf_602 = (
				SELECT
					SUM(ipr.quantity * ipr.listprice)
				FROM
					vtiger_invetoryproductrel ipr
					INNER JOIN
						vtiger_service s
						ON
							(s.serviceid = ipr.productid)
				WHERE
					ipr.productid='$id' 
			)
WHERE
	i.invoiceid = '$id';
QUERY;


//update the table
mysql_query($query) or die (mysql_error());

// Get all the data from the table
$dummyValue = '7629';
$result15 = mysql_query("
				SELECT
					crm.description,
					tt.solution,
					tt..ticketid
				FROM
					vtiger_troubletickets tt
					INNER JOIN 
						vtiger_crmentity crm
					ON 
						tt.ticketid = crm.crmid
				WHERE
					tt.ticketid ='$dummyValue'") or die(mysql_error());

list ($trouble, $fix) = mysql_fetch_assoc($result15, MYSQL_NUM);
$sql = array();
$sql[] = "
		UPDATE 
			communiq_vtcrm1.vtiger_invoice,
		SET
			terms_conditions = '" . mysql_real_escape_string($fix) . "' , 
		WHERE 
			x.invoiceid ='$id'";
$sql[] = "
		UPDATE 
			communiq_vtcrm1.vtiger_crmentity 
		SET
			description= '" . mysql_real_escape_string($trouble) . "' 
		WHERE 
			crmid = '$id'";
foreach ($sql as $query){
	mysql_query($query) or die (mysql_query());
}