The Joomla! Forum ™

Download

Demo


Board index » Joomla! Older Version Support » Joomla! 1.5 » General Questions/New to Joomla! 1.5

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.



Post new topic Reply to topic  Page 1 of 1
  [ 30 posts ] 
  Print view Previous topic | Next topic 
Author Message
BoardBoss
PostPosted: Wed Sep 28, 2011 5:09 am 
User avatar
Joomla! Ace
Joomla! Ace

Joined: Sat Mar 18, 2006 10:41 am
Posts: 1143
Location: Hvolsvöllur, Iceland
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.
Top
 Profile  
 
svolga
PostPosted: Wed Sep 28, 2011 6:40 am 
Joomla! Fledgling
Joomla! Fledgling

Joined: Mon Jul 11, 2011 10:51 am
Posts: 3
JTable class has method
***
null|string store ([boolean $updateNulls = false])
***
use the next method
store (true)

instead of

store ()
Top
 Profile  
 
BoardBoss
PostPosted: Thu Sep 29, 2011 3:24 pm 
User avatar
Joomla! Ace
Joomla! Ace

Joined: Sat Mar 18, 2006 10:41 am
Posts: 1143
Location: Hvolsvöllur, Iceland
@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.
Top
 Profile  
 
BoardBoss
PostPosted: Sat Oct 01, 2011 11:18 am 
User avatar
Joomla! Ace
Joomla! Ace

Joined: Sat Mar 18, 2006 10:41 am
Posts: 1143
Location: Hvolsvöllur, Iceland
Anyone else have any ideas? Does anyone know of a non-commercial component that can store a null into the database?
Top
 Profile  
 
BoardBoss
PostPosted: Fri Oct 07, 2011 3:11 pm 
User avatar
Joomla! Ace
Joomla! Ace

Joined: Sat Mar 18, 2006 10:41 am
Posts: 1143
Location: Hvolsvöllur, Iceland
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.
Top
 Profile  
 
alikon
PostPosted: Fri Oct 07, 2011 4:44 pm 
User avatar
Joomla! Virtuoso
Joomla! Virtuoso

Joined: Fri Aug 19, 2005 10:46 am
Posts: 4975
Location: Roma
Quote:
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:
INSERT INTO #_table (F1, F2, F3) VALUES ('A','B',NULL);


the joomla way
Code:
$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 - Global Moderator
my knowledge is know to not know - www.alikonweb.it
www.joomladdons.eu - joomla free open source addons
Top
 Profile  
 
BoardBoss
PostPosted: Fri Oct 07, 2011 5:03 pm 
User avatar
Joomla! Ace
Joomla! Ace

Joined: Sat Mar 18, 2006 10:41 am
Posts: 1143
Location: Hvolsvöllur, Iceland
@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!
Top
 Profile  
 
alikon
PostPosted: Fri Oct 07, 2011 5:08 pm 
User avatar
Joomla! Virtuoso
Joomla! Virtuoso

Joined: Fri Aug 19, 2005 10:46 am
Posts: 4975
Location: Roma
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 - Global Moderator
my knowledge is know to not know - www.alikonweb.it
www.joomladdons.eu - joomla free open source addons
Top
 Profile  
 
BoardBoss
PostPosted: Fri Oct 07, 2011 5:16 pm 
User avatar
Joomla! Ace
Joomla! Ace

Joined: Sat Mar 18, 2006 10:41 am
Posts: 1143
Location: Hvolsvöllur, Iceland
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".
Top
 Profile  
 
alikon
PostPosted: Mon Oct 10, 2011 6:36 am 
User avatar
Joomla! Virtuoso
Joomla! Virtuoso

Joined: Fri Aug 19, 2005 10:46 am
Posts: 4975
Location: Roma
maybe your table field is declared nullable with default zero

_________________
Nicola Galgano - Global Moderator
my knowledge is know to not know - www.alikonweb.it
www.joomladdons.eu - joomla free open source addons
Top
 Profile  
 
Tonie
PostPosted: Mon Oct 10, 2011 6:51 am 
User avatar
Joomla! Master
Joomla! Master

Joined: Thu Aug 18, 2005 7:13 am
Posts: 16290
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.

_________________
Joomla forum global moderator.

Take care
Top
 Profile  
 
alikon
PostPosted: Mon Oct 10, 2011 7:02 am 
User avatar
Joomla! Virtuoso
Joomla! Virtuoso

Joined: Fri Aug 19, 2005 10:46 am
Posts: 4975
Location: Roma
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 - Global Moderator
my knowledge is know to not know - www.alikonweb.it
www.joomladdons.eu - joomla free open source addons
Top
 Profile  
 
alikon
PostPosted: Mon Oct 10, 2011 7:07 am 
User avatar
Joomla! Virtuoso
Joomla! Virtuoso

Joined: Fri Aug 19, 2005 10:46 am
Posts: 4975
Location: Roma
so i've created a simple table
Code:
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:
INSERT INTO `dbt`.`forumtesttable` (`f1`, `f2`, `f3`) VALUES ('1', NULL, NULL);

and here is the result
Attachment:
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 - Global Moderator
my knowledge is know to not know - www.alikonweb.it
www.joomladdons.eu - joomla free open source addons
Top
 Profile  
 
Tonie
PostPosted: Mon Oct 10, 2011 7:16 am 
User avatar
Joomla! Master
Joomla! Master

Joined: Thu Aug 18, 2005 7:13 am
Posts: 16290
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.

_________________
Joomla forum global moderator.

Take care
Top
 Profile  
 
BoardBoss
PostPosted: Mon Oct 10, 2011 7:19 am 
User avatar
Joomla! Ace
Joomla! Ace

