Help with if less statement

if statement

[zorro]

I am trying to set up some simple stats counters that increment the number by 1 each time a users details page is visited but I want to insert serpate increments depending on a value.

I have got most of them working fine for example each time the users page is visited the table column 'page_visit' is updated by +1:

$sql="UPDATE ".$TABLES["stats"]." SET page_visit=page_visit+1 WHERE id='".$_REQUEST["id"]."'";

The above works fine for just general page visits.

What I would also like to do is the following:
In the same table I also have a column called 'visits_since_payment'
I want to increment this column by +1 each time the page is visited but only if a certain value is true.

In laymens terms:
If 'payment date' is less than 12 months before now and 'payment amount'=10, update table stats, column 'visits_since_payment'+1 WHERE id = request id.
If 'payment date' is more than 12 months ago do nothing.

Problem 1. I'm not sure what the proper code is to use to check for all dates 12 months before todays date?

Problem 2. What happens when user pays again next year round?

For example: User makes payment of 10, on 1st Jan 2010. So up until 1st Jan 2011 increments will be put into the 'visits_since_payment' column

Lets say they pay again the next year round - payment of 10, on 1st Jan 2011.
Would the if statements above still work as there would be now 2 entries in the database for the same id.

id=? payment date - 01/01/2010 amount 10
id=? payment date - 01/01/2011 amount 10

Would the 2nd if statement above "If 'payment date' is more than 12 months ago do nothing" be conflicting as there is both a payment data within the (less than 12 months) date and also a payment date (older than 12 month).

Would it be better to clear the table column 'visits_since_payment' when the same id makes the same payment again and start over again.

I'm not sure where to go with this!
Hope I have explained it ok?

[Readie]

Right, problem number 2 first:

$sql = 'SELECT * FROM table_name WHERE user_id = "' . $user_id . '"

ORDER BY payment_date DESC LIMIT 1

';
$result = mysql_query($sql);
$res = mysql_fetch_assoc($result);

So we're only looking at the most recent payment.

Problem 1:

Problem 1. I'm not sure what the proper code is to use to check for all dates 12 months before todays date?

$twelve_months_prev = mktime(date('H'), date('i'), date('s'), date('n'), date('j'), (date('Y') - 1));
if(strtotime($res['payment_date']) > $twelve_months_prev) {
 // Update column
}

Probably better ways to do that, but meh, I'm tired.

Anyways, I compare the UNIX timestamp of 1 year ago, and the UNIX timestamp of what you have saved in the database (I love strtotime() :)), if the latter is greater than the former, update the column.

[Matthew1980]

Hi there Zorro,
[EDIT]: Readie is officially the faster typist! And gave better tips, High Five Chap!

something like:-

if(($PayMentDate <= date("y")) && ($PayMentAmount == 10)){
//do update
}
else{
//do nothing as conditions aren't met - you needn't both using the else really
}

Just means that the date/time format from the database will need to match the format given from the date() function.

Anyway that's what I understand from your explanation.

>>Problem 1. I'm not sure what the proper code is to use to check for all dates 12 months before todays date?

That would just mean using the strtotime("-1 year") inside the date function: date("Y-m-d", strtotime("-1 year"));

You can see where I am heading from that anyway.

Lastly:

Please try not to use $_REQUEST['id'], if this is coming from a form or url, use $_POST or $_GET respectively, as there are security issues with using $_REQUEST - though having stated that, using it for development use should be ok, just not in live (public) instances, as you reveal a lot of information using $_REQUEST[]

Hope that helps :)

Cheers,
MRb

[zorro]

Thanks Readie and Matthew1980 that's brilliant!

By the way Matthew the 'request id' is not used in any forms or urls, it is used simply to determine which user is which, for example:

select from database where user id = request id etc
I presume this is ok?

Thanks again, very much!

[Matthew1980]

Hi there Zorro,

I was just trying raise the awareness of the security issues that surround the use of $_REQUEST within the scope of a 'public domain script'. Google "$_REQUEST security issues" to see what I mean.

You say it is not used in any form's or url's, how is it being set & got then?

Cheers,
MRb

[zorro]

It is only used after a user logs into their account with their username and password to dynamically return certain data on the page that is relevant to their account, if that make any sense?
I am not coder by a long shot. We had the website built by some Bulgarian php programmers years ago and I have been picking at bits of the code for the last two years and creating extra features and learning hell of lot on the way, before that I was strictly HTML but now i've got the php bug and can't seem to shake it off!

I tried several versions of yours Matthew but couldn't get it to work. I did get Readie's version to work though so am well happy.

Here is what I tried with your version:

$sql="SELECT * FROM ".$TABLES["payments"]." WHERE id='".$_REQUEST["id"]."' ORDER BY date DESC LIMIT 1";
$sql_result = mysql_query ($sql, $connection ) or die ('request "Could not execute SQL query" '.$sql);
$PAYMENTS=mysql_fetch_assoc($sql_result);
------------------------------------------
if ($PAYMENTS["date"] <= date("Y-m-d", strtotime("-1 year")) && ($PAYMENTS['amount']==80))
//do update
}
-----------------------------------------
also tried these in the date function

date("Y-m-d", strtotime("-1 year")) etc
date("YYYY-mm-dd", strtotime("-1 year")) etc
date("Y", strtotime("-1 year")) etc
date("YYYY", strtotime("-1 year")) etc
date("%Y-%m-%d %H:%i:%s", strtotime("-1 year")) etc
date("Y-m-d H:i:s", strtotime("-1 year")) etc

