Date Query Question 1

vetteguy69 · Mar 23, 2002

I have a MySQL database and was looking for a query to take in 2 form variables (beginning and end date) and based on those dates, return users from the database who's birthday(already in database column) is within that date range. How would I achieve this type of functionality?

JavaSprout · Mar 24, 2002

Your select will look something like

"SELECT * FROM mytable WHERE birthdate <= $startdate AND birthdate >= $enddate"

You might also find the following function useful:
/**
*
* Display a form to enable you to select a date
*
* e.g. To show a start_date and end_date form:
* date_selector("start_", $start_date);
* date_selector("end_", $end_date );
*
*/
function date_selector($inName, $useDate=0)
{
//create array so we can name months
$monthName = array(1=> "Jan", "Feb", "Mar",
"Apr", "May", "Jun", "Jul", "Aug",
"Sep", "Oct", "Nov", "Dec&quot

;

//if date invalid or not supplied, use current time
if($useDate == 0) {
$useDate = Time();
}

/*
** make month selector
*/
print("<select name=" . $inName . "month>\n&quot

;
for($currentMonth = 1; $currentMonth <= 12; $currentMonth++) {
print("<option value=\"&quot

;
print(intval($currentMonth));
print("\"&quot

;
if(intval(date( "m", $useDate))==$currentMonth) {
print(" selected&quot

;
}
print(">" . $monthName[$currentMonth] . "\n&quot

;
}
print("</select>&quot

;

/*
** make day selector
*/
print("<select name=" . $inName . "day>\n&quot

;
for($currentDay=1; $currentDay <= 31; $currentDay++) {
print("<option value=\"$currentDay\"&quot

;
if(intval(date( "d", $useDate))==$currentDay) {
print(" selected&quot

;
}
print(">$currentDay\n&quot

;
}
print("</select>&quot

;

/*
** make year selector
*/
print("<select name=" . $inName . "year>\n&quot

;
$startYear = date( "Y", $useDate);
for($currentYear = $startYear - 5; $currentYear <= $startYear; $currentYear++) {
print("<option value=\"$currentYear\"&quot

;
if(date( "Y", $useDate)==$currentYear) {
print(" selected&quot

;
}
print(">$currentYear\n&quot

;
}
print("</select>&quot

;
}

vetteguy69 · Apr 3, 2002

In trying to use the query you suggested I am running into some problems. When I submit my data from my form to the .php page with the query on it, it doesn't return any results. What data type should the dob field be in my database. I have it set as text now and I think it's wrong. Any suggestions?

--Here's the code I have so far--

-Form Page-
<html>
<head>
<title>Form Page</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body bgcolor="#FFFFFF" text="#000000">
<form name="form1" method="post" action="bdayfind.php">
<p>Start Date
<input type="text" name="startdate">
(mm/dd/yyyy)</p>
<p>End Date
<input type="text" name="enddate">
(mm/dd/yyyy)</p>
<p>
<input type="submit" name="Submit" value="Submit">
<input type="reset" name="Reset" value="Reset">
</p>
</form>
</body>
</html>

--bdayfind.php--
<html>

<head>

<title>Birthday Finder</title>

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link rel="stylesheet" type="text/css" href="../style.css">
</head>

<body bgcolor="#FFFFFF" text="#000000">
<center>

<?php

// Connecting, selecting database

$link = mysql_connect("albanycoors.com", "username", "password&quot

or die("Could not connect&quot

;

mysql_select_db("albanycoor&quot

or die("Could not select database&quot

;

// Performing SQL query

$query = "SELECT * FROM Membership WHERE dob <= $startdate AND dob >= $enddate";

$result = mysql_query($query)
or die("Query failed&quot

;

// Printing results in HTML
print "\t<center><table border=1 cellpadding=2 cellspacing=2>\n";
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {

print "\t<tr>\n";

foreach ($line as $col_value) {
print "\t\t<td class=textbold> $col_value </td>\n";
}
print "\t</tr>\n";
}

print "</table></center>\n";

// Free resultset
mysql_free_result($result);

// Closing connection
mysql_close($link);
?>

</center>
</body>
</html>

KarveR · Apr 3, 2002

$query = "SELECT * FROM Membership WHERE dob <= '$startdate' AND dob >= '$enddate'";

while ($line = mysql_fetch_array($result)) {

both should help

***************************************
Party on, dudes!

JavaSprout · Apr 3, 2002

Vetteguy69,

Firstly, your date field must be in the datatype of date! Don't even think about fudging it with another datatype, please

.

I have just looked in mysql.com and searched for 'date' and got the following info:

-------------------------------------------------------
The format of a DATE value is 'YYYY-MM-DD'. According to ANSI SQL, no other format is allowed. You should use this format in UPDATE expressions and in the WHERE clause of SELECT statements. For example:

mysql> SELECT * FROM tbl_name WHERE date >= '1997-05-05';
--------------------------------------------------------

So, all you need to do is build your date string in the format 'YYYY-MM-DD' and then you can do the comparisms easily.

Looking at your code, the first thing I would do is blend in the function which I proposed to you, i.e. instead of:

<form name="form1" method="post" action="bdayfind.php">
<p>Start Date
<input type="text" name="startdate">
(mm/dd/yyyy)</p>
<p>End Date
<input type="text" name="enddate">
(mm/dd/yyyy)</p> etc.

use something like:

<form name="form1" method="post" action="bdayfind.php">
<p>Start Date
<?
date_selector("start_&quot

;
?>
<p>End Date
<?
date_selector("end_&quot

;
?>

That will give you a very user friendly date selection.

Next you need to process all the bits in the receiving .php file. Basically you just need to join the bits together in the required order, a bit like as follows (in bdayfind.php):

$built_start_date = $start_year . "-" . $start_month . "-" . $start_day;
$built_end_date = $end_year . "-" . $end_month . "-" . $end_day;

Now you can do the SQL comparism as in the SQL documentation above.

Just stick plenty of 'print' statements everywhere to understand what is going on step by step, and I'm sure you'll soon have it licked.

Happy coding!

JavaSprout

vetteguy69 · Apr 3, 2002

ok got that working. For my next trick I'm trying to make a dropdown list with the months and based on the month the user selects it displays the people whose bday is in that month. HOw would I do this?

JavaSprout · Apr 3, 2002

The logic to me is:

1) Work out the first day of the month (easy: you know the month!)
2) Work out the first day of the next month (trickier: check php.net date/time functions, I'm sure there's either a function built in or someone's written one)
3) Do the same sort of query you now know how to do now using the start and end dates in (1) and (2) above (being careful of course about your <= or < etc.

Go get 'em Floyd.

KarveR · Apr 3, 2002

SELECT * FROM Membership WHERE dob like '____-$mymonth-__';

Months must be given as 01 02 03 .. 12 ***************************************
Party on, dudes!

KarveR · Apr 3, 2002

To clarify the above post, in mysql an underscore _ matches any single chracter, put 4 in for the year ____ and a - then the month value then - then you can either finish with % for any other characters or continue with underscores. ***************************************
Party on, dudes!

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Date Query Question 1

vetteguy69

Technical User

JavaSprout

Programmer

vetteguy69

Technical User

KarveR

MIS

JavaSprout

Programmer

vetteguy69

Technical User

JavaSprout

Programmer

KarveR

MIS

KarveR

MIS

Similar threads

Part and Inventory Search

Sponsor