Page 1 of 2
PHP/MySQL - Ignoring Null
Posted: Tue May 13, 2008 6:30 am
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.
Re: PHP/MySQL - Ignoring Null
Posted: Tue May 13, 2008 6:52 am
by deejay
could you show the code you have so far. you should be able to add something like
Re: PHP/MySQL - Ignoring Null
Posted: Tue May 13, 2008 7:39 am
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.
Re: PHP/MySQL - Ignoring Null
Posted: Wed May 14, 2008 12:57 am
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>
Re: PHP/MySQL - Ignoring Null
Posted: Wed May 14, 2008 2:53 am
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".
Re: PHP/MySQL - Ignoring Null
Posted: Wed May 14, 2008 3:21 am
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
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.
Re: PHP/MySQL - Ignoring Null
Posted: Wed May 14, 2008 3:59 am
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.
Re: PHP/MySQL - Ignoring Null
Posted: Wed May 14, 2008 4:14 am
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).
Re: PHP/MySQL - Ignoring Null
Posted: Wed May 14, 2008 4:40 am
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.
Re: PHP/MySQL - Ignoring Null
Posted: Wed May 14, 2008 4:55 am
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
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.
Re: PHP/MySQL - Ignoring Null
Posted: Wed May 14, 2008 5:41 am
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.
Re: PHP/MySQL - Ignoring Null
Posted: Wed May 14, 2008 1:51 pm
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
Re: PHP/MySQL - Ignoring Null
Posted: Tue May 20, 2008 4:03 am
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.
Re: PHP/MySQL - Ignoring Null
Posted: Tue May 20, 2008 5:18 am
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)
Re: PHP/MySQL - Ignoring Null
Posted: Wed May 21, 2008 5:07 am
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.