PHP/MySQL - Ignoring Null

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

kdidymus
Forum Contributor
Posts: 196
Joined: Tue May 13, 2008 3:37 am

PHP/MySQL - Ignoring Null

Post by kdidymus »

Another stupid question but it's not covered in my PHP primer.

My family tree website uses a MySQL database to populate a PHP page with info on each of my ancestors.

e.g. http://www.didymus.org.uk/display.php?urn=583

For this individual, there are lots of null values (which at the moment are configured to show N/K). What I want to do is SKIP these altogether so that only fields with some data in are displayed. This will reduce the size of the output and make the page look neater.

Any ideas how I can do this? And I'm a PHP numpty so you'll have to be gentle with me!

Thanks in advance.

Kris Didymus.
User avatar
deejay
Forum Contributor
Posts: 201
Joined: Wed Jan 22, 2003 3:33 am
Location: Cornwall

Re: PHP/MySQL - Ignoring Null

Post by deejay »

could you show the code you have so far. you should be able to add something like

Code: Select all

 
 
if (!$value == 'N/K')
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Re: PHP/MySQL - Ignoring Null

Post by aceconcepts »

Instead of using a string as a null value i'd use 0.

It's often easier and more straightfoward to deal with 0 than a string - especially when databases are involved.

Store your null value (0) as an integer.
kdidymus
Forum Contributor
Posts: 196
Joined: Tue May 13, 2008 3:37 am

Re: PHP/MySQL - Ignoring Null

Post by kdidymus »

Here is the code for the display.php file (with the .inc filename masked out for obvious reasons).

What I want the code to do is skip any lines where there is no data on the database (or at very least as has been suggested, a value of '0').

Any suggestions would be gratefully received and as I said before, I'm brand new to this. I've been working with .html for years but this is my first venture in to the world of PHP and MySQL.

Kris.

Code: Select all

<?php
/* Program: display.php
 * Desc:    Displays all details of selected individual.
 */
