...

View Full Version : Searching date from and to...



tomyknoker
07-09-2007, 03:11 AM
I am trying to set up a php search page, I have had no issues with searching my FirstName and LastName fields in my db... But I need to do a search for MembersLogin from 12/12/2006 to 12/12/2007, the dates would be able to be filled in by a user and submitted. However someone told me it's really complicated to do this as my column data type is a DATE field... Apparently a UNIX TIMESTAMP is alot easier to do this kind if search :confused: ???

Anyway it's too late to edit the database now so if anyone has any ideas please let me know!

Thanks in advance!

CFMaBiSmAd
07-09-2007, 03:51 AM
If your database contains a DATE type column, this could not be any easier. Use the mysql STR_TO_DATE() function with the proper format string to convert your mm/dd/yyyy dates into a DATE type and use the results in a WHERE comparison in your query - http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_str-to-date

tomyknoker
07-09-2007, 04:43 AM
Hey cool... Ok well I read the whole page and took a bit in, but I really struggle with php so if you can bear with me would be appreciated...

My Search Page Currently


<?php

//connect to db
include 'library/config.php';
include 'library/opendb.php';

?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" />
<title>Untitled Document</title>
</head>

<body>
<form id="form1" name="form1" method="post" action="memberResults.php">
<table width="900" border="0" cellpadding="0" cellspacing="0">
<tr>
<td>Email
<input type="text" name="email" /></td>
<td>&nbsp;</td>
<td>State
<select name="state">
<option value="nsw">NSW</option>
<option value="vic">VIC</option>
<option value="qld">QLD</option>
<option value="tas">TAS</option>
<option value="sa">SA</option>
<option value="act">ACT</option>
<option value="wa">WA</option>
<option value="nt">NT</option>
</select></td>
</tr>
<tr>
<td colspan="3">Below this does not work so don't worry about it</td>
</tr>
<tr>
<td>JoinDateFrom
<input type="text" name="joindatefrom" /> (format of yyyy/mm/dd)</td>
<td>JoinDateTo
<input type="text" name="joindateto" /> (format of yyyy/mm/dd)</td>
<td>&nbsp;</td>
</tr>
<tr>
<td>ApprovedDateFrom
<input type="text" name="approveddatefrom" /> (format of yyyy/mm/dd)</td>
<td>ApprovedDateTo
<input type="text" name="approveddateto" /> (format of yyyy/mm/dd)</td>
<td>&nbsp;</td>
</tr>
<tr>
<td>LastLoginDateFrom
<input type="text" name="loginfrom" /> (format of yyyy/mm/dd)</td>
<td>LastLoginDateTo
<input type="text" name="loginto" /> (format of yyyy/mm/dd)</td>
<td>&nbsp;</td>
</tr>
<tr>
<td>&nbsp;</td>
<td>&nbsp;</td>
<td><input type="submit" name="Submit" value="Search" /></td>
</tr>
<tr>
<td>&nbsp;</td>
<td>&nbsp;</td>
<td>&nbsp;</td>
</tr>
<tr>
<td>&nbsp;</td>
<td>&nbsp;</td>
<td>&nbsp;</td>
</tr>
</table>
</form>
</body>
</html>

<?php
//close the connection
include 'library/closedb.php';

?>

And my result page... Or atleast up too the query...



/*set varibles from form */
$firstName = trim($_POST['firstname']);
$lastName = trim($_POST['lastname']);
$email = trim($_POST['email']);
$rep = $_POST['rep'];
$state = $_POST['state'];
$status = $_POST['status'];


$results = mysql_query("SELECT * FROM `tblmembers` WHERE `FirstName` LIKE '%$firstName%' AND `LastName` LIKE '%$lastName%` AND `Email` LIKE '%$email%' AND `State`='$state' AND `mng`='$rep' AND `MemberApproved`='$status' AND `loginDateTime` LIKE ('$joinDateTo'-'$joinDateFrom')") or die ('Error: '.mysql_error ());


/* Display the results found and what column it was found in */
?>

Hmm yea I'm not getting it... I read this post and thought I could make sense of it but no luck... Is my query above kind of thing on the right track? I was working from the below example...


SELECT nick FROM `users` WHERE TO_DAYS( NOW( ) ) - TO_DAYS( last_login ) <=1 AND DATE_FORMAT( CURRENT_TIMESTAMP( ) , '%H%i' ) - DATE_FORMAT( last_login, '%H%i' ) <=5 ORDER BY `nick` ASC;

tomyknoker
07-10-2007, 04:01 AM
Anyone? I'm kind of desperate...

phpandmysql
07-10-2007, 04:45 PM
I use a simple greater than and less than, wouldn't something like the following work for you?



$early_date = "2006-12-12";
$later_date = "2007-12-12";
$sql = mysql_query("SELECT * FROM users WHERE date > '$early_date' && date < '$later_date'");

//this next part is just to show how many rows for testing
$rows = mysql_num_rows($sql);
echo $rows;


Thats what I use.


so your code might be:



$results = mysql_query("SELECT * FROM `tblmembers` WHERE `FirstName` LIKE '%$firstName%' AND `LastName` LIKE '%$lastName%` AND `Email` LIKE '%$email%' AND `State`='$state' AND `mng`='$rep' AND `MemberApproved`='$status' AND `loginDateTime` > '$joinDateTo' AND 'loginDateTime' < '$joinDateFrom'") or die ('Error: '.mysql_error ());

tomyknoker
07-13-2007, 05:11 AM
hey there that's great! i was so excited when i got it to work... a couple of questions though... say if i have this query just as a test


/*set varibles from form */
$early_date = "2005-08-22";
$later_date = "2005-08-24";
$sql = mysql_query("SELECT * FROM `tblmembers` WHERE (`JoinDate` > '$early_date' && `JoinDate` < '$later_date')");

//this next part is just to show how many rows for testing
$rows = mysql_num_rows($sql);
echo $rows;

it gives me the results for 2005-08-23, is there any way to include the early date and later date in the range?

Sussex_Chris
09-18-2009, 01:37 PM
hey there that's great! i was so excited when i got it to work... a couple of questions though... say if i have this query just as a test


/*set varibles from form */
$early_date = "2005-08-22";
$later_date = "2005-08-24";
$sql = mysql_query("SELECT * FROM `tblmembers` WHERE (`JoinDate` > '$early_date' && `JoinDate` < '$later_date')");

//this next part is just to show how many rows for testing
$rows = mysql_num_rows($sql);
echo $rows;

it gives me the results for 2005-08-23, is there any way to include the early date and later date in the range?

;
$sql = mysql_query("SELECT * FROM `tblmembers` WHERE (`JoinDate` >= '$early_date' && `JoinDate` <= '$later_date')");



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum