Looking at the orders-table schema, the date_purchased field is defined as
Under what circumstances can that field be NULL?Code:date_purchased datetime default NULL
Looking at the orders-table schema, the date_purchased field is defined as
Under what circumstances can that field be NULL?Code:date_purchased datetime default NULL
It's set that way because there's no good default value. Look at all the trouble we have had with 0000-00-00 and then 0001-01-01.
That Software Guy. My Store: Zen Cart Support
Available for hire - See my ad in Services
Plugin Moderator, Documentation Curator, Chief Cook and Bottle-Washer.
Do you benefit from Zen Cart? Then please support the project.
That's not my question, my question is "Under what conditions can that field be NULL" (misspelled in the post's initial title).
From inspecting the order-class, that's "always" set to now(), but I believe I've found a site where the date on some orders is NULL.
Here's the technical answer:
Just like any field's "default value", it will have that "default value" whenever no value is explicitly set.
Therefore it will happen with any insert/update query on that table's records where the field is not part of the query (eg: not mentioned in the field-value pairs the query is inserting/updating).
It also includes records that were present before the field was added to the table.
And of course, if a query explicitly sets it to that value it will have that value.
As for this specific field in this specific table, you're right: it should always be set ... at least for data inserted since the table was created and that code added to the order class.
Is there some other code that creates/injects orders into this database? Updates happening to the data where the code might list the field but pass non-existing data that gets treated (correctly, albeit perhaps unexpectedly) as null?
.
Zen Cart - putting the dream of business ownership within reach of anyone!
Donate to: DrByte directly or to the Zen Cart team as a whole
Remember: Any code suggestions you see here are merely suggestions. You assume full responsibility for your use of any such suggestions, including any impact ANY alterations you make to your site may have on your PCI compliance.
Furthermore, any advice you see here about PCI matters is merely an opinion, and should not be relied upon as "official". Official PCI information should be obtained from the PCI Security Council directly or from one of their authorized Assessors.
FWIW, I'm investigating an issue reported on the SNAP Affiliates support-thread.
The SNAP-admin code currently determines the first-order-date for the given store via the following query:
If one or more of the store's orders has a NULL date_purchased, the query returns no records. If I change the query to:Code:$first_order_date = $db->Execute( "SELECT date_purchased FROM " . TABLE_ORDERS . " ORDER BY date_purchased ASC LIMIT 1" );
... then (so long as the store's recorded at least one order with a non-NULL purchase date) the query returns the expected value.Code:$first_order_date = $db->Execute( "SELECT date_purchased FROM " . TABLE_ORDERS . " WHERE date_purchased IS NOT NULL ORDER BY date_purchased ASC LIMIT 1" );
Yes, the context is helpful.
One thing to note is that MySQL sorts nulls "first" in search results (lower than the lowest non-null value).
So, yes, excluding null is necessary when sorting for lowest non-null value on a nullable field.
.
Zen Cart - putting the dream of business ownership within reach of anyone!
Donate to: DrByte directly or to the Zen Cart team as a whole
Remember: Any code suggestions you see here are merely suggestions. You assume full responsibility for your use of any such suggestions, including any impact ANY alterations you make to your site may have on your PCI compliance.
Furthermore, any advice you see here about PCI matters is merely an opinion, and should not be relied upon as "official". Official PCI information should be obtained from the PCI Security Council directly or from one of their authorized Assessors.
Doing so will also "remove" any order that was singly made by a customer and logged as null. I found this on stackoverflow which allows sorting to have the null result(s) returned and to place them at the beginning or end based on the sort order provided: https://stackoverflow.com/questions/...ues-at-the-end
Which would mean the WHERE date_purchased IS NULL portion could be removed.Code:ORDER BY date_purchased IS NULL, date_purchased
ZC Installation/Maintenance Support <- Site
Contribution for contributions welcome...
In either use, readability is not really the issue. For the desired result (no date, no payment) the where clause is correct as it eliminates all "no date" items. In the situation of ignoring "no date" items unless that is the only item(s), the (yes) somewhat confusing order by method would at least offer the existence of such a record.
Noting also that I hadn't seen the subsequent "reasons" for the question before posting. :)
ZC Installation/Maintenance Support <- Site
Contribution for contributions welcome...
Bookmarks