CODEWITHSUNDEEP
phpmysql
Hexana
Hexana

Reputation: 1135

PHP Returns No Rows When a Field is Empty

I have a while loop that iterates through the rows returned from a DB query.

Query

$sql = "SELECT t.barcode AS barcode, t.code, t.brand, t.name, t.cost, t.price, t.vat, SUM(n.stock) AS stock
    FROM t 
    INNER JOIN c.am ON t.code=am.code
    INNER JOIN n ON t.code=n.code WHERE (n.name='X' OR n.name='Y') 
    AND t.code IN ($in)
    GROUP BY t.code, t.name, t.cost, t.salesprice, t.vat";

    $result = $mysqli->query($sql);

  echo "<table id='maintable'><tr><th>Barcode</th><th>Name</th><th>Brand</th></tr>";
  while($row = $result->fetch_array()) {
   if(empty($row['barcode'])){
   $barcode = "none";
   }
   $barcode = $row['barcode'];
   $name = $row['name'];
   $brand = $row['brand'];

   echo "<td>" . $barcode . "</td>";
   echo "<td>" . $name . "</td>";
   echo "<td>" . $brand . "</td>";
  }
  echo "</table>";

The problem is if barcode is empty (not in the DB table), no rows get displayed. However if there is a barcode, the row DOES display. In my example I have tried to check if $row['barcode'] is empty to assign a string so that the row will still display but its unsuccessful.

In the Table itself in the database, the Barcode has a Null field set to YES and Default Field set to NULL so I have also tried:

   if(is_null($row['barcode'])) { ..... };

But unsuccessful.

Somewhere I read that empty can equate to: '', 0 and NULL so I'm thinking it's failing because checking if "empty" is the wrong approach? Any help appreciated.

Upvotes: 1

Views: 70

Answers (2)

spencer7593
spencer7593

Reputation: 108420

Your conditional logic is being followed by this line:

$barcode = $row['barcode'];

Whatever was assigned to $barcode previously is being overwritten.

Looks like you wanted that to be else, or you wanted that line before the conditional test.

As an alternative, you could just modify your query to return the string "none" in place of NULL with an IFNULL function, assuming that barcode is character type.

SELECT IFNULL(t.barcode,'none') AS barcode
     , ...
  FROM t

EDIT

The question has been edited (after I posted my answer), to add a SQL query to the question.

I thought the problem was that an empty (zero length) string was being assigned to a variable, and the intent was to replace the empty string with a literal.

Upvotes: 3

Alex
Alex

Reputation: 17289

if(empty($row['barcode'])){
   $barcode = "none";
} else {
   $barcode = $row['barcode'];
}

or even better:

$barcode = empty($row['barcode'])?"none":$row['barcode'];

So your code would be:

while($row = $result->fetch_array()) {
   $barcode = empty($row['barcode'])?"none":$row['barcode'];
   $name = $row['name'];
   $brand = $row['brand'];

   echo "<td>" . $barcode . "</td>";
   echo "<td>" . $name . "</td>";
   echo "<td>" . $brand . "</td>";
}

UPDATE Not sure about your database structure, but if you need some empty t.barcode values your query could be like:

$sql = "SELECT 
      t.barcode AS barcode, 
      n.code,  
      t.brand, t.name, t.cost, t.price, t.vat, 
      SUM(n.stock) AS stock
FROM n 
LEFT JOIN t 
ON t.code=n.code 
   AND t.code IN ($in)
WHERE (n.name='X' OR n.name='Y') 
GROUP BY n.code, t.name, t.cost, t.salesprice, t.vat";

And I don't understand what is INNER JOIN c.am ON t.code=am.code stands for, maybe you should delete it.

Upvotes: 3

Related Questions