Mysql default value probem

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

Post Reply
User avatar
cap2cap10
Forum Contributor
Posts: 158
Joined: Mon Apr 14, 2008 11:06 pm

Mysql default value probem

Post by cap2cap10 »

Hello again, PHP technorati. Ok, I think that I have narrowed down the problem to a MySQL issue. Here is the code that checks database:

Code: Select all

[color=#FF4000]for ($i = 1; $i<=10; $i++) {
    if (!is_null($info_1["job_duty_$i"])) {
           print "<b>" . $info_1["start_mo_$i"]
                       . "&nbsp;"
                       . $info_1["start_yr_$i"]
                       . "&nbsp;"
                       . $info_1["to"]
                       . "&nbsp;"
                       . $info_1["end_mo_$i"]
                       . "&nbsp;"
                       . $info_1["end_yr_$i"] . "</b><br><b>"
                       . $info_1["boss_$i"] . "<br>"
                       . $info_1["job_title_$i"] . "</b><br><b>"
                       . $info_1["job_duty_$i"]. "</b><p>";
    }
}[/color]
Well, what I want is for the code to check the database to see if each field ,job_duty, has data. If the job_duty field is occupied with data, then print all associated variables in the afore mentioned code. If the job_duty field is empty, do not print the associated variables and my "To" preposition. Unfortunately, even if job_duty fields are empty it prints empty fields including my "To" preposition. this results in an
awkward looking series of To(s) in one column:
TO
TO
TO
TO
TO.....
This annoys me and it is not acceptable.
Here is the sql code for comparison:

Code: Select all

-- Table structure for table `resume`
--

