Problem creating a where clause in MySQL

Hi,
      I have a piece of MySQL code which looks like:

SELECT
    u.id,
    u.user_registered,
    (select meta_value from bbct_usermeta where user_id = u.id and meta_key = '_mgm_cf_title' limit 1) as title,
    (select meta_value from bbct_usermeta where user_id = u.id and meta_key = 'first_name' limit 1) as FirstName,
    (select meta_value from bbct_usermeta where user_id = u.id and meta_key = 'last_name' limit 1) as LastName,
    (select meta_value from bbct_usermeta where user_id = u.id and meta_key = '_mgm_cf_email' limit 1) as Email,
    (select meta_value from bbct_usermeta where user_id = u.id and meta_key = '_mgm_cf_address_line_1' limit 1) as Address1,
    (select meta_value from bbct_usermeta where user_id = u.id and meta_key = '_mgm_cf_address_line_2' limit 1) as Address2,
    (select meta_value from bbct_usermeta where user_id = u.id and meta_key = '_mgm_cf_address_line_3' limit 1) as Address3,
    (select meta_value from bbct_usermeta where user_id = u.id and meta_key = '_mgm_cf_city' limit 1) as town,
    (select meta_value from bbct_usermeta where user_id = u.id and meta_key = '_mgm_cf_county' limit 1) as county,
    (select meta_value from bbct_usermeta where user_id = u.id and meta_key = '_mgm_cf_zip' limit 1) as postcode,
    (select meta_value from bbct_usermeta where user_id = u.id and meta_key = '_mgm_cf_phone' limit 1) as telephone,
    (select meta_value from bbct_usermeta where user_id = u.id and meta_key = '_mgm_cf_children_s_details' limit 1) as childrens_details,
    (select meta_value from bbct_usermeta where user_id = u.id and meta_key = '_mgm_cf_buzz_pdf' limit 1) as newsbuzz,
    (select meta_value from bbct_usermeta where user_id = u.id and meta_key = '_mgm_cf_bus_review' limit 1) as newsbom,
    (select meta_value from bbct_usermeta where user_id = u.id and meta_key = '_mgm_cf_monthly_e_newsletter' limit 1) as enewsletter,
    (select meta_value from bbct_usermeta where user_id = u.id and meta_key = '_mgm_cf_hear_about_us' limit 1) as hear_about_us,
    (select meta_value from bbct_usermeta where user_id = u.id and meta_key = '_mgm_cf_gift_aid' limit 1) as gift_aid,
    (select meta_value from bbct_usermeta where user_id = u.id and meta_key = '_mgm_cf_data_protection_options' limit 1) as data_protection,
    (select meta_value from bbct_usermeta where user_id = u.id and meta_key = 'mgm_member_options' limit 1) as member_options
    FROM bbct_users u
	WHERE user_registered BETWEEN DATE_SUB(CURDATE(),INTERVAL 30 DAY) AND CURDATE()

Open in new window


Within the member_options is a serialised value for last_pay_date which gets populated if somebody renews their membership.  What I need to do is rather than check the registered users for the last 30 days, is check how many records have had the last_pay_date changed in the last 7 days?

How can I achieve this?

Thanks

Damian
damianb123Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
NerdsOfTechTechnology ScientistCommented:
 WHERE `last_pay_date` BETWEEN DATE_SUB(CURDATE(),INTERVAL 7 DAY) AND CURDATE()

Open in new window

0
 
NerdsOfTechTechnology ScientistCommented:
 SELECT COUNT(*)
 FROM bbct_users u
 WHERE u.last_pay_date BETWEEN DATE_SUB(CURDATE(),INTERVAL 7 DAY) AND CURDATE()

Open in new window

0
 
damianb123Author Commented:
Hi NerdsOfTech,
     The trouble with this is, the field for last_pay_date is within a serialised field called Member_Options, so it's not quite as easy as you've outlined above :-(

Damian
0
 
NerdsOfTechTechnology ScientistCommented:
Now the question is, which epoch is the serialized date derived from?

Please provide an example serialized date here and its actual date counter-part (YYYY-MM-DD). We will determine if the epoch of the serialized date is Microsoft (Excel, etc.) or Unix (mySQL, etc) based; then we can convert it and plug it into a query.
0
All Courses

From novice to tech pro — start learning today.