Second JOIN statement creates warning in mySQL query

Question

I'm trying out below to connect three tables. Once I add the second JOIN statement I get a warning mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in...

Am I doing something wrong here?

$sql ="SELECT a.productcode, a.description, a.QtyDWN as `Qty in System`, b.`PF Counted`, b.`Bulk Counted`, b.`Tot`, ( `Tot`-a.QtyDWN) as `Variance`, c.`Price`
FROM pludata as a
JOIN (
    SELECT CODE, SUM(QTY_PF) AS `PF Counted`, SUM(QTY_BULK) AS `Bulk Counted`, SUM(QTY_PF + QTY_BULK) as `Tot`
    FROM stock_take_data
    
    GROUP BY CODE
) AS b ON a.productcode=b.CODE

JOIN (
    SELECT PCODE, PLAST_COST AS `Price`
    FROM last_cost
    GROUP BY PCODE
) AS c ON a.productcode=c.PCODE

GROUP BY a.productcode
ORDER BY `Variance` ASC";

PHP Snippet:

$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
    ?><div class='table-responsive-md'>
    <table class='table'>
    <thead>
      <tr>
          <th scope='col'>Code</th>
          <th scope='col'>Description</th>
          <th scope='col'>Qty in System</th>
          <th scope='col'>PF Counted</th>
          <th scope='col'>Bulk Counted</th>
          <th scope='col'>Tot Counted</th>
          <th scope='col'>Var</th>
          <th scope='col'>Re-count</th>
      </tr>
      </thead><?php
  while($row = mysqli_fetch_assoc($result)) {

    echo "<tr>
    <td>".$row["productcode"]."</td>
    <td>".$row["description"]."</td>
    <td>".$row["Qty in Sybiz"]."</td>
    <td>".$row["PF Counted"]."</td>
    <td>".$row["Bulk Counted"]."</td>
    <td>".$row["Tot"]."</td>
    <td>".$row["Variance"]."</td>
    <td>".$row["Price"]."</td> 
    </tr>";
  }

When you use `GROUP BY PCODE` you should use an aggregation function for other columns, e.g. `SELECT PCODE, MAX(PLAST_COST) AS Price` — Barmar, Feb 19 '21 at 00:02

score 0 · Answer 1 · answered Feb 18 '21 at 23:42

0

mysqli_query() returns FALSE in case of error.

You should check if the query was successfull, just one by one.

answered Feb 18 '21 at 23:42

Jonathan Aráujo

1
1

Welcome to Stack Overflow! This is really a comment, not an answer. With a bit more rep, [you will be able to post comments](//stackoverflow.com/privileges/comment). – Barmar Feb 19 '21 at 00:00

Second JOIN statement creates warning in mySQL query

1 Answers1