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!
 
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.
 
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
 
I still can't get it working, I'll take a look tomorrow with fresh eyes. Thanks everyone for getting this working...

visibleman, after some reading it appears the ? !empty :

Is a fancy way of doing an if statement.

If a value is not emtpy then execute the bit after ? else its false (colon) and output "" which outputs no html markup, therefore it doesn't show anything on my html page.

Is that it in very basic terms?
 
visibleman said:
It's called a ternary operator or ternary if statement; essentially a shortened if statement -
( *condition* ) ? *condition true* : *condition false*

And yup, it'll work as you suggested and won't output that particular line/HTML if the table column field is empty (NULL).


However, is there a particular reason why you're using heredoc syntax for strings rather than the standard quotation in your examples?
Click to expand...

No, no reason, it's just how I learnt to do it from googling. I have bought a couple of php books and am slowly going to go through them, IF I can understand variables, strings, operators, functions & classes I think that will cover. I know a lot more now than I did in 2010, but I haven't followed a structure of learning as I am trying to get my first project completed.

I have found a nice beginner guide at http://www.tuxradar.com/practicalphp
 
You must log in or register to reply here.
Back
Top Bottom