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

You desperately need to understand that SQL query is just a string of text, being sent to the SQL server. And SQL server do not understand PHP, but SQL only. — Your Common Sense
– Your Common Sense, Commented Aug 1, 2010 at 10:28

mvds · Accepted Answer · 2010-08-01 10:23:34Z

5

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)

edited Aug 1, 2010 at 10:23

answered Aug 1, 2010 at 10:16

mvds

47.4k8 gold badges104 silver badges113 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

HW1 Over a year ago

Thanks for your answer :) I tried your solution but I got this error message : Unknown column 'id_tva' in 'from clause'. The column "id_tva" exists in the table "tva".

HW1 Over a year ago

I find the solution :) I just delete "using...." and replace it by "on invoice_prod.tva = tva.id_tva". Thanks a lot :)

mvds Over a year ago

good to hear, but in your question you really state that both tables have a id_tva field ;-)

kander · Accepted Answer · 2010-08-01 10:18:27Z

0

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?

answered Aug 1, 2010 at 10:18

kander

4,3061 gold badge28 silver badges43 bronze badges

Collectives™ on Stack Overflow

Calling a PHP Function into sql query

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related