How can I Write a Null Value to MySQL Database?

General questions relating to Joomla! 1.5 There are other boards for more specific help on Joomla! features and extensions.

Moderator: General Support Moderators

Forum rules
Forum Rules
Absolute Beginner's Guide to Joomla! <-- please read before posting.
Forum Post Assistant - If you are serious about wanting help, you should use this tool to help you post.
Locked
BoardBoss
Joomla! Ace
Joomla! Ace
Posts: 1210
Joined: Sat Mar 18, 2006 10:41 am
Location: Borgarnes, Iceland

How can I Write a Null Value to MySQL Database?

Post by BoardBoss » Wed Sep 28, 2011 5:09 am

Joomla 1.5.23
PHP 5.3.8
MySQL 5.0.92-community

I have a database table that has multiple integer int(11) and decimal(5,2) field types, all set to allow null and with a default of null ("null" checkbox checked). I can null the field manually using SQL, but when Joomla writes to the field, it cannot write a null. For the int(11), it stores "0" instead of null, and for the decimal(5,2), it stores "0.00" instead of null.

Any ideas? This has me (and a few others) stumped.

svolga
Joomla! Fledgling
Joomla! Fledgling
Posts: 3
Joined: Mon Jul 11, 2011 10:51 am

Re: How can I Write a Null Value to MySQL Database?

Post by svolga » Wed Sep 28, 2011 6:40 am

JTable class has method
***
null|string store ([boolean $updateNulls = false])
***
use the next method
store (true)

instead of

store ()

BoardBoss
Joomla! Ace
Joomla! Ace
Posts: 1210
Joined: Sat Mar 18, 2006 10:41 am
Location: Borgarnes, Iceland

Re: How can I Write a Null Value to MySQL Database?

Post by BoardBoss » Thu Sep 29, 2011 3:24 pm

@svolga

Thank you for the reply. I was out of my office most of yesterday, and just had someone try what you suggested. Unfortunately, it did not work. I would appreciate any additional assistance you can offer.

BoardBoss
Joomla! Ace
Joomla! Ace
Posts: 1210
Joined: Sat Mar 18, 2006 10:41 am
Location: Borgarnes, Iceland

Re: How can I Write a Null Value to MySQL Database?

Post by BoardBoss » Sat Oct 01, 2011 11:18 am

Anyone else have any ideas? Does anyone know of a non-commercial component that can store a null into the database?

BoardBoss
Joomla! Ace
Joomla! Ace
Posts: 1210
Joined: Sat Mar 18, 2006 10:41 am
Location: Borgarnes, Iceland

Re: How can I Write a Null Value to MySQL Database?

Post by BoardBoss » Fri Oct 07, 2011 3:11 pm

Since I received no definitive answers, I am assuming Joomla cannot actually store a null value into the database. Can someone that really knows Joomla confirm or deny that? What I ended up doing was hiring a programmer to resolve this.

What he did was make a small change in a Joomla core file, which was libraries/joomla/database/database.php. I then made minor changes in the component being developed. Now null values for int and decimal field types can be written into the database.

If the Joomla development team is interested in including this into core Joomla code, please contact me. I am willing to contribute this to the community; however, I do not want to accomplish that by just giving it to everyone that asks. I think it would be better to let the devs make that decision so it can be support properly.

Note: I use MySQLi.

User avatar
alikon
Joomla! Champion
Joomla! Champion
Posts: 5941
Joined: Fri Aug 19, 2005 10:46 am
Location: Roma
Contact:

Re: How can I Write a Null Value to MySQL Database?

Post by alikon » Fri Oct 07, 2011 4:44 pm

I am assuming Joomla cannot actually store a null value into the database
this obvoiulsy not true

Assuming that f3 is the nullable field of table the mysql way

Code: Select all

INSERT INTO #_table (F1, F2, F3) VALUES ('A','B',NULL); 
the joomla way

Code: Select all

$db = JFactory::getDbo();
$query="INSERT INTO #_table (F1, F2, F3) VALUES ('A','B',NULL)";
$db->setQuery($query);
$db->query();
if ($db->getErrorNum())
{
echo 'error';
}
Nicola Galgano
i know that i don't know
www.alikonweb.it

BoardBoss
Joomla! Ace
Joomla! Ace
Posts: 1210
Joined: Sat Mar 18, 2006 10:41 am
Location: Borgarnes, Iceland

Re: How can I Write a Null Value to MySQL Database?

Post by BoardBoss » Fri Oct 07, 2011 5:03 pm