Joined: Sat Mar 18, 2006 10:41 am
Posts: 1143
Location: Hvolsvöllur, Iceland
@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.
Top
 Profile  
 
BoardBoss
PostPosted: Mon Oct 10, 2011 7:21 am 
User avatar
Joomla! Ace
Joomla! Ace

Joined: Sat Mar 18, 2006 10:41 am
Posts: 1143
Location: Hvolsvöllur, Iceland
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?
Top
 Profile  
 
alikon
PostPosted: Mon Oct 10, 2011 7:45 am 
User avatar
Joomla! Virtuoso
Joomla! Virtuoso

Joined: Fri Aug 19, 2005 10:46 am
Posts: 4975
Location: Roma
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:
$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 - Global Moderator
my knowledge is know to not know - www.alikonweb.it
www.joomladdons.eu - joomla free open source addons
Top
 Profile  
 
BoardBoss
PostPosted: Mon Oct 10, 2011 11:48 am 
User avatar
Joomla! Ace
Joomla! Ace

Joined: Sat Mar 18, 2006 10:41 am
Posts: 1143
Location: Hvolsvöllur, Iceland
@alikon

Either way, I certainly appreciate your comments and continued efforts. :) Grazie!
Top
 Profile  
 
BoardBoss
PostPosted: Thu Dec 22, 2011 11:22 am 
User avatar
Joomla! Ace
Joomla! Ace

Joined: Sat Mar 18, 2006 10:41 am
Posts: 1143
Location: Hvolsvöllur, Iceland
@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.
Top
 Profile  
 
Per Yngve Berg
PostPosted: Thu Dec 22, 2011 4:13 pm 
Joomla! Master
Joomla! Master

Joined: Mon Oct 27, 2008 9:27 pm
Posts: 13729
Location: Akershus, Norway
Is it Jtable that is faulty?
Top
 Profile  
 
BoardBoss
PostPosted: Tue Jan 10, 2012 7:32 pm 
User avatar
Joomla! Ace
Joomla! Ace

Joined: Sat Mar 18, 2006 10:41 am
Posts: 1143
Location: Hvolsvöllur, Iceland
Per Yngve Berg wrote:
Is it Jtable that is faulty?


I believe so.
Top
 Profile  
 
Per Yngve Berg
PostPosted: Tue Jan 10, 2012 8:34 pm 
Joomla! Master
Joomla! Master

Joined: Mon Oct 27, 2008 9:27 pm
Posts: 13729
Location: Akershus, Norway
File a Bug Tracker Report.

http://joomlacode.org/gf/project/joomla/tracker/?action=TrackerItemBrowse&tracker_id=8103
Top
 Profile  
 
BoardBoss
PostPosted: Wed Jan 11, 2012 12:55 am 
User avatar
Joomla! Ace
Joomla! Ace

Joined: Sat Mar 18, 2006 10:41 am
Posts: 1143
Location: Hvolsvöllur, Iceland
Per Yngve Berg wrote:
File a Bug Tracker Report.

http://joomlacode.org/gf/project/joomla/tracker/?action=TrackerItemBrowse&tracker_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.
Top
 Profile  
 
Gistapulous
PostPosted: Wed Mar 07, 2012 10:51 pm 
Joomla! Intern
Joomla! Intern

Joined: Tue Mar 29, 2011 3:49 pm
Posts: 53
Just wanted to say that this thread just saved me hours more of headaches - was just having the same problem. Thanks!
Top
 Profile  
 
BoardBoss
PostPosted: Thu Mar 08, 2012 1:26 am 
User avatar
Joomla! Ace
Joomla! Ace

Joined: Sat Mar 18, 2006 10:41 am
Posts: 1143
Location: Hvolsvöllur, Iceland
@ 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? :)
Top
 Profile  
 
Gistapulous
PostPosted: Thu Mar 08, 2012 2:45 am 
Joomla! Intern
Joomla! Intern

Joined: Tue Mar 29, 2011 3:49 pm
Posts: 53
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 :/
Top
 Profile  
 
BoardBoss
PostPosted: Thu Mar 08, 2012 2:50 am 
User avatar
Joomla! Ace
Joomla! Ace

Joined: Sat Mar 18, 2006 10:41 am
Posts: 1143
Location: Hvolsvöllur, Iceland
I will not say anything more, lest I get my hand slapped by some wayward moderator... lol
Top
 Profile  
 
cmedia
PostPosted: Thu Mar 29, 2012 8:09 am 
Joomla! Fledgling
Joomla! Fledgling

Joined: Fri Mar 02, 2012 11:33 am
Posts: 2
Location: Västerås, Sweden
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:
    /**
     * 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().
Top
 Profile  
 
VipArt
PostPosted: Mon Apr 30, 2012 12:25 pm 
User avatar
Joomla! Enthusiast
Joomla! Enthusiast

Joined: Thu Nov 24, 2005 1:05 pm
Posts: 113
Location: Vilnius, Lithuania
I think you should check this solution: https://groups.google.com/forum/?fromgr ... di6j8Xkykc
Top
 Profile  
 
BoardBoss
PostPosted: Mon Apr 30, 2012 12:32 pm 
User avatar
Joomla! Ace
Joomla! Ace

Joined: Sat Mar 18, 2006 10:41 am
Posts: 1143
Location: Hvolsvöllur, Iceland
@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!
Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  Page 1 of 1
  [ 30 posts ] 


Board index » Joomla! Older Version Support » Joomla! 1.5 » General Questions/New to Joomla! 1.5

Who is online

Users browsing this forum: Exabot [Bot] and 27 guests

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
Powered by phpBB® Forum Software © phpBB Group