Removing null values from mysql output

craptakular

craptakular

craptakular

Soldato
Joined
7 Jan 2007
Posts
10,607
Location
Sussex, UK
How do you go about removing null values from a mysql query into php?

I have a table like this:

col1----col2----col3
dog-----Cow----NULL
NULL---Cat-----NULL
cat-----dog-----frog

How can I get it to display like this in php:

dog----cow
cow
cat----dog----frog

Do I need to use isset() or is_null()? If so, how?

Hope you can help!
 
Does MySQL have an equivalent of the COALESCE function in MSSQL?

If so you can use that to return the first non null value you specify, i.e.

SELECT COALESCE(col1, 'lol')

Also you could potentially replace the values in your page code if you check the values you are writing back first.
 
Last edited:
When you are looping through each row and then printing the columns from each row can't you just check if the column value is null?

For example:

Code: 
loop through rows{
   loop through column values{
      if(column value is null){
         print ""
      }else{
         print column value
      }
   }
}
 
Null in the database is intended, I want it to completely delete the value so it doesn't print anything, but prints all over values in different columns.

Sorry I should have said I am a beginner, going to try a bit more now.
 
Well, it looks like COALESCE works with MYSQL too.

This for example will return empty quotes if the field would otherwise contain a NULL.

Code: 
-- Create Temp table
CREATE TABLE #TempTable
(
	product_id int,
	product_description varchar(50)
);

-- INSERT records
INSERT INTO #TempTable
(product_id, product_description)
VALUES
(1, 'Product 1');

INSERT INTO #TempTable
(product_id, product_description)
VALUES
(2, NULL);

INSERT INTO #TempTable
(product_id, product_description)
VALUES
(3, 'Product 3');

-- Either return column value, or empty quotes if value would otherwise be NULL using COALESCE function
SELECT
product_id,
COALESCE(product_description, '') AS [product_description]
FROM
#TempTable;
 
Yeh but what I want to avoid in the echo output is this:

<li>99999</li>
<li></li>
<li>6753</li>
<li>989898989898</li>
<li></li>


Which If I am correct is what your code would produce?

I can't have the blanks or it will mess up when I style the page with css.

The above is the situation I have now, I need to get rid of the blank markup.

EDIT:

I need it to only output this:

<li>99999</li>
<li>6753</li>
<li>989898989898</li>

When in the database it will be:

99999
NULL
6753
989898989898
NULL
 
Last edited:
PHP: 
           $query =		("SELECT * 
						FROM operators
						JOIN products
						ON operators.idoperators = products.idoperator JOIN sales
						ON products.idProducts = sales.idproducts 
						ORDER BY purchasedate  DESC
                        LIMIT 3");
					
					
$result = mysql_query($query) or die(mysql_error());

	

// Print out the contents of each row into a table 
	while($row = mysql_fetch_array($result)) {
		
		$html = <<<EOF
<div class="top3">

<div class="operator">{$row['operator']}</div>

<div class="product">{$row['product']}</div>

<div class="price">{$row['sale1']}</div>

<div class="price">{$row['sale2']}</div>

<div class="price">{$row['sale3']}</div>

<div class="price">{$row['sale4']}</div>

<div class="price">{$row['sale5']}</div>

<div class="price">{$row['purchasedate']}</div>

</div>
EOF;
		echo $html;
	}
	
mysql_close();
?>

The above works atm but it displayed blank html markup, say if sale5 is NULL on a certain row but in others it could be a numeric value, which I need to remove, yes the html is awful, but I need a working script first :p
 
PHP: 
    function print_field($class,$content)
    {
    	$html = '';
    	if (!$content)
    		return $html;
    	$html = <<<EOF
    		<div class "{$class}">{$content}</div>
EOF;
		return $html;
    }

// Print out the contents of each row into a table 
    while($row = mysql_fetch_array($result)) 
    {
        $html = <<<EOF
<div class="top3">
EOF;

        $html .= print_field('operator',$row['operator']);
        $html .= print_field('product',$row['product']);
        $html .= print_field('price',$row['sale1']);
        $html .= print_field('price',$row['sale2']);
        $html .= print_field('price',$row['sale3']);
        $html .= print_field('price',$row['sale4']);
        $html .= print_field('price',$row['sale5']);
        $html .= print_field('price',$row['purchasedate']);
	
        $html .= <<<EOF
</div>
EOF;
        echo $html;
    }
 
Code: 
<?php
// Print out the contents of each row into a table
while($row = mysql_fetch_array($result)) {
?>
<div class="top3">
    <?=(!empty($row['operator']))?"<div class=\"operator\">".$row['operator']."</div>":""?>
    <?=(!empty($row['product']))?"<div class=\"product\">".$row['product']."</div>":""?>
    <?=(!empty($row['sale1']))?"<div class=\"price\">".$row['sale1']."</div>":""?>
    <?=(!empty($row['sale2']))?"<div class=\"price\">".$row['sale2']."</div>":""?>
    <?=(!empty($row['sale3']))?"<div class=\"price\">".$row['sale3']."</div>":""?>
    <?=(!empty($row['sale4']))?"<div class=\"price\">".$row['sale4']."</div>":""?>
    <?=(!empty($row['sale5']))?"<div class=\"price\">".$row['sale5']."</div>":""?>
    <?=(!empty($row['purchasedate']))?"<div class=\"price\">".$row['purchasedate']."</div>":""?>
</div>
<?php
}
mysql_close();
?>

Not very neat but it'll work......

Edit - If you want to buffer the output then use ob_start() etc
 
You must log in or register to reply here.
Back
Top Bottom