Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2009
    Location
    Stuart, FL
    Posts
    9,818
    Plugin Contributions
    73

    Default Under what conditions can orders::date_purchased be NULL?

    Looking at the orders-table schema, the date_purchased field is defined as

    Code:
    date_purchased datetime default NULL
    Under what circumstances can that field be NULL?

  2. #2
    Join Date
    Feb 2006
    Location
    Tampa Bay, Florida
    Posts
    7,468
    Plugin Contributions
    101

    Default Re: Under what conditions can orders::date_purchased by 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 Modifications
    Available for hire - See my ad in Services
    Plugin Moderator, Documentation Curator, Chief Cook and Bottle-Washer.

  3. #3
    Join Date
    Sep 2009
    Location
    Stuart, FL
    Posts
    9,818
    Plugin Contributions
    73

    Default Re: Under what conditions can orders::date_purchased by NULL?

    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.

  4. #4
    Join Date
    Jan 2004
    Posts
    65,660
    Blog Entries
    7
    Plugin Contributions
    240

    Default Re: Under what conditions can orders::date_purchased by 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.

  5. #5
    Join Date
    Sep 2009
    Location
    Stuart, FL
    Posts
    9,818
    Plugin Contributions
    73

    Default Re: Under what conditions can orders::date_purchased by NULL?

    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:
    Code:
    $first_order_date = $db->Execute(
    "SELECT date_purchased
    FROM " . TABLE_ORDERS . "
    ORDER BY date_purchased ASC
    LIMIT 1"
    );
    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 . "
    WHERE date_purchased IS NOT NULL
    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.

  6. #6
    Join Date
    Jan 2004
    Posts
    65,660
    Blog Entries
    7
    Plugin Contributions
    240

    Default Re: Under what conditions can orders::date_purchased by NULL?

    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.

  7. #7
    Join Date
    Jul 2012
    Posts
    15,321
    Plugin Contributions
    17

    Default Re: Under what conditions can orders::date_purchased by NULL?

    Quote Originally Posted by lat9 View Post
    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:
    Code:
    $first_order_date = $db->Execute(
    "SELECT date_purchased
    FROM " . TABLE_ORDERS . "
    ORDER BY date_purchased ASC
    LIMIT 1"
    );
    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 . "
    WHERE date_purchased IS NOT NULL
    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.
    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

    Code:
    ORDER BY
        date_purchased IS NULL,
        date_purchased
    Which would mean the WHERE date_purchased IS NULL portion could be removed.
    ZC Installation/Maintenance Support <- Site
    Contribution for contributions welcome...
    Upgraded to Zen Cart V1.5.3 from V1.5.1 from V1.5.0 from V1.3.9h

  8. #8
    Join Date
    Sep 2009
    Location
    Stuart, FL
    Posts
    9,818
    Plugin Contributions
    73

    Default Re: Under what conditions can orders::date_purchased by NULL?

    Quote Originally Posted by DrByte View Post
    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.
    Thanks, DrByte, for the confirmation.

    Quote Originally Posted by mc12345678 View Post
    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

    Code:
    ORDER BY
        date_purchased IS NULL,
        date_purchased
    Which would mean the WHERE date_purchased IS NULL portion could be removed.
    That's the intent, @mc12345678, no purchase-date, no SNAP affiliate payment. I prefer the use of the WHERE clause as it's (IMO) a bit more readable.

  9. #9
    Join Date
    Jul 2012
    Posts
    15,321
    Plugin Contributions
    17

    Default Re: Under what conditions can orders::date_purchased by NULL?

    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...
    Upgraded to Zen Cart V1.5.3 from V1.5.1 from V1.5.0 from V1.3.9h

 

 

Similar Threads

  1. v155 Making product free shipping under certain conditions.
    By necroside in forum General Questions
    Replies: 1
    Last Post: 16 Mar 2017, 11:50 AM
  2. Where can I find a guide that explains what each attribute under Add Product does?
    By bparker in forum Templates, Stylesheets, Page Layout
    Replies: 7
    Last Post: 9 Mar 2010, 08:14 PM
  3. Prevent Redirect After Add To Cart, under certain conditions
    By andrew55 in forum Templates, Stylesheets, Page Layout
    Replies: 14
    Last Post: 26 Sep 2009, 08:50 PM
  4. Free Shipping On Orders Under 50, But On Orders Under 30 Locally?
    By steve_ringuk in forum Built-in Shipping and Payment Modules
    Replies: 1
    Last Post: 11 Aug 2008, 05:01 PM
  5. free shipping under conditions
    By carlwenrich in forum Built-in Shipping and Payment Modules
    Replies: 1
    Last Post: 14 Jun 2008, 06:31 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
disjunctive-egg
Zen-Cart, Internet Selling Services, Klamath Falls, OR