PHP BETWEEN not working as expected

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
diseman
Forum Contributor
Posts: 174
Joined: Mon Jul 26, 2010 1:30 pm
Location: Florida

PHP BETWEEN not working as expected

Post 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'];
		}

User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: PHP BETWEEN not working as expected

Post by Celauran »

MySQL's default date format is YYYY-MM-DD
User avatar
diseman
Forum Contributor
Posts: 174
Joined: Mon Jul 26, 2010 1:30 pm
Location: Florida

Re: PHP BETWEEN not working as expected

Post 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
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: PHP BETWEEN not working as expected

Post 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
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: PHP BETWEEN not working as expected

Post 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).
(#10850)
Post Reply