@alikon Interesting. I have many developers (more than 3) that have told me, and showed me with actual tests that included using MySQLi to view the results actually written to the database, that a NULL could not be written to field types int and decimal.

That said, can you provide me with an example of anywhere in Joomla, or can you provide me with the name of any non-commercial Joomla 1.5 component, that will store a NULL into a MySQL database table field with the types int and decimal?

Thanks!

User avatar
alikon
Joomla! Champion
Joomla! Champion
Posts: 5941
Joined: Fri Aug 19, 2005 10:46 am
Location: Roma
Contact:

Re: How can I Write a Null Value to MySQL Database?

Post by alikon » Fri Oct 07, 2011 5:08 pm

as i know if a table fields is declared nullable
for every type (char , decimal,int ect) null vaule is permitted for definition
Nicola Galgano
i know that i don't know
www.alikonweb.it

BoardBoss
Joomla! Ace
Joomla! Ace
Posts: 1210
Joined: Sat Mar 18, 2006 10:41 am
Location: Borgarnes, Iceland

Re: How can I Write a Null Value to MySQL Database?

Post by BoardBoss » Fri Oct 07, 2011 5:16 pm

The table fields in question are set to allow null, and are null by default when the table is first created. When a field is left empty in the component being developed, the value written to the database was "0" for the field type INT(11), and "0.00" for the field type DECIMAL(5,2). Using SQL statements, we could null the entries manually. Again, however, whenever Joomla was used, the values reverted to "0" and "0.00".

User avatar
alikon
Joomla! Champion
Joomla! Champion
Posts: 5941
Joined: Fri Aug 19, 2005 10:46 am
Location: Roma
Contact:

Re: How can I Write a Null Value to MySQL Database?

Post by alikon » Mon Oct 10, 2011 6:36 am

maybe your table field is declared nullable with default zero
Nicola Galgano
i know that i don't know
www.alikonweb.it

User avatar
Tonie
Joomla! Master
Joomla! Master
Posts: 16553
Joined: Thu Aug 18, 2005 7:13 am

Re: How can I Write a Null Value to MySQL Database?

Post by Tonie » Mon Oct 10, 2011 6:51 am

This is not going to help but I had the same issue. Could not find a solution and because I had done too much coding already I changed the columns to strings, it's now on my to do list.

User avatar
alikon
Joomla! Champion
Joomla! Champion
Posts: 5941
Joined: Fri Aug 19, 2005 10:46 am
Location: Roma
Contact:

Re: How can I Write a Null Value to MySQL Database?

Post by alikon » Mon Oct 10, 2011 7:02 am

maybe i'm confusing cause using several DBMS ORACLE,DB2, and mysql
but this is related to SQL wich is a standard.

let me make a simple test.... and report back result soon
Nicola Galgano
i know that i don't know
www.alikonweb.it

User avatar
alikon
Joomla! Champion
Joomla! Champion
Posts: 5941
Joined: Fri Aug 19, 2005 10:46 am
Location: Roma
Contact:

Re: How can I Write a Null Value to MySQL Database?

Post by alikon » Mon Oct 10, 2011 7:07 am

so i've created a simple table

Code: Select all

CREATE TABLE IF NOT EXISTS `forumtesttable` (
  `f1` int(11) NOT NULL auto_increment,
  `f2` int(3) default NULL,
  `f3` decimal(15,8) default NULL,
  PRIMARY KEY  (`f1`),
  UNIQUE KEY `f1` (`f1`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='forum test' AUTO_INCREMENT=1 ;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
issued an insert

Code: Select all

INSERT INTO `dbt`.`forumtesttable` (`f1`, `f2`, `f3`) VALUES ('1', NULL, NULL);
and here is the result
nullvalue.png
so NULL value are inserted in the field
You do not have the required permissions to view the files attached to this post.
Nicola Galgano
i know that i don't know
www.alikonweb.it

User avatar
Tonie
Joomla! Master
Joomla! Master
Posts: 16553
Joined: Thu Aug 18, 2005 7:13 am

Re: How can I Write a Null Value to MySQL Database?

Post by Tonie » Mon Oct 10, 2011 7:16 am

I had done this as well to check if I was not going mental. The way it would trigger for me:

- I have a form with sports results
- A new record is created by using the 'New' functionality to set up a match
- The score of that match is in the same record, but not filled with a value yet because the match hasn't been played yet
- The form is saved
- In the database, all integer form fields are set to '0' instead of NULL.

If I do the same query straight into Mysql, all integers are NULL.

BoardBoss
Joomla! Ace
Joomla! Ace
Posts: 1210
Joined: Sat Mar 18, 2006 10:41 am
Location: Borgarnes, Iceland

Re: How can I Write a Null Value to MySQL Database?

Post by BoardBoss » Mon Oct 10, 2011 7:19 am

@alikon

In your test, did you use SQL statements to store this in your DB? It looks like you did. As I said in my first post, I am able to null the fields using SQL statements. It is when I use Joomla's write-to-database methodology that a null cannot be written to the database.

BoardBoss
Joomla! Ace
Joomla! Ace
Posts: 1210
Joined: Sat Mar 18, 2006 10:41 am
Location: Borgarnes, Iceland

Re: How can I Write a Null Value to MySQL Database?

Post by BoardBoss » Mon Oct 10, 2011 7:21 am

Tonie wrote:I had done this as well to check if I was not going mental. The way it would trigger for me:

- I have a form with sports results
- A new record is created by using the 'New' functionality to set up a match
- The score of that match is in the same record, but not filled with a value yet because the match hasn't been played yet
- The form is saved
- In the database, all integer form fields are set to '0' instead of NULL.

If I do the same query straight into Mysql, all integers are NULL.
This sounds like what I am experiencing. Is this how Joomla currently works (by design) in 1.5.23, or would it be considered a bug?

User avatar
alikon
Joomla! Champion
Joomla! Champion
Posts: 5941
Joined: Fri Aug 19, 2005 10:46 am
Location: Roma
Contact:

Re: How can I Write a Null Value to MySQL Database?

Post by alikon » Mon Oct 10, 2011 7:45 am

maybe all of us have some "right"
i've made this work in joomla without joomla api db class with normal PHP/mysql code

Code: Select all

$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("171", $con);  
$query="INSERT INTO forumtesttable (f2, f3) VALUES ( NULL, NULL);";     
mysql_query($query) or die('Error, insert query failed');
mysql_close($con);
so i don't know if joomla db class handle this in some particular way and for some reason

but for sure we can write NULL value on a Mysql table nullable field
Nicola Galgano
i know that i don't know
www.alikonweb.it

BoardBoss
Joomla! Ace
Joomla! Ace
Posts: 1210
Joined: Sat Mar 18, 2006 10:41 am
Location: Borgarnes, Iceland

Re: How can I Write a Null Value to MySQL Database?

Post by BoardBoss » Mon Oct 10, 2011 11:48 am

@alikon

Either way, I certainly appreciate your comments and continued efforts. :) Grazie!

BoardBoss
Joomla! Ace
Joomla! Ace
Posts: 1210
Joined: Sat Mar 18, 2006 10:41 am
Location: Borgarnes, Iceland

Re: How can I Write a Null Value to MySQL Database?

Post by BoardBoss » Thu Dec 22, 2011 11:22 am

@Tonie

The bad news: I think there is a problem with the Joomla! code that is causing this.

The good news: I believe there is a relatively easy fix for it; I got it solved for 1.5.X. At least I did it for MySQLi, which is what I needed.

May I send you the file via PM so it can be tested, and possibly updated in future releases? If it works, then I look into it for 1.7.x.

User avatar
Per Yngve Berg
Joomla! Master
Joomla! Master
Posts: 30809
Joined: Mon Oct 27, 2008 9:27 pm
Location: Romerike, Norway

Re: How can I Write a Null Value to MySQL Database?

Post by Per Yngve Berg » Thu Dec 22, 2011 4:13 pm

Is it Jtable that is faulty?

BoardBoss
Joomla! Ace
Joomla! Ace
Posts: 1210
Joined: Sat Mar 18, 2006 10:41 am
Location: Borgarnes, Iceland

Re: How can I Write a Null Value to MySQL Database?

Post by BoardBoss » Tue Jan 10, 2012 7:32 pm

Per Yngve Berg wrote:Is it Jtable that is faulty?
I believe so.

User avatar
Per Yngve Berg
Joomla! Master
Joomla! Master
Posts: 30809
Joined: Mon Oct 27, 2008 9:27 pm
Location: Romerike, Norway

Re: How can I Write a Null Value to MySQL Database?

Post by Per Yngve Berg » Tue Jan 10, 2012 8:34 pm


BoardBoss
Joomla! Ace
Joomla! Ace
Posts: 1210
Joined: Sat Mar 18, 2006 10:41 am
Location: Borgarnes, Iceland

Re: How can I Write a Null Value to MySQL Database?

Post by BoardBoss » Wed Jan 11, 2012 12:55 am

Per Yngve Berg wrote:File a Bug Tracker Report.

http://joomlacode.org/gf/project/joomla ... er_id=8103
It is my understanding (after briefly researching) that this is a known issue, and a decision was made not to fix it. Since this fix is working for me, it might be a fix the devs could incorporate into the Joomla core code, if they choose to do so. Someone with more knowledge (and desire) would need to test this possible fix to see if it meshes well with the Joomla framework. I have this working in 1.5, and the fix has been made in 1.7 as well.

Gistapulous
Joomla! Intern
Joomla! Intern
Posts: 56
Joined: Tue Mar 29, 2011 3:49 pm

Re: How can I Write a Null Value to MySQL Database?

Post by Gistapulous » Wed Mar 07, 2012 10:51 pm

Just wanted to say that this thread just saved me hours more of headaches - was just having the same problem. Thanks!

BoardBoss
Joomla! Ace
Joomla! Ace
Posts: 1210
Joined: Sat Mar 18, 2006 10:41 am
Location: Borgarnes, Iceland

Re: How can I Write a Null Value to MySQL Database?

Post by BoardBoss » Thu Mar 08, 2012 1:26 am

@ Gistapulous

Glad I could help. I had to wind up making my code work with the broken Joomla code because it's not right that I ask people to update Joomla core files, and then potentially have to redo those updates again after upgrading Joomla. I really don't know why one of the devs for Joomla hasn't done something with this. I mean, really, does it get any easier than somebody giving you the fix? :)

Gistapulous
Joomla! Intern
Joomla! Intern
Posts: 56
Joined: Tue Mar 29, 2011 3:49 pm

Re: How can I Write a Null Value to MySQL Database?

Post by Gistapulous » Thu Mar 08, 2012 2:45 am

I'm just getting involved with developing for Joomla - these little surprises are certainly keeping Google busy ;) I'm sure there's a reason - just be nice if it was a bit more published :/

BoardBoss
Joomla! Ace
Joomla! Ace
Posts: 1210
Joined: Sat Mar 18, 2006 10:41 am
Location: Borgarnes, Iceland

Re: How can I Write a Null Value to MySQL Database?

Post by BoardBoss » Thu Mar 08, 2012 2:50 am

I will not say anything more, lest I get my hand slapped by some wayward moderator... lol

cmedia
Joomla! Fledgling
Joomla! Fledgling
Posts: 2
Joined: Fri Mar 02, 2012 11:33 am
Location: Västerås, Sweden
Contact:

Re: How can I Write a Null Value to MySQL Database?

Post by cmedia » Thu Mar 29, 2012 8:09 am

I stumbled upon this problem in another framework a few years ago, so this not something unique to Joomla. The problem is this:
  • Empty values in a form are not translated to NULL before inserted into the database (by Joomla)
  • Empty values in a MySQL INSERT INTO query are translated to 0 for INTEGER fields (by MySQL)
So I fixed this in my table class by changing all empty strings for fields that allow NULL to the actual NULL value. This seems to work, but I haven't tested it thoroughly yet. I'm using version 2.5.

Code: Select all

    /**
     * Change empty strings to NULL if allowed
     */
    protected function nullifyEmptyStrings()
    {
        $fields = $this->getFields();

        foreach (get_object_vars($this) as $fieldName => $fieldValue)
        {
            if ($fieldValue !== '')
            {
                // ignore values that are not empty strings
                continue;
            }

            if (!isset($fields[$fieldName]))
            {
                // ignore internal properties that aren't fields in the table
                continue;
            }

            $fieldInfo = $fields[$fieldName];

            if ($fieldInfo->Null == 'NO')
            {
                // ignore fields that don't allow NULL
                continue;
            }

            // set field value to NULL
            $this->$fieldName = NULL;
        }
    }
Just call this function inside store().

User avatar
VipArt
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 114
Joined: Thu Nov 24, 2005 1:05 pm
Location: Vilnius, Lithuania
Contact:

Re: How can I Write a Null Value to MySQL Database?

Post by VipArt » Mon Apr 30, 2012 12:25 pm

I think you should check this solution: https://groups.google.com/forum/?fromgr ... di6j8Xkykc

BoardBoss
Joomla! Ace
Joomla! Ace
Posts: 1210
Joined: Sat Mar 18, 2006 10:41 am
Location: Borgarnes, Iceland

Re: How can I Write a Null Value to MySQL Database?

Post by BoardBoss » Mon Apr 30, 2012 12:32 pm

@VipArt Thanks for the follow-up. I have the same, or similar, solution. I just wish the Joomla devs would incorporate a fix (I offered mine free of charge) into the core code so folks don't have to keep looking for such workarounds. Cheers!


Locked

Return to “General Questions/New to Joomla! 1.5”