I need some noob help here.

[elentz]

I want to take a result of a Mysql query and insert that into a different table. I can get the result to echo on the screen but I can't seem to get it to work in the next query. Here's what I have:
$sql1="SELECT
Sum(vtiger_inventoryproductrel.extlistprice)
FROM
vtiger_inventoryproductrel
Inner Join vtiger_products ON vtiger_inventoryproductrel.productid = vtiger_products.productid
WHERE
vtiger_inventoryproductrel.id = '3824'";
$result = mysql_query($sql1, $conn) or trigger_error("SQL", E_USER_ERROR);
$productstotal = mysql_fetch_row($result);
//*************Update query to insert the Product total into the invoiceCF table****

$sql1="Update vtiger_invoicecf SET cf_603 = $productsubtotal where vtiger_invoicecf.invoiceid = '3824'";
$result1 = mysql_query($sql1, $conn) or trigger_error("SQL", E_USER_ERROR);


I get a Call to undefined MySql Fetch error from the first query. Can some one help show me what I have wrong?

Thanks

 

[jpadie]

that's curious. what is the exact text of the error?

 

[vacunita]

I can get the result to echo on the screen but I can't seem to get it to work in the next query.

What changed between the two versions?

If you can get it to display to the screen, then you should not be getting any error in the fetch_array as it should have remained the same.


Additionally: Even if your query is only returning a single value from the sum, $productstotal will still be an array. A single index array, but an array nonetheless.

As such it should be addressed $productstotal[0].




----------------------------------
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]

Here's the error:
Fatal error: Call to undefined function mysql_fetch() in /home

This is the line it is referring to:
$productstotal = mysql_fetch_row($result);

It being an array, what do I need to change since it will always be a single item?

Thanks

 

[vacunita]

Fatal error: Call to undefined function mysql_fetch() in /home

That points to a problem with the mysql configuration in the PHP.ini file. Mainly that its not set correctly.

However this makes absolutely no sense if you "...can get the result to echo on the screen".

So either you are doing this in different servers configured differently, or your display to the screen isn't working either.



----------------------------------
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]

here's all my code. I didn't show it all because I thought it might not be needed:

<?php
ini_set("display_errors", "1");
error_reporting(E_ALL);

// connect to db
$conn = mysql_connect('localhost','communi','9AX') or trigger_error("SQL", E_USER_ERROR);
$db = mysql_select_db('communi_vtcrm1',$conn) or trigger_error("SQL", E_USER_ERROR);

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

//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) or trigger_error("SQL", E_USER_ERROR);

//****************************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 = '3824'";
$result = mysql_query($sql1, $conn) or trigger_error("SQL", E_USER_ERROR);
$productstotal = mysql_fetch_row($result);
//*************Update query to insert the Product total into the invoiceCF table****

$sql2="Update vtiger_invoicecf SET cf_603 = $productsubtotal where vtiger_invoicecf.invoiceid = '3824'";
$result1 = mysql_query($sql2, $conn) or trigger_error("SQL", E_USER_ERROR);

//************Math to set the Sales Tax for the products Sold***********************
//$sql2="Update vtiger_invoicecf SET cf_604 = $productsubtotal * .06 where invoiceid ='3824'";
//$result2 = mysql_query($sql2, $conn) or trigger_error("SQL", E_USER_ERROR);

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

//*****************Update to insert the Service total into the invoiceCF table********
//$sql3="Update vtiger_invoicecf SET cf_602 = $servicestotal where invoiceid = '3824'";
//$result3 = mysql_query($sql3, $conn) or trigger_error("SQL", E_USER_ERROR);


//Echo $servicestotal;

Echo $productstotal[0];
mysql_close();
?>

Here's the error:
Parse error: syntax error, unexpected T_CONSTANT_ENCAPSED_STRING in /home/communiq/public_html/crm51/customstuff/test.php on line 27

 

[vacunita]

That's a totally different error than the one you posted before.

So which is it? We can help you if we are guessing here at what is actually wrong.




----------------------------------
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]

there's a typo in Vacunita's reply. He intended to say (i am sure)

We can[red't[/red] help you if we are guessing here at what is actually wrong.

 

[vacunita]

oops! Yes, I meant "[red]can't[/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.

 

[elentz]

Sorry, I have been trying different things, the last I posted is what I have a problem with. If I leave the Echo $productstotal[0]; That shows the first SUM query, which is fine, but I can't figure out how to use that in the next Query where I get the error. I realize it is an array, so what to do about that? It will never need to be an array.

 

[vacunita]

O.K., assuming we are working this section of code:

$result = mysql_query($sql1, $conn) or trigger_error("SQL", E_USER_ERROR);
$productstotal = mysql_fetch_row($result);
//*************Update query to insert the Product total into the invoiceCF table****

$sql2="Update vtiger_invoicecf SET cf_603 = $productsubtotal where vtiger_invoicecf.invoiceid = '3824'";
$result1 = mysql_query($sql2, $conn) or trigger_error("SQL", E_USER_ERROR);

You are getting $productstotal from the first query, but you don;t actually use it.

You are using $product[red]sub[/red]total which doesn't appear to be defined anywhere.

So you need to either change $productsubtotal to $productstotal[0] or define $productsubtotal somewhere.


----------------------------------
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]

Making the change you suggested worked. I can't believe I made that mistake!! I must be tired of looking at this stuff.

Thank you very much for your help. Now to make the rest of the code operate the way I want.

Thanks

 

[d0nny]

[red]elentz[/red] - it looks like you might be using vTiger, the open source CRM??
Probably not the right place to discuss this, but I wanted to know what you thought of it as I'm about to implement this in my business.
And what made you choose this over SugerCRM?

 

[elentz]

d0nny,
You are right I am using vTiger. I have been using it for awhile now. At the time I was looking for a CRM SugarCRM didn't do some things I wanted and vTiger did. Right now I can't remember what they were. The one thing I don't like about vTiger is the way it handles products, services and taxes. That is one of the reasons I posted this problem. For our little business we need to keep track of taxes on products and we have services that are non-taxable such as hourly rates, trip charges to get to the customer site, etc.. vTiger only now has the distinction between products and services, so I can run reports on that. Now I am working on a way to keep track of the taxes in a way that I can run the included reports on. Both CRMs have pros and cons, vTiger just happened to be a better fit for us.

HTH

Ed