mysql_query question.

[cleverpork]

Hey, all. 

I have a question that is perplexing to me, but possible simple to others...

Here’s the scenario (the code in question will follow).

I have a module that was built for me that is subscription based in nature. One function allows the customer to purchase a set number of download credits for a year, but releases them every 30 days. (example: customer x purchases 36 download credits for $99. They get released 3 download credits per 30 days).

That said, here’s the code I need help with (question will follow):

if(in_array(1,$fields)){
            if($schedule_period ==1)
                $nextdate = mktime(0, 0, 0, date("m")  , date("d")+30, date("Y"));
            if($schedule_period ==12)
                $nextdate = mktime(0, 0, 0, date("m")  , date("d")+360, date("Y"));
        $next_schedule =date("d-m-Y",$nextdate);
        $next = mktime(0, 0, 0, date("m")  , date("d")+$nextdate, date("Y"));
        $updateon = date("d-m-Y",$nextdate);
        mysql_query("UPDATE " . TABLE_CUSTOMERS . " SET pending_downloads = $products_downloads ,update_left=$schedule_period,update_on='$updateon',allow_downloads = $products_downloads WHERE customers_id ='".$_SESSION[customer_id]."'");

Here’s where I need the help:
Logically speaking I need this:
if($schedule_period ==1) then the “UPDATE” should print $updateon for “update_on”.
if($schedule_period ==12) then the “UPDATE” should print todays date +30 for “update_on.”

Does that make sense what I’m asking? I realize I’m not the best in making this make sense, but hopefully that’s enough detail to help get things going. 

Feel free to ask any questions. I’ll do my best to talk through this thing.

Thanks!

Cris

[DrByte]

What's the code producing "now"?

[cleverpork]

What's the code producing "now"?

It will produce the initial order, provide initial download credits and recurr bill in 360 days for the "if($schedule_period==12) code. And it will produce the order, provide credits, and recurr bill in 30 days for the if($schedule_period==1) code.

The customer can go to a product page and see they have download points and then use them at that time.

Following that, the monthly subscription (schedule_period==1) will recur bill and provide more download credits.

Also, that above code places the recurr bill date for both schdules into the "update_on" column, which is really the issue.

For the annual purchase (schedule_period==12) it needs to update the downloads every 30 days. As is, it's updating the downloads every 360 days. For reference there is another column that grabs the recurr bill date, so tampering with the "update_column" will not alter the other functions that are working.

That said, if I'm understanding everything correctly, it seems I simply need to generate an "if / then" type statement for the "UPDATE" code..

I hope that helps understand. I find it difficult to generate a sentence that accurately describes this mod...

Anway, let me know if that makes sense....

[DrByte]

I understand the overall picture of what you're trying to accomplish, since you've described it in previous threads.

What I'm unclear about is what the code you pasted is doing "now" vs what you "want" it to do.

Your statement about "for the annual purchase it needs to update the downloads every 30 days" is puzzling to me. You say it's updating every 360 days now, but isn't that how "annual" *should* work? I must be missing something about what *else* is happening behind-the-scenes to understand what your goal is here.

[cleverpork]

... What I'm unclear about is what the code you pasted is doing "now" vs what you "want" it to do.

...Your statement about "for the annual purchase it needs to update the downloads every 30 days" is puzzling to me.

Firstly, thanks for trying to understand.
I'll try and simplify...

If customer x purchases the annual subscription, they get 36 credits that are released 3 at a time, every 30 days. That's the hiccup.

The "update_on" column pertains to when the credits are released. As is, it will release 3 credits every 360 days.

Hope that helps.

Again, thanks!

[DrByte]

Okay, so if the ONLY thing you need to change is the "update_on" parameter, but keep ALL the rest the same, then the following might work:
(I'm making a lot of assumptions, but trying to isolate this to just the bit of code you quoted, hoping it's enough of the picture)

if(in_array(1,$fields)){
        $nextdate = mktime(0, 0, 0, date("m")  , date("d")+30, date("Y"));
        $updateon = date("d-m-Y",$nextdate);
        mysql_query("UPDATE " . TABLE_CUSTOMERS . " SET pending_downloads = $products_downloads ,update_left=$schedule_period,update_on='$updateon',allow_downloads = $products_downloads WHERE customers_id ='".$_SESSION[customer_id]."'");

Now, the lines I removed might be needed for stuff that's happening AFTER the query is done. In that case, just repeat all those other lines (except the query) after the query.

[cleverpork]

Okay, so if the ONLY thing you need to change is the "update_on" parameter, but keep ALL the rest the same, then the following might work:
(I'm making a lot of assumptions, but trying to isolate this to just the bit of code you quoted, hoping it's enough of the picture).

Nope. This didn't work because it wasn't all of the picture, but....



Your code prompted an aha moment that helped me figure a new solution. I know it's obvious that 30 days are the common item between the monthly subscription and the update of credits for the annual subscription, but I couldn't see it for some reason.



Anyway, needing to keep the "schedule_period" bits of code for other reasons, I decided to create this new line after the schedule_period==12

$nextdateupdate = mktime(0, 0, 0, date("m")  , date("d")+30, date("Y")); 
$updateon2 = date("d-m-Y",$nextdateupdate);

And then changed the mysql_query to grab $updateon2 for "update_on". This way the other stuff isn't touched and the update_on column is great.

Again, I'm new at all this and this seems to have solved my code issues.

Thanks Dr Byte!

[DrByte]

Glad you got it sorted out!