0

I want to sum and join 2 tables. I am getting a wrong answer. What am i doing wrong please

$stmt = $pdo->query("
   SELECT 
   a.prod_name, a.prod_size,
   b.prod_name, b.prod_size,
   SUM(b.qty_bought) qty_bot,
   SUM(a.prod_qty) qty_received
   FROM tbl_distribution a
   JOIN tbl_sales_bar b
   ON a.staff_id = b.staff_id
   WHERE a.staff_id = '$_GET[id]'
   GROUP BY  b.prod_id
");
WHILE($row = $stmt->fetch(PDO::FETCH_ASSOC)){
  echo '<tr>
           <td>'.$row["prod_name"].'</td>           
           <td>'.$row["prod_size"].'</td>
           <td>'.$row["qty_received"].'</td>
           <td>'.$row["qty_bot"].'</td>
           <td></td>
           </tr>';
}

what i want is to get the sum of quantity bought and the sum of product quantity so that when i minus the product quantity for the quantity bought, i will get the balance which is the difference between the product quantity - quantity bought = available quantity

Thanks

Edit: let me use images to show what i intend to have as result:

distribution table

distribution table for a user after grouping

sales table

sales table for user

the result i hope to achieve

thanks

Chidi
  • 35
  • 4
  • 1
    Your query looks good. Can you elaborate more what is wrong at the moment? – Wimanicesir Jan 27 '23 at 12:20
  • 1
    you should add a proper data sample and the expected result as tabular text .. is not clear the meaning of group by b.prod_id – ScaisEdge Jan 27 '23 at 12:21
  • [Tips for asking a good Structured Query Language (SQL) question](https://meta.stackoverflow.com/questions/271055/tips-for-asking-a-good-structured-query-language-sql-question). This really has nothing to do with PHP at all, _unless_ you can show the result is generated correctly by SQL, but then not displayed correctly in the web output. It's not clear if you've tested the SQL independently or not. It's also unclear specifically what the issue is...sample base data with expected output vs current output is required, alongside the description (which by itself is always going to be a bit hazy). – ADyson Jan 27 '23 at 12:22
  • 1
    P.S. **Warning:** Your code is vulnerable to SQL Injection attacks. You should use parameterised queries and prepared statements to help prevent attackers from compromising your database by using malicious input values. http://bobby-tables.com gives an explanation of the risks, as well as some examples of how to write your queries safely using PHP / PDO. **Never** insert unparameterised data directly into your SQL. The way your code is written now, someone could easily steal, incorrectly change, or even delete your data. – ADyson Jan 27 '23 at 12:27
  • https://phpdelusions.net/pdo also contains good examples of writing safe SQL using mysqli. See also: [How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) . Parameterising your queries will also greatly reduce the risk of accidental syntax errors as a result of un-escaped or incorrectly quoted input values. If you learnt your current technique from a tutorial or book, please don't use that resource again. – ADyson Jan 27 '23 at 12:27
  • @Wimanicesir the result is not outputting correctly – Chidi Jan 27 '23 at 20:33
  • @ADyson thanks. will work on the injection – Chidi Jan 27 '23 at 20:35

0 Answers0