0
I need to add the field value from the table I’m working on, but the sum is done by blocks where the user id is the same.
Exemplifying: It is a shopping cart, each registration is a product in the cart with the respective customer’s id. The sum will return the sum of the products in each user’s shopping cart.
I would like to do this sum, either in SQL directly, or in PHP.
Code of consultation:
public function busca(){
$query = "SELECT CLI.CODCLI, CLI.NOME, CLI.ENDCLI AS ENDERECO, CLI.TELCLI AS TEL, CLI.CELCLI AS CEL, " .
" PROD.NOMEPROD AS PRODUTO, CAR.QUANTIDADE, CAR.VALOR, CAR.INGREREMOV, " .
" PAG.NOME AS FORMAPAG, FIN.CODCAR AS NUMPEDIDO, FIN.TROCOPARA AS TROCO " .
" FROM CARRINHO CAR, CLIENTE CLI, PRODUTO PROD, FINALIZADO FIN, PAGAMENTO PAG " .
" WHERE CLI.CODCLI = CAR.CODCLI AND " .
" CAR.CODPROD = PROD.CODPROD AND " .
" (FIN.CODCAR = CAR.IDCAR AND " .
" FIN.CODCLI = CAR.CODCLI) AND " .
" PAG.CODIGO = FIN.FORMAPAG " .
" ORDER BY FIN.HORA";
var_dump($query);
$db = new PDO("mysql:host=localhost; dbname=mizy_sushi_bar; charset=utf8;", "root", "");
$item = $db->prepare($query);
$item->execute();
$count = $item->rowCount();
if ($count > 0) {
$vetor[] = array("resultado" => "ok");
$result = $item->fetchAll(PDO::FETCH_ASSOC);
$pos = $registro = 0;
foreach ($result as $linha) {
foreach ($linha as $key=>$value){
//$keys = array_keys($linha);
//$valor = $linha[$keys[$pos]];
$chave = key($linha);
next($linha);
array_push(strtolower($vetor[$registro][$key]), $valor);
$vetor[$registro][strtolower($key)] = $value;
$pos++;
}
$registro++;
}
return json_encode($vetor, JSON_HEX_TAG | JSON_HEX_APOS | JSON_HEX_AMP | JSON_FORCE_OBJECT);
}
}
What is returned:
{
"0":{
"resultado":"ok",
"codcli":"1",
"nome":"Jhonatan Pereira",
"endereco":"Endereço completo",
"tel":"2100000000",
"cel":"21000000000",
"produto":"Produto Teste2",
"quantidade":"3",
"valor":"90.00",
"ingreremov":"nenhum",
"formapag":"DINHEIRO",
"numpedido":"1",
"troco":"0.00"
},
"1":{
"codcli":"1",
"nome":"Jhonatan Pereira",
"endereco":"Endereço",
"tel":"2100000000",
"cel":"21000000000",
"produto":"Prod Teste",
"quantidade":"5",
"valor":"125.00",
"ingreremov":"nenhum",
"formapag":"DINHEIRO",
"numpedido":"1",
"troco":"0.00"
}
}
NOTE: In this case the third row of the table is not in JSON due to other criteria outside the case in question (purchase not finalized).
What I wish him to return (last line of the two objects):
{
"0":{
"resultado":"ok",
"codcli":"1",
"nome":"Jhonatan Pereira",
"endereco":"Endereço completo",
"tel":"2100000000",
"cel":"21000000000",
"produto":"Produto Teste2",
"quantidade":"3",
"valor":"90.00",
"ingreremov":"nenhum",
"formapag":"DINHEIRO",
"numpedido":"1",
"troco":"0.00",
"soma":"215.00"
},
"1":{
"codcli":"1",
"nome":"Jhonatan Pereira",
"endereco":"Endereço",
"tel":"2100000000",
"cel":"21000000000",
"produto":"Prod Teste",
"quantidade":"5",
"valor":"125.00",
"ingreremov":"nenhum",
"formapag":"DINHEIRO",
"numpedido":"1",
"troco":"0.00",
"soma":"215.00"
}
}
OBS.: the redundancy of the "sum" key is no problem at the moment. The third record, whose value is 60.00, would enter as 60.00 even, for not having other records of the same client.
Link to the . sql file that creates the tables and inserts the data:
https://drive.google.com/file/d/0B0HKv_LLOe9SaklJRkZOaW9mSXM/view?usp=drivesdk
Try using the sum function of sql and group by client
– Nayron Morais
Good, Nayron, it worked, I put a SUM(CAR.VALUE) and before ORDER BY I put GROUP BY CLI.CODCLI, only that already solved. Answer there for me to vote
– Jhonatan Pereira