Page 1 of 1

PHP BETWEEN not working as expected

Posted: Sun Nov 29, 2015 2:07 pm
by diseman
I have two records in db. One record is for $200 in the year 2015 and another record for $231 in the year 2016. However, the output is equal to $431 for both $referral_paid_this_year AND $referral_paid_last_year when it should be $200 for one and $231 for the other. Can anyone find the reason why this is happening?

Yes, I realize it's not 2016 yet, but this is just a learning experience for me; not real world application.

Code: Select all


// calculate total payments sent to affiliate for 2016

	$query = "SELECT referral_fee_paid, referral_pay_date FROM payments WHERE affiliate = '".$company_name."' && referral_pay_date BETWEEN '01-01-2016' AND '12-31-2016' ";

	$result = mysqli_query($con, $query) or die (mysqli_error($con));

		$referral_paid_this_year = 0;

		while ($row = mysqli_fetch_assoc ($result)) {

	    		$referral_paid_this_year += $row['referral_fee_paid'];
		}


// calculate total payments sent to affiliate for 2015

	$query = "SELECT referral_fee_paid, referral_pay_date FROM payments WHERE affiliate = '".$company_name."' && referral_pay_date BETWEEN '01-01-2015' AND '12-31-2015' ";

	$result = mysqli_query($con, $query) or die (mysqli_error($con));

		$referral_paid_last_year = 0;

		while ($row = mysqli_fetch_assoc ($result)) {

	    		$referral_paid_last_year += $row['referral_fee_paid'];
		}


Re: PHP BETWEEN not working as expected

Posted: Sun Nov 29, 2015 4:24 pm
by Celauran
MySQL's default date format is YYYY-MM-DD

Re: PHP BETWEEN not working as expected

Posted: Sun Nov 29, 2015 6:54 pm
by diseman
Thank you Celauran. That did it. Funny how no one even mentioned that in ALL the results I read on Google trying to figure this out.

So, that means standard practice is to alway put it in the db in that format and if I want to see it in mm-dd-yyyy, I have to change it on the displayed output (i.e. webpage), right?

Thank you and have a good night

Re: PHP BETWEEN not working as expected

Posted: Sun Nov 29, 2015 7:38 pm
by Celauran
diseman wrote:So, that means standard practice is to alway put it in the db in that format and if I want to see it in mm-dd-yyyy, I have to change it on the displayed output (i.e. webpage), right?
That's right

Re: PHP BETWEEN not working as expected

Posted: Sun Nov 29, 2015 10:23 pm
by Christopher
diseman wrote:So, that means standard practice is to alway put it in the db in that format and if I want to see it in mm-dd-yyyy, I have to change it on the displayed output (i.e. webpage), right?
Or use MySQL's DATE_FORMAT() function (https://dev.mysql.com/doc/refman/5.5/en ... ate-format).