the format in the database is like this 2010-07-26 00:00:00

Not sure why it didn't work!
Thanks again for all your help!
I think you should have a donate button near your username so people can donate the odd pound/buck for helping them?
I would have been glad to donate the odd pound or so as I know this can save loads of time. The last time I was tring to figure something out I spent 2 full day on it searching google etc.

[zorro]

Hi Readie and Matthew,

Sorry the above should have read $PAYMENTS['amount']==10 this was a typo.

I've just thought of another possible problem though!

In the database there is the column 'visits_since_payment' which gets incremented by +1 if the;
a. Payment date is less than 12 months old
b. Payment equals 10
SO FAR SO GOOD.

Lets assume a user makes a payment on 1st Jan 2009 of 10
and between the dates 01/01/2009 and 01/01/2010 the page gets visited 22 times, this therefore places the figure '22' in the database column:'visits_since_payment'

If they don't pay again the next year round nothing gets added to the: 'visits_since_payment' column as the last payment is older than 1 year.

Now assuming they pay again on 01/01/2010 (or before/after give or take a few days).
Increments of +1 will be added to the column 'visits_since_payment'.
The only problem is that this column will already contain the figure '22' so the next figure will be '23'.

Maybe I didn't explain it properly but once a user make another payment again for the same amount 10 (as this fee is only due annually) I want the figures in 'visits_since_payment' column to start again from 0.

To make thing a little clearer we guarantee the user, so many page visits per year from date of payment, if the figure is not reach they are refunded, if the figure is reached things continue as normal until next payment.

Any suggestion greatly appreciated.

[Matthew1980]

Hi there Zorro,

>>donate button near your username so people can donate

Well that's a new one! But then again the saying goes, "If I had a dollar for every time...". To be honest, it's just nice occasionally to get a bit of feedback to know as you have helped someone a little, over the years this forum has helped me lots, so in essence I am returning the favour ;)

Ok then. With regards to the date format, that's easy to sort out: [uk3.php.net ] Read up on the format settings in that link - very useful resource, I have this printed out and on my desk :)

>>the format in the database is like this 2010-07-26 00:00:00

To get that:-

date("Y m d H:i:s", strtotime("-1 year"));

That would produce the time relevant to the CPU/comptuer your server is running on -1 year, and will output that to the format your after.

Hopefully that explains that a little clearer.

Cheers,
MRb

[zorro]

Oh I see! It was the hyphens between the Y M and D, damn, I nearly had it with the last example above.
I have bookmarked the uk3 site cheers, just researching now on how to reset the table data to zero for the 2nd payment.

Thanks again for all your help.

[zorro]

Hi Readie, Matthew1980.

Just thought i'd let you know that I solved it!
Rather than looking at resetting the table:'stats' column:'visits_since_payment' to zero each time a new payment is made, it was easier to include the column: 'visits_since_payment' into the 'payments' table (rather than stats table).
As the payment table has a record for every payment, from every user, it was easier to create another 'visits_since_payment' column on the end of this table and insert or rather UPDATE this column by +1 based on a WHERE clause.

THE CODE EVENTUALLY LOOKING LIKE THIS...

$sql="SELECT * FROM ".$TABLES["payments"]." WHERE property_id='".$_REQUEST["id"]."' ORDER BY date DESC LIMIT 1";
$sql_result = mysql_query ($sql, $connection ) or die ('request "Could not execute SQL query" '.$sql);
$PAYMENTS=mysql_fetch_assoc($sql_result);

$twelve_months_prev = mktime(date('H'), date('i'), date('s'), date('n'), date('j'), (date('Y') - 1));
if(strtotime($PAYMENTS['date']) > $twelve_months_prev AND ($PAYMENTS['amount']==10)){

$sql="UPDATE ".$TABLES["payments"]." SET visits_since_payment=visits_since_payment+1 WHERE property_id='".$_REQUEST["id"]."'
AND date > $twelve_months_prev ORDER BY date DESC LIMIT 1";
$sql_result = mysql_query ($sql, $connection ) or die ('request "Could not execute SQL query" '.$sql);
}

THIS THEN ONLY INCREMENTS BY +1 THE 'VISITS SINCE PAYMENT' FOR THE 'LATEST PAYMENT' BY THIS USER

THANKS AGAIN BOTH OF YOU FOR YOUR HELP

[zorro]

IF YOU STARE AT CODE LONG ENOUGH IT COMES TO YOU!

[Readie]

IF YOU STARE AT CODE LONG ENOUGH IT COMES TO YOU!

Often I find it helps just to walk away, get a drink, do something to relax for a few minutes: even if you're not activley thinking about it, your subconscience is still working on it.

[zorro]

Hi Readie,
Not sure if this is a php or sql subject...
I thought I had solved the coding part as pointed out above and it seems to work fine apart from one little glitch.
I've tried altering the structure of the sql table row e.g Type, NULL, Not Null, As defined, None etc etc etc

What's happening is on the first page visit, the 'visits_since_payment' column is inserting +2 NOT +1
After this it inserts +1 and everything ok after this.

[zorro]

Scrub the above post it is inserting 2 all the time but inserts only one on a page refresh ?

[zorro]

SOLVED: Moved code further down the page as the process was running twice once on page open and again when user click button to visit page.

[Matthew1980]

Hi all,

>>Often I find it helps just to walk away, get a drink, do something to relax for a few minutes:

Well we all have our "methods" of distraction. I enjoy my drive home personally :)

Glad your all working now though.

Have fun with the rest of your project.

Cheers,
MRb