How to use IF NULL, do this in MySQL?

Question

I'm trying to build a members-system in which you can view certain values from a database. It worked, so that was great, but I'm having a problem now because I'm using a NULL value on 'age'.

When viewing a profile page, it looks like this:

$id = $_GET["id"];

$result = mysql_query("SELECT * FROM membersfromsite WHERE `idofmember`=$_GET[id]");
$row = mysql_fetch_array($result);

echo "<b>" . $row['userusername'] . "</b>: </p>"; ?>

<?php

$id = $_GET["id"];

$result = mysql_query("SELECT * FROM membersfromsite WHERE `idofmember`=$_GET[id]");
$noage = mysql_query("SELECT ISNULL([age]) FROM membersfromsite WHERE         `idofmember`=$_GET[id]");

while ($row = mysql_fetch_array($result))
{
  echo "<p class='middle'>id-number: " . $row['idofmember'] . "<br>";
  echo "Username: " . $row['userusername'] . "<br>";
  if ($noage)
  {
    echo "Age not specified";
  }
  else
  {
    echo "Age: " .$row['age'] ;
  }
}

I have tried all kinds of other things, but the problem which I 'm having is that it either returns 'Age not specified' on every userpage or the age on every userpage, including the pages with a NULL value, which makes it look like:

Age:

The code which you can see above returns the age on every page, including the pages with an age which is set to NULL. What I don't understand is if I change the code to this:

$noage = mysql_query("SELECT * FROM membersfromsite WHERE `idofmember`=$_GET[id] AND age IS NULL");

it simply doesn't work. Since I'm using IS NULL instead of = NULL I don't really see why this shouldn't work, but I guess it has to do with the IF which is inside the 'while' thing, I don't really see in what other way I could fix this though...

I'm having an idea what the problem is, because I think that there is already a MyQS, Query done with Results and $noage is maybe ignored because of this, but I don't know how to solve this.

BTW, please read [How can I prevent SQL injection in PHP?](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) before you put any of this code your website. — Ilmari Karonen, Jan 22 '14 at 13:31

score 1 · Accepted Answer · answered Dec 18 '13 at 17:14

1

You don't need to do a whole separate $noage query.

Just do:

if(!$row['age'])
{
echo "Age not specified";
}
else
{
echo "Age: " .$row['age'] ;
}

answered Dec 18 '13 at 17:14

Digital Chris

6,177
1
20
29

score 0 · Answer 2 · answered Dec 18 '13 at 17:16

0

Instead of if($noage) use if(!$row['age']) and skip the second query.

The other reason your code does not work is that the second query returns an array with something like array('expr1' => 0) which is not false. You only get a false result if nothing is found.

The reason why = NULL does not work? There are books written about it, it is just as it is.

answered Dec 18 '13 at 17:16

Matijs

2,533
20
24

Yeah, I already knew that = NULL doesn't work. I have read somewhere why but can't find it back, I will read about it later. – Ubiz Dec 18 '13 at 18:03

score 0 · Answer 3 · answered Dec 18 '13 at 17:22

Rather than relying on MySQL to tell us if no age was found or not, we can programatically determine whether the age value is Null right in PHP.

Here is an example:

$id = $_GET['id'];
$result = mysql_query("SELECT * FROM membersfromsite WHERE `idofmember` = '" . mysql_real_escape_string($id) . "'");

while($row = mysql_fetch_array($result)) {
  echo '<p class='middle'>id-number: ' . $row['idofmember'] . '<br>';
  echo 'Username: ' . $row['userusername'] . '<br>';
  if($row['age'] === Null) {
    echo "Age not specified";
  } else {
    echo "Age: " .$row['age'] ;
  }
}

I haven't tried this one yet. I think it returns the same as the code of Digital Chris if I look at this piece of code, but thanks for the answer, now I can use this one too in the future. It's really handy to know SQL, just started a few days ago learning it. — Ubiz, Dec 18 '13 at 18:00

How to use IF NULL, do this in MySQL?

3 Answers3