1

i have two tables with fields and values:

TABLE respostas

Id      Corp_resp            Id_perg     Id_quest 
12       0,2                 1             1
13       0,5                 2             1
14       0,7                 3             1
15       0,2                 4             1
16       0,3                 5             1
17       0,6                 6             1

Table menu

Id_perg         percentagemadministrador Id_quest
1                             0.5        1    
2                             0.4        1         
3                             0.3        1          
4                             0,2        1
5                             0,1        1
6                             0,8        1

I want to associate the id_quest to id_perg and multiply the corp_resp with percentagemadministrador of the last 10 rows of the table respostas

total=SUM(corp_resp*percentagemadministrador) of the last 10 rows of the table.

<?php
mysql_connect("localhost","root","") or die(mysql_error());
mysql_select_db("teste") or die(mysql_error()); // selecciona a database do server escolhido

//something like that

$query ="SELECT SUM(menu.percentagemadministrador*respostas.corp_resp) as TOTAL 
         FROM respostas 
         INNER JOIN menu 
         WHERE respostas.id_perg=menu.id_perg 
         AND respostas.id_quest=menu.id_perg 
         LIMIT id_perg=10";

$resultado = mysql_query($query) or die(mysql_error());

if($row = mysql_fetch_array($resultado)){

    echo " <center> Final: </center> ".$row['TOTAL'];
} ?>

Can you help with that?

Christophe Costa
  • 79
  • 1
  • 7
  • I can't see 10 rows here – Strawberry Jan 22 '16 at 13:21
  • what do you mean for associate the id_quest to id_perg.. ? and how the two table are joined?(which key fields) – ScaisEdge Jan 22 '16 at 13:21
  • What have you do done so far to achieve the desired output? – Shadow Jan 22 '16 at 13:23
  • Is a example Strawberry. Id_quest is 1 and inside you have have lots of questions and that are id_perg. – Christophe Costa Jan 22 '16 at 13:44
  • Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). – Jay Blanchard Jan 22 '16 at 13:46

1 Answers1

0

I think you should use a subselect for getting the 10 firts id only

SELECT SUM(menu.percentagemadministrador*respostas.corp_resp) as TOTAL 
FROM respostas INNER JOIN menu 
WHERE respostas.id_perg=menu.id_perg 
AND respostas.id_quest=menu.id_perg 
AND id_perg in (select id_perg from respostas order by id_perg limit 10);

if don't work in we can try with inner join 

SELECT SUM(menu.percentagemadministrador*respostas.corp_resp) as TOTAL 
FROM respostas 
INNER JOIN menu on ( respostas.id_perg=menu.id_perg 
     AND respostas.id_quest=menu.id_perg)  
INNER JOIN  ( select id_perg from  respostas order by id_perg limit 10) as t2 
 on (respostas.id = t2. id)
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107