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 :)

Answer 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)

Answer 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?