3 Replies Latest reply on Dec 19, 2007 3:04 PM by Günter Schenk

    Working with Nulls

    Level 1
      Hello,

      I was hoping to get some advice on working with NULL values. In the past I have preferred to set my database fields to not NULL and when an empty input field is submitted through a form, the corresponding database field stays empty. I have just started using the Developer Toolbox and for insert and update transactions it adds NULL for empty input fields.

      I'm sure I can get into the ADDT code and change it to insert '' rather than 'NULL', but I'm not sure that's such a good idea.

      Is using NULL values simply a 'best practice' for working with fields holding no data? Is there an easy work around for inserting nothing rather than NULL into empty fields?

      I usually use code like if($row_Catalog['category']) or if(!$row_Catalog['category']) rather than if($row_Catalog['category'] == 'NULL')

      I am assuming that the reason for using NULLs is for data integrity.

      In any case, what advice does anyone have regarding this? Does everyone just use NULLs for database fields that can be empty? Is there an easy solution for using ADDT and having it submit nothing for empty input fields? Will I break anything if I get into to ADDT include files and change the code to submit nothing rather than NULL?

      Thanks in advance for your advice.

      Shane
        • 1. Re: Working with Nulls
          Günter Schenk Level 4
          Hi Shane,

          mysql´s column types have NULL as default unless you specify them as NOT NULL. ADDT on the other hand will always insert an empty '' value when a certain form field hasn´t been filled out, and it doesn´t really care or know if you have set a column to NULL or not

          -----
          I usually use code like if($row_Catalog['category']) or if(!$row_Catalog['category'])
          -----

          it seems you´re mistakening stuff here -- a NULL column simply has zero chars stored, and a reliable way to check that with PHP would be to examine the column´s string length (PHP function "strlen") like this:

          if (strlen($row_Catalog['category']) == 0)

          -------
          if($row_Catalog['category'] == 'NULL')
          -------

          checking against NULL is something that will have to be done in your query and can´t be done with PHP, because NULL is not a detectable value, it´s a condition -- examples:

          SELECT * FROM table_name WHERE column_name IS NULL

          SELECT * FROM table_name WHERE column_name IS NOT NULL

          ------
          I am assuming that the reason for using NULLs is for data integrity
          ------

          humm, varchar etc columns holding textual information can (and will have to be) NULL, what´s not a problem at all -- other column types holding numerical information (e.g. type "int") at times might deserve to store a default value of 0 in case a field hasn´t been filled out, but that´s up to you to define

          ------
          Will I break anything if I get into to ADDT include files and change the code to submit nothing rather than NULL?
          ------

          well, apart from the fact that it won´t help you at all, I really can´t recommend changing ADDT´s "includes" code unless you know what you´re doing respectively how to revert your changes if things go wrong

          Cheers,
          Günter Schenk
          Adobe Community Expert, Dreamweaver
          • 2. Re: Working with Nulls
            Level 1
            Günter,

            Thanks for the information. I was obviously confused about how NULLs are seen/interpreted by mysql and php.

            In the past I have set all my table fields to be NOT NULL when creating the database tables. Then when I inserted empty values into the database I would just do something like:

            INSERT INTO .... VALUES ('thisdata', 'thatdata', '') Where '' is the empty data.

            Using NULLs would mean the insert would look like:

            INSERT INTO .... VALUES ('thisdata', 'thatdata', NULL)

            I used to think that this required a little more foresite when checking for and working with empty table fields in my code.

            Doing it my old way, I could simply pull a row from a database table and do if($row_Catalog['category'] to see if that table field is empty. If the variable $row_Catalog['category'] is not set to any value when PHP reads that table row in, it would return false. I was not needing to do a whole query for all NULL values in a table, but just needed to check if a particular returned table field was empty.

            After I read your post, I did some testing. Now I realize that I can set the tables to allow for NULL and still use my code the same way.

            In other words, if a field's value is NULL I can still pull in the row and use if($row_Catalog['category'] to check if a value has been set for that variable. I tested it out and it works fine.

            Thanks for your help. Now I understand NULLs much better. You are the workhorse on these forums.

            Shane
            • 3. Re: Working with Nulls
              Günter Schenk Level 4
              Hi Shane,

              ------
              You are the workhorse on these forums
              ------

              that´s why I keep snickering while replying to posts here :-)

              However, as you say you have just started using ADDT, I´d like to point you to the "Show IF Conditional Region" server behaviour (see under "Conditional Regions"). This behaviour will actually wrap whatever page element in e.g. a...

              if (@$row_Catalog['category'] != "")

              ...PHP block, what will do the same as your if($row_Catalog['category']) solution -- but it´s a pretty convenient "helper", also because it lets you define an "has else" alternate element in case your condition doesn´t match.

              I´m sure you can do the same with hand coding and writing some if-else conditions yourself -- but this one is actually much faster and will keep you from coding too much :-)

              Cheers,
              Günter Schenk
              Adobe Community Expert, Dreamweaver