Sum of two different tables msql php

Question

need help. I would like to display the sum of the amount of Id of the storeId column referring to the names of the storeName column.

Example:

My code is like this, but I’m a beginner and it’s not working:

<?php
    $mysqli = new mysqli("localhost", "user", "password", "banco") or die("Não foi possível a conexão com o Banco");

    $storeId = 1;
    $sql = "SELECT SUM(storeId) as soma FROM cadastro WHERE storeId = $storeId AND storeName = $storeName";
    $sql = $con->query($sql);
    $row = $sql->fetch_assoc();
    $soma = $row['soma'];

    echo 'total de notas de [storeName ('.$storeName.')] [storeId ('.$storeId.')] : '.$soma;
?>

I appreciate the help.

Answer 1

The easiest way is to already calculate in your SQL command.

After creating the connection with the bank do the following:

Create an SQL query that brings the sum to the $resultado_query variable:

$resultado_consulta = "SELECT storeNAme, COUNT(storeID) AS QTD FROM cadastro GROUP BY storeNAme";

Then run the command along with your connection and save to another variable in the case $resultado_usuario_query:

$resultado_usuario_query = mysqli_query($mysqli, $resultado_consulta);

Do this list these values in a variable that will be used as "line":

$row_usuario = mysqli_fetch_assoc($resultado_usuario_query);

Then just call the calculated value with while to where you want, note that I have called the calculated value QTD:

while($row_usuario = mysqli_fetch_assoc($resultado_usuario_query)){ 
                    
        echo "Nome:" . $row_usuario ['storeNAme'] . "";
        echo "Quantidade:" . $row_usuario ['QTD'] . "";

    }

Complete code:

$resultado_consulta = "SELECT storeNAme, COUNT(storeID) AS QTD FROM cadastro GROUP BY storeNAme";

$resultado_usuario_query = mysqli_query($mysqli, $resultado_consulta);

$row_usuario = mysqli_fetch_assoc($resultado_usuario_query);

while($row_usuario = mysqli_fetch_assoc($resultado_usuario_query)){ 
                    
        echo "Nome:" . $row_usuario ['storeNAme'] . "";
        echo "Quantidade:" . $row_usuario ['QTD'] . "";

    }

You can tabulate the result with the html table concept.