?>
<html>
<head><title>Results</title></head>
<body>
<?php
include_once("*******.inc");
  $cxn = mysql_connect($host,$user,$password)
         or die ("couldn't connect to server");
  mysql_select_db($database);
  $urn=$_GET['urn'];  //urn passed by hyperlink
  $query = "SELECT * FROM tree WHERE urn='$urn'";
  $result = mysql_query($query)
            or die ("Couldn't execute query.");
 
  /* Display results in a table */
  $urn = ucfirst($urn);
  while($row = mysql_fetch_assoc($result))
  {
     extract($row);
  echo "<font face='Arial' size='3'><b>$surname, $forename $middlenames</font></b>";
  echo "<table cellspacing='0'>";
  echo "<tr><td colspan='3'><hr /></td></tr>";
     echo " <tr>\n
            <td  valign='top'><font face='Arial' size='1'><b>URN:</font></b></td><td  valign='top'><font face='Arial' size='1'>$urn</font></td>\n
        <tr bgcolor='#CCFFCC' valign='top'><td><font face='Arial' size='1'><b>SURNAME:</font></b></td><td  valign='top'><font face='Arial' size='1'>$surname</font></td>\n</tr>
        <tr valign='top'><td><font face='Arial' size='1'><b>FORENAME(S):</font></b></td><td><font face='Arial' size='1'>$forename $middlenames</font></td>\n</tr>
        <tr bgcolor='#CCFFCC' valign='top'><td><font face='Arial' size='1'><b>DATE OF BIRTH:</font></b></td><td><font face='Arial' size='1'>$dateofbirth</font></td>\n</tr>
        <tr valign='top'><td><font face='Arial' size='1'><b>PLACE OF BIRTH:</font></b></td><td><font face='Arial' size='1'>$placeofbirth</font></td>\n</tr>
        <tr bgcolor='#CCFFCC' valign='top'><td><font face='Arial' size='1'><b>MOTHER:</font></b></td><td><font face='Arial' size='1'>$mother</font></td>\n</tr>
        <tr valign='top'><td><font face='Arial' size='1'><b>FATHER:</font></b></td><td><font face='Arial' size='1'>$father</font></td>\n</tr>
        <tr bgcolor='#CCFFCC' valign='top'><td><font face='Arial' size='1'><b>SIBLINGS:</font></b></td><td><font face='Arial' size='1'>$sibling1</font></td>\n</tr>
        <tr bgcolor='#CCFFCC' valign='top'><td></td><td><font face='Arial' size='1'>$sibling2</font></td>\n</tr>
        <tr bgcolor='#CCFFCC' valign='top'><td></td><td><font face='Arial' size='1'>$sibling3</font></td>\n</tr>
        <tr bgcolor='#CCFFCC' valign='top'><td></td><td><font face='Arial' size='1'>$sibling4</font></td>\n</tr>
        <tr bgcolor='#CCFFCC' valign='top'><td></td><td><font face='Arial' size='1'>$sibling5</font></td>\n</tr>
        <tr bgcolor='#CCFFCC' valign='top'><td></td><td><font face='Arial' size='1'>$sibling6</font></td>\n</tr>
        <tr bgcolor='#CCFFCC' valign='top'><td></td><td><font face='Arial' size='1'>$sibling7</font></td>\n</tr>
        <tr bgcolor='#CCFFCC' valign='top'><td></td><td><font face='Arial' size='1'>$sibling8</font></td>\n</tr>
        <tr bgcolor='#CCFFCC' valign='top'><td></td><td><font face='Arial' size='1' bgcolor='#CCFFCC'>$sibling9</font></td>\n</tr>
        <tr bgcolor='#CCFFCC' valign='top'><td></td><td><font face='Arial' size='1'>$sibling10</font></td>\n</tr>
        <tr valign='top'><td><font face='Arial' size='1'><b>CHRISTENING DATE:</font></b></td><td><font face='Arial' size='1'>$christeningdate</font></td>\n</tr>
        <tr bgcolor='#CCFFCC' valign='top'><td><font face='Arial' size='1'><b>CHRISTENING PLACE:</font></b></td><td><font face='Arial' size='1'>$christeningplace</font></td>\n</tr>
        <tr valign='top'><td><font face='Arial' size='1'><b>SPOUSE 1:</font></b></td><td><font face='Arial' size='1'>$spouse1</font></td>\n</tr>
        <tr bgcolor='#CCFFCC' valign='top'><td><font face='Arial' size='1'><b>MARRIAGE DATE:</font></b></td><td><font face='Arial' size='1'>$spouse1marriagedate</font></td>\n</tr>
        <tr valign='top'><td><font face='Arial' size='1'><b>MARRIAGE PLACE:</font></b></td><td><font face='Arial' size='1'>$spouse1marriageplace</font></td>\n</tr>
        <tr bgcolor='#CCFFCC' valign='top'><td><font face='Arial' size='1'><b>CHILDREN:</font></b></td><td><font face='Arial' size='1'>$spouse1child1</font></td>\n</tr>
        <tr bgcolor='#CCFFCC' valign='top'><td></td><td><font face='Arial' size='1' bgcolor='#CCFFCC'>$spouse1child2</font></td>\n</tr>
        <tr bgcolor='#CCFFCC' valign='top'><td></td><td><font face='Arial' size='1' bgcolor='#CCFFCC'>$spouse1child3</font></td>\n</tr>
        <tr bgcolor='#CCFFCC' valign='top'><td></td><td><font face='Arial' size='1'>$spouse1child4</font></td>\n</tr>
        <tr bgcolor='#CCFFCC' valign='top'><td></td><td><font face='Arial' size='1'>$spouse1child5</font></td>\n</tr>
        <tr bgcolor='#CCFFCC' valign='top'><td></td><td><font face='Arial' size='1'>$spouse1child6</font></td>\n</tr>
        <tr bgcolor='#CCFFCC' valign='top'><td></td><td><font face='Arial' size='1'>$spouse1child7</font></td>\n</tr>
        <tr bgcolor='#CCFFCC' valign='top'><td></td><td><font face='Arial' size='1'>$spouse1child8</font></td>\n</tr>
        <tr bgcolor='#CCFFCC' valign='top'><td></td><td><font face='Arial' size='1'>$spouse1child9</font></td>\n</tr>
        <tr bgcolor='#CCFFCC' valign='top'><td></td><td><font face='Arial' size='1'>$spouse1child10</td>\n</tr>
        <tr valign='top'><td><font face='Arial' size='1'><b>SPOUSE 2:</font></b></td><td><font face='Arial' size='1'>$spouse2</font></td>\n</tr>
        <tr bgcolor='#CCFFCC' valign='top'><td><font face='Arial' size='1'><b>MARRIAGE DATE:</font></b></td><td><font face='Arial' size='1'>$spouse2marriagedate</font></td>\n</tr>
        <tr valign='top'><td><font face='Arial' size='1'><b>MARRIAGE PLACE:</font></b></td><td><font face='Arial' size='1'>$spouse2marriageplace</font></td>\n</tr>
        <tr bgcolor='#CCFFCC' valign='top'><td><font face='Arial' size='1'><b>CHILDREN:</font></b></td><td><font face='Arial' size='1'>$spouse2child1</font></td>\n</tr>
        <tr bgcolor='#CCFFCC' valign='top'><td></td><td><font face='Arial' size='1'>$spouse2child2</font></td>\n</tr>
        <tr bgcolor='#CCFFCC' valign='top'><td></td><td><font face='Arial' size='1'>$spouse2child3</font></td>\n</tr>
        <tr bgcolor='#CCFFCC' valign='top'><td></td><td><font face='Arial' size='1'>$spouse2child4</font></td>\n</tr>
        <tr bgcolor='#CCFFCC' valign='top'><td></td><td><font face='Arial' size='1'>$spouse2child5</font></td>\n</tr>
        <tr bgcolor='#CCFFCC' valign='top'><td></td><td><font face='Arial' size='1'>$spouse2child6</font></td>\n</tr>
        <tr bgcolor='#CCFFCC' valign='top'><td></td><td><font face='Arial' size='1'>$spouse2child7</font></td>\n</tr>
        <tr bgcolor='#CCFFCC' valign='top'><td></td><td><font face='Arial' size='1'>$spouse2child8</font></td>\n</tr>
        <tr bgcolor='#CCFFCC' valign='top'><td></td><td><font face='Arial' size='1'>$spouse2child9</font></td>\n</tr>
        <tr bgcolor='#CCFFCC' valign='top'><td></td><td><font face='Arial' size='1'>$spouse2child10</td>\n</tr>
        <tr valign='top'><td><font face='Arial' size='1'><b>OCCUPATION:</font></b></td><td><font face='Arial' size='1'>$occupation</font></td>\n</tr>
        <tr bgcolor='#CCFFCC' valign='top'><td><font face='Arial' size='1'><b>DATE OF DEATH:</font></b></td><td><font face='Arial' size='1'>$dateofdeath</font></td>\n</tr>
        <tr valign='top'><td><font face='Arial' size='1'><b>PLACE OF DEATH:</font></b></td><td><font face='Arial' size='1'>$placeofdeath</font></td>\n</tr>
        <tr bgcolor='#CCFFCC' valign='top'><td><font face='Arial' size='1'><b>CAUSE OF DEATH:</font></b></td><td><font face='Arial' size='1'>$causeofdeath</font></td>\n</tr>
        <tr valign='top'><td><font face='Arial' size='1'><b>NOTES:</font></b></td><td><font face='Arial' size='1'>$notes</font></td>\n</tr>
        <tr bgcolor='#CCFFCC' valign='top'><td><font face='Arial' size='1'><b>CERTIFICATES:</font></b></td><td><font face='Arial' size='1'>$certificates</font></td>\n</tr>
        <tr valign='top'><td><font face='Arial' size='1'><b>CENSUS:</font></b></td><td><font face='Arial' size='1'>$census</font></td>\n</tr>
        <tr bgcolor='#CCFFCC' valign='top'><td><font face='Arial' size='1'><b>PHOTOGRAPHS:</font></b></td><td><font face='Arial' size='1'>$photographs</font></td>\n</tr>
        <tr valign='top'><td><font face='Arial' size='1'><b>SCRAPBOOK:</font></b></td><td><font face='Arial' size='1'>$scrapbook</font></td>\n</tr>
        </tr>\n";
     echo "<tr><td colspan='3'><hr /></td></tr>\n";
  }
  echo "</table>\n";
