Page 1 of 1

comparing date against database date

Posted: Sun Aug 29, 2010 12:48 pm
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");

Re: comparing date against database date

Posted: Sun Aug 29, 2010 2:10 pm
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

Re: comparing date against database date

Posted: Sun Aug 29, 2010 6:27 pm
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.

Re: comparing date against database date

Posted: Sun Aug 29, 2010 6:51 pm
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....

Re: comparing date against database date

Posted: Sun Aug 29, 2010 7:07 pm
by mywwd
i ended up figuring it out, but thanks

Bobby

Re: comparing date against database date

Posted: Mon Aug 30, 2010 12:46 am
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.