Displaying null for unwanted data

[mattix]

I have the following piece of code which extracts the data from mysql table. Since some of the data  have  unwanted characters I use the  preg_replace('/[^0-9]+/','',) function to remove them, and only to grab numbers from the rows. I would like to display null in the table cell for certain values like Tnex>=40, Mode>=20 etc. When I tried to do it in sql  query with where clause,  the rows that don't fit the criteria are not displayed on the table. Instead, I need the rows to be displayed, but just the values to be shown null or empty.  For example, in a certain row if "Tnex" >= 40, then the table cell should be null.  How can I do this? Thanks.

Quote

 

<table class="tbresult">
 <tr>
<th>Date</th> 
<th>Snex</th>
<th>Tnex</th>
<th>Mode</th>
</tr>

 <?php

  include ("config.php");

  $sql = "SELECT Date, Snex, Tnex, Mode FROM datatb";
  $result = $conn->query($sql);
  if ($result->num_rows > 0) {

   while($row = $result->fetch_assoc()) 
    
   {
        echo "<tr><td>" . $row["Date"] . "</td><td>" .  preg_replace('/[^0-9]+/','',$row["Snex"]). "</td><td>". preg_replace('/[^0-9]+/','', $row["Tnex"]). "</td><td>".  preg_replace('/[^0-9]+/','',$row["Mode"]). "</td></tr>";

  }
  
</table>

 

 

[Psycho]

I'm not following. You first state that you are using a regular expression to remove all but numbers from the values. Then you state that you want a value such as "Tnex>=40" to return Null or empty (as opposed to "40"). So, I'm not sure what you really want returned.

Then you state that when you try to add the logic to your query you are not getting the rows that don't match the criteria. But, the query you provided doesn't have any where clause. If you had, I might have a better understanding of what you are really wanting from above.

Lastly, the most important question is WHY are you doing this? If that data is not valid, then you should fix the data instead of creating complex process to handle the bad data on the output. I would update all the values in the database to just the number (or empty if you prefer) and implement logic when saving new values to ensure only valid data is entered.

Although I would NOT advise the approach you are wanting, one of these will do what I think you are wanting

Return the value if it is only a number - else return an empty string

SELECT Date, Tnex, Mode,
       IF(Snex*1 = 0, "", Snex) as Snex
FROM datatb

Return the value with all non-numeric characters removed

SELECT Date, Tnex, Mode,
       REGEXP_REPLACE(Snex, '[^0-9]+', '') as Snex
FROM datatb

Edited July 6, 2018 by Psycho

[mattix]

Thanks for your answer. There are some characters with numbers in the table which sometimes I need them like this, and in this case, i just need the numbers. 

I'm trying to do two things together here.  First is to remove the characters from the data (eg. if it is ".4", then it should be "4"), and after this (or together, I am open to all solutions) I want to filter  show null instead of certain numbers (e.g, if my criteria is for "Tnex" is like Tnex<=10, then I would like to show "null" for  the values from 1 to 10, and only display the rest of the values.

 

Edited July 6, 2018 by mattix