?>
</body></html>
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: PHP/MySQL - Ignoring Null

Post by onion2k »

aceconcepts wrote:Instead of using a string as a null value i'd use 0.

It's often easier and more straightfoward to deal with 0 than a string - especially when databases are involved.

Store your null value (0) as an integer.
NULL and 0 are not the same. NULL means "there is no value", 0 means "the value is 0".

For example, imagine you're storing the age of a person in a database ... if they've just been born their age would be 0. If you don't know their age it'd be NULL. If you used 0 as NULL in your database every baby would be "unknown age".
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Re: PHP/MySQL - Ignoring Null

Post by aceconcepts »

That's correct. If you write your scripts on the premise that 0 = null then anything greater than 0 would be a valid value.

I suggested the use of 0 because it is often easier to deal with when implementing conditional statements etc...

Code: Select all

 
//sql query
 
if($sqlRow['attnedance']>0)
{
   //we know this person will be attending
}
 
Anyhow, I find it quite useful :D

The baby's age example wouldn't necessarily be accurate because a baby's age is never 0. I would imagine in this instance a date type would be used.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: PHP/MySQL - Ignoring Null

Post by onion2k »

aceconcepts wrote:The baby's age example wouldn't necessarily be accurate because a baby's age is never 0. I would imagine in this instance a date type would be used.
Ok, the age example was a little simple, but there are times where you need to differentiate between 0 and NULL so it's good practise to use NULL properly rather than treating 0 as NULL.
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Re: PHP/MySQL - Ignoring Null

