Tutorial :Calling a PHP Function into sql query



Question:

I have a MySQL table called "invoice_prod" :

  id | qte | price | id_tva   

And another table called "tva" :

  id_tva | content  

Now, I want to find the total of an invoice, so I made this query and it works.

$sql_mnt = mysql_query("      SELECT SUM( (qte * price) * ( 1 + (tva / 100))) AS total_un_prod      FROM invoice_prod       WHERE id='$id_invoice'      ");  

I have just one problem with this query. The "tva" return the "id of the tva" and not its content. Which I have to extract its content from the table "tva".

Clearly, this query return this value of the tva : "1", or, it should return "18%" which is the content of the tva's ID.

I tried to make a PHP function that extracts the tva's content from an id and include it into the SQL query like this :

$sql_mnt = mysql_query("      SELECT SUM(( qte * price) * ( 1 + (" .      tva_get_by_id(tva) . "/ 100))) AS total_un_prod      FROM invoice_prod       WHRE id='$id_invoice'      ");  

But it doesn't work.

What should I do ?

Thanks a lot for your help and have a nice day :)


Solution:1

use

$sql_mnt = mysql_query("           SELECT SUM((`qte`*`price`)*(1+(`content`/100))) AS `total_un_prod`           FROM `invoice_prod`           LEFT JOIN `tva` USING (`id_tva`)           WHERE `id`='".mysql_real_escape_string($id_invoice)."'");  

this connects the two tables within mysql, so you can use the content field directly, which I presume holds the TVA percentage.

edit formatted and addressed the escaping of id_invoice as well (I guess it is int so a cast would suffice, but if not, this will work as well)


Solution:2

I am not sure I understand correctly what you're doing here, but it looks like what you want is an INNER JOIN on the tva table, and then use tva.content in your calculation. Is that correct?


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »