How can I Write a Null Value to MySQL Database?
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.
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.
-
- 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?
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.
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.
-
- Joomla! Fledgling
- Posts: 3
- Joined: Mon Jul 11, 2011 10:51 am
Re: How can I Write a Null Value to MySQL Database?
JTable class has method
***
null|string store ([boolean $updateNulls = false])
***
use the next method
store (true)
instead of
store ()
***
null|string store ([boolean $updateNulls = false])
***
use the next method
store (true)
instead of
store ()
-
- 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?
@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.
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.
-
- 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?
Anyone else have any ideas? Does anyone know of a non-commercial component that can store a null into the database?
-
- 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?
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.
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.
- alikon
- 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?
this obvoiulsy not trueI am assuming Joomla cannot actually store a null value into the database
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);
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';
}
-
- 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?
@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!
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!
- alikon
- 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?
as i know if a table fields is declared nullable
for every type (char , decimal,int ect) null vaule is permitted for definition
for every type (char , decimal,int ect) null vaule is permitted for definition
-
- 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?
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".
- alikon
- 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?
maybe your table field is declared nullable with default zero
- Tonie
- Joomla! Master
- Posts: 16553
- Joined: Thu Aug 18, 2005 7:13 am
Re: How can I Write a Null Value to MySQL Database?
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.
- alikon
- 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?
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
but this is related to SQL wich is a standard.
let me make a simple test.... and report back result soon
- alikon
- 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?
so i've created a simple table
issued an insert
and here is the result
so NULL value are inserted in the field
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 */;
Code: Select all
INSERT INTO `dbt`.`forumtesttable` (`f1`, `f2`, `f3`) VALUES ('1', NULL, NULL);
You do not have the required permissions to view the files attached to this post.
- Tonie
- Joomla! Master
- Posts: 16553
- Joined: Thu Aug 18, 2005 7:13 am
Re: How can I Write a Null Value to MySQL Database?
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.
- 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.
-
- 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?
@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.
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.
-
- 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?
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?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.
- alikon
- 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?
maybe all of us have some "right"
i've made this work in joomla without joomla api db class with normal PHP/mysql code
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
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);
but for sure we can write NULL value on a Mysql table nullable field
-
- 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?
@alikon
Either way, I certainly appreciate your comments and continued efforts. Grazie!
Either way, I certainly appreciate your comments and continued efforts. Grazie!
-
- 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?
@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.
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.
- Per Yngve Berg
- Joomla! Master
- Posts: 30929
- Joined: Mon Oct 27, 2008 9:27 pm
- Location: Romerike, Norway
Re: How can I Write a Null Value to MySQL Database?
Is it Jtable that is faulty?
-
- 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?
I believe so.Per Yngve Berg wrote:Is it Jtable that is faulty?
- Per Yngve Berg
- Joomla! Master
- Posts: 30929
- Joined: Mon Oct 27, 2008 9:27 pm
- Location: Romerike, Norway
-
- 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?
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.Per Yngve Berg wrote:File a Bug Tracker Report.
http://joomlacode.org/gf/project/joomla ... er_id=8103
-
- Joomla! Intern
- Posts: 56
- Joined: Tue Mar 29, 2011 3:49 pm
Re: How can I Write a Null Value to MySQL Database?
Just wanted to say that this thread just saved me hours more of headaches - was just having the same problem. Thanks!
-
- 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?
@ 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?
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?
-
- Joomla! Intern
- Posts: 56
- Joined: Tue Mar 29, 2011 3:49 pm
Re: How can I Write a Null Value to MySQL Database?
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 :/
-
- 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?
I will not say anything more, lest I get my hand slapped by some wayward moderator... lol
-
- 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?
I stumbled upon this problem in another framework a few years ago, so this not something unique to Joomla. The problem is this:
Just call this function inside store().
- 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)
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;
}
}
- VipArt
- 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?
I think you should check this solution: https://groups.google.com/forum/?fromgr ... di6j8Xkykc
-
- 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?
@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!