Post by aceconcepts »

I understand that 0 and NULL are different and get treated differently. There instances where I would in fact use NULL (obviously) however, my main point was that a string e.g. 'N/K' is a particularly bad method to use and an integer value would be easier to work with - and obviously so would NULL, respectively (i just didn't mention it).
kdidymus
Forum Contributor
Posts: 196
Joined: Tue May 13, 2008 3:37 am

Re: PHP/MySQL - Ignoring Null

Post by kdidymus »

Okay. So I THINK I understand the example given by aceconcepts. Let me try to articulate my understanding...

Code: Select all

if($sqlRow['$placeofbirth']>0){<td><font face='Arial' size='1'>$placeofbirth</font></td>\n}
I'm GUESSING that this script checks to see if the value of $placeofbirth is more than 0 and if so, it echoes the section of html within the curly brackets.

Am I correct and if NOT, how might I implement this very useful piece of script in my own database?

I need an idiot's guide here!!

KD.
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Re: PHP/MySQL - Ignoring Null

Post by aceconcepts »

Unless you have set the value of $placeOfBirth previously, you would need to out put the value from the query result - also you need to echo you html:

Code: Select all

 
if($sqlRow['$placeofbirth']>0)
{
   echo'<td><font face="Arial" size="1">' . $sqlRow['placeofbirth'] . '</font></td>';
}
 
I just used more lines so it's easier to digest :D

Whenever, you want to output something from PHP to the browser you need to use echo or print - it's like instructing PHP to speak. A lot like when a person is quoted we use "speech marks" to denote this. PHP uses echo'Hello' or echo"Hello".

Also, in your IF statement you are using an arithmetic operator ">" (greater than), so you should ideally have your database table column (placeofbirth) set as an integer.
kdidymus
Forum Contributor
Posts: 196
Joined: Tue May 13, 2008 3:37 am

Re: PHP/MySQL - Ignoring Null

Post by kdidymus »

All sorted. It would appear that my code automatically ignores null values.

http://www.didymus.org.uk/display.php?urn=583

Now that I've changed the unknown values to NULL in the MySQL database, they're overlooked by the code which is EXACTLY what I wanted to achieve.

Thank you all for your help.

Kris.
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Re: PHP/MySQL - Ignoring Null

Post by Mordred »

A couple of hints:
include_once("*******.inc");
-->
include_once("so.what.if.you.know.the.name.it.ends.in.php.inc.php");

$urn=$_GET['urn']; //urn passed by hyperlink by evil hackers
-->
$urn = mysql_real_escape_string($_GET['urn']); //can't touch this
kdidymus
Forum Contributor
Posts: 196
Joined: Tue May 13, 2008 3:37 am

Re: PHP/MySQL - Ignoring Null

Post by kdidymus »

Mordred.

I am intrigued by your last entry. I have added the escape strings to the $GET line. Thank you for that.

It's the one referring to *******.inc that intrigues me most though. Now I know that my .inc file contains all of the login information for my MySQL database. But how do I secure it to ensure nobody can hack in to it?

Thanks in advance.

KD.
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Re: PHP/MySQL - Ignoring Null

Post by Mordred »

If the filename ends with .php, even if the attacker knows its name (like config.inc.php) and can access it from his browser
(i.e. http://site.com/includes/config.inc.php) he will still see no login details, as the include file will be executed as PHP (and won't produce any output)

Of course there are still vulnerabilities that may allow the attacker to get the file, but not through a direct access.

Also, it is better to place the include files in a folder that is NOT accessible through the web. (Again, this will not help with the above-mentioned vulnerabilities, only will stop direct access)
kdidymus
Forum Contributor
Posts: 196
Joined: Tue May 13, 2008 3:37 am

Re: PHP/MySQL - Ignoring Null

Post by kdidymus »

Here's one for you.

I created a webpage with a hyperlink to my .inc file.

When I loaded this webpage in my browser, right-clicked on the hyperlink and downloaded it, voila! So if a hacker can find the file, he can download it. And view its contents. Including my password.

Any idea how I can prevent a hacker exploiting this vulnerability or am I just being paranoid?!

KD.
Post Reply