Search Members By Birthday

[tomyknoker]

Hi wondering if anyone can help me, I have a list of members in a MySQL db. I want to have an initial page which just has a drop down menu, which lists all the months of the year. From this say I select February in the drop down menu I want it to list all the members with birthdays in February... I only need it to display the members FirstName, LastName, State and DateOfBirth in the outputted table... Any help would be greatly appreciated!

[thetestingsite]

You could try something like this in your sql query (after the form has been submitted.



$sql = "SELECT * FROM `table` WHERE `birthday` LIKE '%$bday%'";

$result = mysql_query($sql);


$bday is the variable assigned by the form choice that has been submitted. Change the parts in red above to work on your db.

Hope this helps.

[tomyknoker]

Yea I think that would be good... so something like this? Is it good to have that else statement under all of it?
<?php

/* connect to the mysql database and use a query to get the members info */

include 'library/config.php';
include 'library/opendb.php';
//navigation
include("nav.php");

//birthday search
$sql = "SELECT * FROM `table` WHERE `birthday` LIKE '&#37;$bday%'";

$result = mysql_query($sql);

echo '<table border="1" cellpadding="3" cellspacing="1">
<tr valign="top">
<td>First Name</td>
<td>Last Name</td>
<td>DateOfBirth</td>
<td>Last Login</td>
</tr>';

if (mysql_num_rows($info) < 1) {
echo '<tr valign="top">
<td colspan="4">There are no members that match the query. Please go back and try again</td>
</tr>';
}

else {
while ($qry = mysql_fetch_array($info)) {

//create the layout
?>
<link href="cs_style.css" rel="stylesheet" type="text/css" />

<tr valign="top">
<td><?php echo $qry['FirstName']; ?></td>
<td><?php echo $qry['LastName']; ?></td>
<td><?php echo $qry['DateOfBirth']; ?></td>
<td><?php echo $qry['State']; ?></td>
</tr>
<?php
}
}

echo '</table>';
?>

[thetestingsite]

That would work if you had another page display the form and submit to the page that the code you posted above is in. So it would look something like this:



<form action="search.php" method="POST">
Date: <input type="text" name="bday" value="March" readonly>
<input type="submit" value="Do Search">
</form>


Then search.php would be like this (modified version of your above posted code):



<?php

/* connect to the mysql database and use a query to get the members info */

include 'library/config.php';
include 'library/opendb.php';
//navigation
include("nav.php");

$bday = $_POST['bday']; //assign the bday variable.

//birthday search
$sql = "SELECT * FROM `table` WHERE `birthday` LIKE '&#37;$bday%'";

$info = mysql_query($sql);

echo '<table border="1" cellpadding="3" cellspacing="1">
<tr valign="top">
<td>First Name</td>
<td>Last Name</td>
<td>DateOfBirth</td>
<td>Last Login</td>
</tr>';

if (mysql_num_rows($info) < 1) {
echo '<tr valign="top">
<td colspan="4">There are no members that match the query. Please go back and try again</td>
</tr>';
}

else {
while ($qry = mysql_fetch_array($info)) {

//create the layout
?>
<link href="cs_style.css" rel="stylesheet" type="text/css" />

<tr valign="top">
<td><?php echo $qry['FirstName']; ?></td>
<td><?php echo $qry['LastName']; ?></td>
<td><?php echo $qry['DateOfBirth']; ?></td>
<td><?php echo $qry['State']; ?></td>
</tr>
<?php
}
}

echo '</table>';
?>


Hope this helps.

[tomyknoker]

Ok I received this error
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in e:\inetpub\esvc000208\admin\birthdaysearch.php on line 25

[thetestingsite]

Sorry about that, view my edit in the above code (search.php) in blue. Or in other words, change



$result = mysql_query


to



$info = mysql_query


Hope this helps.

[tomyknoker]

Hmmm I still get the same error output...

[thetestingsite]

Ok, did you change the details in this section?



$sql = "SELECT * FROM `table` WHERE `birthday` LIKE '&#37;$bday%'"


You need to change "table" to your database table name and "birthday" to what field you want the search to be executed in.

[tomyknoker]

I have a feeling I have done this wrong
<html>
<head>
<title>Search Birthdays</title>
</head>
<body>

<?php
//navigation
include("nav.php");
?>

<form action="birthdaysearch.php" method="POST">
<label>
<select name="bday">
<option>Select A Month</option>
<option value="January">January</option>
<option value="February">February</option>
<option value="March">March</option>
<option value="April">April</option>
<option value="May">May</option>
<option value="June">June</option>
<option value="July">July</option>
<option value="August">August</option>
<option value="September">September</option>
<option value="October">October</option>
<option value="November">November</option>
<option value="December">December</option>
</select>
</label>
<input type="submit" value="Do Search">
</form>
</body>
</html>

[thetestingsite]

How is the "birthday" field in your database formed? In other words, is it plain text (January 15, 1992), timestamp (1123411127), or something else?

This is important to know before rewritting the code for it to work.

[tomyknoker]

The type is 'date' and default it '0000-00-00', does that help?

[thetestingsite]

Then you would want something like this (for the form that is):



<form action="search.php" method="POST">
Brithday Month: <select name="month">
<option>Select A Month</option>
<option value="01">January</option>
<option value="02">February</option>
<option value="03">March</option>
<option value="04">April</option>
<option value="05">May</option>
<option value="06">June</option>
<option value="07">July</option>
<option value="08">August</option>
<option value="09">September</option>
<option value="10">October</option>
<option value="11">November</option>
<option value="12">December</option>
</select>
<input type="submit" value="Search">
</form>


and this for search.php:



<?php
$month = $_REQUEST['month'];

$info = mysql_query("SELECT * FROM `table` WHERE `birthday` LIKE '&#37;$month%'");

/* Rest of php code here */


Hope this helps.

[tomyknoker]

Hmmm it's spitting out an error... I think I might have put the code wrong somewhere...
<?php

/* connect to the mysql database and use a query to get the members info */

include 'library/config.php';
include 'library/opendb.php';
//navigation

include("nav.php");

//assign the bday variable.
$month = $_REQUEST['month'];

//birthday search
$info = mysql_query("SELECT * FROM `tblmembers` WHERE `DateOfBirth` LIKE '&#37;$month%'");

$info = mysql_query($sql);

echo '<table border="1" cellpadding="3" cellspacing="1">
<tr valign="top">
<td>First Name</td>
<td>Last Name</td>
<td>DateOfBirth</td>
<td>Last Login</td>
</tr>';

if (mysql_num_rows($info) < 1) {
echo '<tr valign="top">
<td colspan="4">There are no members that match the query. Please go back and try again</td>
</tr>';
}

else {
while ($qry = mysql_fetch_array($info)) {

//create the layout
?>
<link href="cs_style.css" rel="stylesheet" type="text/css" />

<tr valign="top">
<td><?php echo $qry['FirstName']; ?></td>
<td><?php echo $qry['LastName']; ?></td>
<td><?php echo $qry['DateOfBirth']; ?></td>
<td><?php echo $qry['State']; ?></td>
</tr>
<?php
}
}

echo '</table>';
?>

[thetestingsite]

What's the error, that way we could pinpoint it in the code.

EDIT: Nevermind, in your code above, you have 2 mysql_query's. Get rid of the second one (or the one that looks like this):



$info = mysql_query($sql);


Hope this helps.

[tomyknoker]

I'm slow sorry... Ok all worked! But I lost my date/time format... Any ideas?


echo $qry['loginDateTime'];
echo($qry['loginDateTime']?date('d/m/Y H:i:s', strtotime($qry['loginDateTime'])):'unknown')

[thetestingsite]

To get your date/time format back, you could probably use the following:



echo date('d/m/Y H:i:s', strtotime($qry['DateOfBirth']));


in place of the part in red below:



<?php

/* connect to the mysql database and use a query to get the members info */

include 'library/config.php';
include 'library/opendb.php';
//navigation

include("nav.php");

//assign the bday variable.
$month = $_REQUEST['month'];

//birthday search
$info = mysql_query("SELECT * FROM `tblmembers` WHERE `DateOfBirth` LIKE '&#37;$month%'");

$info = mysql_query($sql);

echo '<table border="1" cellpadding="3" cellspacing="1">
<tr valign="top">
<td>First Name</td>
<td>Last Name</td>
<td>DateOfBirth</td>
<td>Last Login</td>
</tr>';

if (mysql_num_rows($info) < 1) {
echo '<tr valign="top">
<td colspan="4">There are no members that match the query. Please go back and try again</td>
</tr>';
}

else {
while ($qry = mysql_fetch_array($info)) {

//create the layout
?>
<link href="cs_style.css" rel="stylesheet" type="text/css" />

<tr valign="top">
<td><?php echo $qry['FirstName']; ?></td>
<td><?php echo $qry['LastName']; ?></td>
<td><?php echo $qry['DateOfBirth']; ?></td>
<td><?php echo $qry['State']; ?></td>
</tr>
<?php
}
}

echo '</table>';
?>


Hope this helps.

[tomyknoker]

I don't know what I am doing today! My posts aren't posting properly or something... What it does is sort them, but it adds both the month and the day as the result. So say I search February, it adds Member 1, 12/03/1980 and also Member 2, 27/12/1973... See what it's doing any idea why?

[tomyknoker]

I get an error message on people that were born beofre 1970... That's wierd, surely you can display birthdays prior to that?



Warning: date(): Windows does not support dates prior to midnight (00:00:00), January 1, 1970 in \birthdaysearch.php on line 41

[thetestingsite]

Taken from the php.net website:



The valid range of a timestamp is typically from Fri, 13 Dec 1901 20:45:54 GMT to Tue, 19 Jan 2038 03:14:07 GMT. (These are the dates that correspond to the minimum and maximum values for a 32-bit signed integer). However, before PHP 5.1.0 this range was limited from 01-01-1970 to 19-01-2038 on some systems (e.g. Windows).


That's for version 5.1.0 and later. What version are you running and what OS is it? That could be the reason why you are getting that error.

Hope this helps.

[tomyknoker]

Ahhhhh damn it's a Windows server... So is there no workaround?

[thetestingsite]

Not according to PHP.net. Sorry.

[boxxertrumps]

I have a workaround.

get a linux...