PHP Returns No Rows When a Field is Empty

Question

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.

show us full fragment of code with mysql query string please — Alex
– Alex, Commented Apr 30, 2015 at 14:46

Alex · Accepted Answer · 2015-04-30 15:43:25Z

3

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.

edited Apr 30, 2015 at 15:43

answered Apr 30, 2015 at 14:47

Alex

17.3k1 gold badge31 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Hexana Over a year ago

Alex this makes sense to me and I've tried your example but "none" is not getting assigned to the barcode field and still no rows display when there is no barcode stored in the dB.

Hexana Over a year ago

The full query is added. So my while loop etc is just an example

Hexana Over a year ago

BINGO Alex. INNER JOIN c.am ON t.code=am.code was not required (it was getting used in another script). It works now so thank you ever so much for your help. Much appreciated. Hexana

spencer7593 · Accepted Answer · 2015-04-30 16:02:43Z

3

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.

edited Apr 30, 2015 at 16:02

answered Apr 30, 2015 at 14:46

spencer7593

109k15 gold badges122 silver badges148 bronze badges

6 Comments

Emilio Gort Over a year ago

I think he/she should manage that logic in the PHP side. Don't you agree?

Alex Over a year ago

@EmilioGort there is mostly no difference where to handle that

Emilio Gort Over a year ago

@Alex for compatibility with other system or if you use that query/SP in other places and you don't want to see the 'None'...is more that logic or presentation shouldn't be in the DB...an idea, but you always can do what you want...it's a thought

Alex Over a year ago

@EmilioGort there is nothing wrong with compatability if you set psrticular mysql query in particular php file, or object or library. this query belongs to the current code fragment and breaks nothing in others.

Emilio Gort Over a year ago

I agree with you just to make the habit that presentation shouldn't be manage in the db. +1 for you for the ternary option in your answer

|

Collectives™ on Stack Overflow

PHP Returns No Rows When a Field is Empty

2 Answers 2

3 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related