CREATE TABLE IF NOT EXISTS `resume` (
  `userID` int(10) unsigned NOT NULL default '0',
  `portID` int(10) unsigned NOT NULL,
  `candidateID` int(12) unsigned NOT NULL default '0',
  `res_hard_file` blob NOT NULL,
  `activate_res` varchar(8) NOT NULL default '',
  `res_date` date NOT NULL default '0000-00-00',
  `view_count` int(11) NOT NULL default '0',
  `boss_block` varchar(3) NOT NULL default 'No',
  `object` text NOT NULL,
  `skills` text NOT NULL,
  `boss_1` tinytext NOT NULL,
  `job_title_1` tinytext NOT NULL,
  `start_mo_1` varchar(12) NOT NULL default '',
  `start_yr_1` varchar(4) NOT NULL default '',
  `end_mo_1` varchar(12) NOT NULL default '',
  `end_yr_1` varchar(4) NOT NULL default '',
  `[color=#FF0000]job_duty_1` mediumtext NOT NULL,[/color]
  `boss_2` tinytext NOT NULL,
  `job_title_2` tinytext NOT NULL,
  `start_mo_2` varchar(12) NOT NULL default '',
  `start_yr_2` varchar(4) NOT NULL default '',
  `end_mo_2` varchar(12) NOT NULL default '',
  `end_yr_2` varchar(4) NOT NULL default '',
  `[color=#FF0000]job_duty_2` mediumtext NOT NULL,[/color]
  `boss_3` tinytext NOT NULL,
  `job_title_3` tinytext NOT NULL,
  `start_mo_3` varchar(12) NOT NULL default '',
  `start_yr_3` varchar(4) NOT NULL default '',
  `end_mo_3` varchar(12) NOT NULL default '',
  `end_yr_3` varchar(4) NOT NULL default '',
  `[color=#FF0000]job_duty_3` mediumtext NOT NULL,[/color]
  `boss_4` tinytext NOT NULL,
  `job_title_4` tinytext NOT NULL,
  `start_mo_4` varchar(12) NOT NULL default '',
  `start_yr_4` varchar(4) NOT NULL default '',
  `end_mo_4` varchar(12) NOT NULL default '',
  `end_yr_4` varchar(4) NOT NULL default '',
  `[color=#FF0000]job_duty_4` mediumtext NOT NULL,[/color]
  `boss_5` tinytext NOT NULL,
  `job_title_5` tinytext NOT NULL,
  `start_mo_5` varchar(12) NOT NULL default '',
  `start_yr_5` varchar(4) NOT NULL default '',
  `end_mo_5` varchar(12) NOT NULL default '',
  `end_yr_5` varchar(4) NOT NULL default '',
  `[color=#FF0000]job_duty_5` mediumtext NOT NULL,[/color]
  `boss_6` tinytext NOT NULL,
  `job_title_6` tinytext NOT NULL,
  `start_mo_6` varchar(12) NOT NULL default '',
  `start_yr_6` varchar(4) NOT NULL default '',
  `end_mo_6` varchar(12) NOT NULL default '',
  `end_yr_6` varchar(4) NOT NULL default '',
  `[color=#FF0000]job_duty_6` mediumtext NOT NULL,[/color]
  `boss_7` tinytext NOT NULL,
  `job_title_7` tinytext NOT NULL,
  `start_mo_7` varchar(12) NOT NULL default '',
  `start_yr_7` varchar(4) NOT NULL default '',
  `end_mo_7` varchar(12) NOT NULL default '',
  `end_yr_7` varchar(4) NOT NULL default '',
  `[color=#FF0000]job_duty_7` mediumtext NOT NULL,[/color]
  `boss_8` tinytext NOT NULL,
  `job_title_8` tinytext NOT NULL,
  `start_mo_8` varchar(12) NOT NULL default '',
  `start_yr_8` varchar(4) NOT NULL default '',
  `end_mo_8` varchar(12) NOT NULL default '',
  `end_yr_8` varchar(4) NOT NULL default '',
  `[color=#FF0000]job_duty_8` mediumtext NOT NULL,[/color]
  `boss_9` tinytext NOT NULL,
  `job_title_9` tinytext NOT NULL,
  `start_mo_9` varchar(12) NOT NULL default '',
  `start_yr_9` varchar(4) NOT NULL default '',
  `end_mo_9` varchar(12) NOT NULL default '',
  `end_yr_9` varchar(4) NOT NULL default '',
  `[color=#FF0000]job_duty_9` mediumtext NOT NULL,[/color]
  `boss_10` tinytext NOT NULL,
  `job_title_10` tinytext NOT NULL,
  `start_mo_10` varchar(12) NOT NULL default '',
  `start_yr_10` varchar(4) NOT NULL default '',
  `end_mo_10` varchar(12) NOT NULL default '',
  `end_yr_10` varchar(4) NOT NULL default '',
  `[color=#FF0000]job_duty_10` mediumtext NOT NULL,[/color]
  `school_1` varchar(80) NOT NULL default '',
  `major_1` varchar(40) NOT NULL default '',
  `degree_1` varchar(40) NOT NULL default '',
  `year_1` varchar(4) NOT NULL default '',
  `school_2` varchar(80) NOT NULL default '',
  `major_2` varchar(40) NOT NULL default '',
  `degree_2` varchar(40) NOT NULL default '',
  `year_2` varchar(4) NOT NULL default '',
  `school_3` varchar(80) NOT NULL default '',
  `major_3` varchar(40) NOT NULL default '',
  `degree_3` varchar(40) NOT NULL default '',
  `year_3` varchar(4) NOT NULL default '',
  `school_4` varchar(80) NOT NULL default '',
  `major_4` varchar(40) NOT NULL default '',
  `degree_4` varchar(40) NOT NULL default '',
  `year_4` varchar(4) NOT NULL default '',
  `school_5` varchar(80) NOT NULL default '',
  `major_5` varchar(40) NOT NULL default '',
  `degree_5` varchar(40) NOT NULL default '',
  `year_5` varchar(4) NOT NULL default '',
  `certificate` mediumtext NOT NULL,
  `association` mediumtext NOT NULL,
  `refer` varchar(250) NOT NULL default '',
  `res_stat` varchar(3) NOT NULL default '',
  `pic_stat` varchar(3) NOT NULL default '',
  `file_stat` varchar(3) NOT NULL default '',
  `image_name` varchar(250) NOT NULL default 'none',
  `file_name` varchar(140) NOT NULL default '',
  `cover_name` varchar(140) NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
I just can't see why it is behaving in this way. Please enlighten me.
I hope I was able to clear up any vagueness on my part.
Thanks in advance,

Batoe
cpetercarter
Forum Contributor
Posts: 474
Joined: Sat Jul 25, 2009 2:00 am

Re: Mysql default value probem

Post by cpetercarter »

You have defined the database so that the value in 'job_duty' cannot be null. So the test if (!is_null($info_1["job_duty_$i"])) will always return 'true'. Try changing the test to if (!empty($info_1["job_duty_$i"])).
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: Mysql default value probem

Post by AbraCadaver »

Yes, and even if the default was NULL, MySQL NULL !== PHP null.

Code: Select all

if (!empty($info_1["job_duty_$i"])) {
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Mysql default value probem

Post by mikosiko »

AbraCadaver wrote:Yes, and even if the default was NULL, MySQL NULL !== PHP null.

Code: Select all

if (!empty($info_1["job_duty_$i"])) {
what happens if $info_1["job_duty_$i"] = '0' being '0' a valid value? how !empty() evaluate?

asking just to clarify myself
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Mysql default value probem

Post by Eran »

Acutally, PHP recognizes MySQL NULL as null.
If you want exact testing for nulls, you should be using is_null() or === null. As mikosiko pointed out, empty() would return true on 0 and also on several other instances.
For the specifics, the manual shows all the combinations - http://www.php.net/manual/en/types.comparisons.php
User avatar
cap2cap10
Forum Contributor
Posts: 158
Joined: Mon Apr 14, 2008 11:06 pm

Re: Mysql default value probem

Post by cap2cap10 »

Found the solution :lol: - If field is "not null", php interprets this as"something must be there!", even if the field is empty. So, I set all job_duty fields to "null" which solved the problem! :drunk:

Thanks again for your expertise,


Batoe

"The road ahead is filled with obstacles, but also success!" The unknown Optimist
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Mysql default value probem

Post by mikosiko »

pytrin wrote:Acutally, PHP recognizes MySQL NULL as null.
If you want exact testing for nulls, you should be using is_null() or === null. As mikosiko pointed out, empty() would return true on 0 and also on several other instances.
For the specifics, the manual shows all the combinations - http://www.php.net/manual/en/types.comparisons.php
+1 thank you
Post Reply