comparing date against database date

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
mywwd
Forum Newbie
Posts: 6
Joined: Mon Aug 02, 2010 12:20 am

comparing date against database date

Post by mywwd »

so i am try to show users with a date of birth greater or equal to the one select and i cant seem to get the script to work right.

<?php

if ($_GET['gender'] == 'Female') {
$where = "sex='Female'";
}
elseif ($_GET['gender'] == 'Male') {
$where = "sex='Male'";
}
elseif ($_GET['gender'] == 'Both') {
$where = "status='active'";


}
$minAge = $_GET['minAge'];
$minimum = date("m/d/Y", strtotime('-$minAge years'));
$min = " AND date_of_birth =>" . $minimum;
$maxAge = $_GET['maxAge'];
$maximum = date("m/d/Y", strtotime('-$maxAge years'));
if (isset($_GET['single']) == 1) {
$where1 = " AND relationship_status='Single'";
}
else
$where1 = '';

if ($_GET['married'] == 1) {
$where2 = ' AND relationship_status="Married"';
}
else
$where2 = '';




$result = mysql_query("SELECT * FROM users WHERE " . $where . $min . $where2 . " ORDER BY signup_date DESC");
User avatar
phpcip28
Forum Newbie
Posts: 22
Joined: Sun Aug 29, 2010 1:38 pm
Location: NewYork
Contact:

Re: comparing date against database date

Post by phpcip28 »

From what I can see, the error is in the fact that you're trying to compare against something of the form: month/day/year which is incorrect.
You need to transform the timestamp you generated with strtotime to something of the MYSQL DATETIME Format which is like this:

Code: Select all

YYYY-MM-DD HH:MM:SS
mywwd
Forum Newbie
Posts: 6
Joined: Mon Aug 02, 2010 12:20 am

Re: comparing date against database date

Post by mywwd »

it doesnt seem to be the time stamp since i store it in the database as m/d/Y. The problem seems to be within the comparing.
User avatar
phpcip28
Forum Newbie
Posts: 22
Joined: Sun Aug 29, 2010 1:38 pm
Location: NewYork
Contact:

Re: comparing date against database date

Post by phpcip28 »

Well... So than of what type is your y/m/d table field ? VARCHAR ? TEXT ? AND you expect to be able to compare a VARCHAR field against another VARCHAR field using arithmetic operands ?!

In translation, what you're doing is like saying:

something >= something_else

where something is your database STRING (TEXT, VARCHAR) date, and something else is your STRING you computed there with the date() function.

I think you should look for the DATETIME field type in MySQL.
Here's a good start:
http://dev.mysql.com/doc/refman/5.1/en/datetime.html

And also read about comparing two strings as opposed to comparing dates or integers or floats, etc in PHP/MySQL...
Also read about programming languages datatypes in general... not only PHP... Even datatypes in query languages such as mysql...
I can't really say anything more, since it would be completely redundant on my side...

Wish you best of luck ! ;)

Oh.. and you might also get a number of mysql errors in there... if you're using the exact same code you've presented here....
mywwd
Forum Newbie
Posts: 6
Joined: Mon Aug 02, 2010 12:20 am

Re: comparing date against database date

Post by mywwd »

i ended up figuring it out, but thanks

Bobby
User avatar
phpcip28
Forum Newbie
Posts: 22
Joined: Sun Aug 29, 2010 1:38 pm
Location: NewYork
Contact:

Re: comparing date against database date

Post by phpcip28 »

Well no problems. I love to help out.

Just off topic, you might wanna try the nice CodeIgniter MVC. It'll get a lot of payload off your back.
Post Reply