...

View Full Version : Formatting Search Date As It's Entered



tomyknoker
07-14-2007, 04:40 PM
I have a search form which is set up to search Date's within my database... At the moment though the dates have to be entered into the search field in the standard format '0000-00-00'... When the results are displayed however I have managed to format them out how I want them '00-00-0000' using the following:


<?php echo date('d/m/Y', strtotime($qry["JoinDate"])); ?>
<?php echo($qry['loginDateTime']?date('d/m/Y', strtotime($qry['loginDateTime'])):'unknown') ?>

Just wondering what I need to change so in the search the dates can be entered in the following format '00-00-0000' so basically Day/Month/Year...

olddocks
07-14-2007, 07:44 PM
why do you take so much risk. Here is the simple way.

To Insert into Database

INSERT INTO <dbase> ( SET date=now()) where ....

While retrieving

SELECT * from Table where date = now()

Note: i have not completed the SQL query. just to indicate how now() is used.

tomyknoker
07-15-2007, 03:08 AM
But I'm conducting a search I'm not inserting any data into my database...

NancyJ
07-15-2007, 09:42 AM
Your question is a bit confusing but I'm assuming you're wanting to capture user inputted dates and format them for an sql query.

The easier way to do that is


$sqlDate = date('Y-m-d', strtotime($inputDate));


Ofcourse that doesnt help you if anyone enters dates in a UK format date. (maybe europeans too)

The very easiest way to get around all date formatting and user input problems is to use select boxes for your dates. You know all days will be in the range of 1-31 and all months 1-12. I use dropdowns for year too but if you have no idea of the range of expected data I guess you could use text box.
Then its not confusing for your application or your users and you format dates as



$date = $_POST['year'].'-'.str_pad($_POST['month'], 2, 0, STR_PAD_LEFT).'-'.str_pad($_POST['day'], 2, 0, STR_PAD_LEFT);

tomyknoker
07-15-2007, 10:05 AM
Hi Nancy I like what you're saying, the thing is it's just for a backend search for admins, so the date format would always be the same... And yes I didn't explain well but you got it, I just simply want the user to be able to enter the date like DD-MM-YYYY and get a result... They currently have to enter it like this YYYY-DD-MM like it sits in the database... Can you tell me how to do it with just a text box? I like the drop down way but have spent so much time getting this far... So just want to stick to the textfield idea...

NancyJ
07-15-2007, 11:20 AM
If they are always entering the date as DD-MM-YYYY then its easy


$dateParts = explode("-", $inputDate);
$dateParts = array_reverse($dateParts);
$date = implode("-", $dateParts);

tomyknoker
07-15-2007, 11:26 AM
Sorry they would enter the dates as DD/MM/YYYY oops! So do I put the above code on the form? Or my page which processes the query? Actually this is to do with my other post too... There will be two fields where dates are entered, $early_Joindate and $later_Joindate...

NancyJ
07-15-2007, 11:32 AM
you put it where-ever you're sanitising your date inputs. And just change the first line to explode on "/" instead of "-"

tomyknoker
07-15-2007, 01:35 PM
you put it where-ever you're sanitising your date inputs. And just change the first line to explode on "/" instead of "-"

Sorry to be a pain... I'm just learning this but getting there slowly but surely... So this is my 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'];
$early_Joindate = $_POST['early_Joindate'];
$later_Joindate = $_POST['later_Joindate'];
$early_Approveddate = $_POST['early_Approveddate'];
$later_Approveddate = $_POST['later_Approveddate'];

$sql = "SELECT * FROM `tblmembers` WHERE `FirstName` LIKE '%$firstName%'
AND `LastName` LIKE '%$lastName%'
AND `Email` LIKE '%$email%'
AND `State` LIKE '%$state%'
AND `MemberApproved` LIKE '%$status%'
AND `rep_NBR` LIKE '%$rep%'
";
if(!empty($early_Joindate) && !empty($later_Joindate))
{
$sql .= "AND `JoinDate` >= '$early_Joindate' && `JoinDate` <= '$later_Joindate'";
}
if(!empty($early_Approveddate) && !empty($later_Approveddate))
{
$sql .= "AND `MemberApprovedDate` >= '$early_Approveddate' && `MemberApprovedDate` <= '$later_Approveddate'";
}
$sql .= "ORDER BY `LastName`";

So I have 4 date variables above all the code should I do something like this? Or have I totally missed it?


$dateParts = explode("/", $early_Joindate, $later_Joindate, $early_Approveddate, $later_Approveddate);
$dateParts = array_reverse($dateParts);
$date = implode("/", $dateParts);

NancyJ
07-15-2007, 01:37 PM
you need to apply the code to each date individually not all at the same time

tomyknoker
07-15-2007, 01:42 PM
right ok... so then like this? using the first two as an example...



$datePartsOne = explode("/", $early_Joindate);
$datePartsOne = array_reverse($datePartsOne);
$date = implode("/", $datePartsOne);

$datePartsTwo = explode("/", $later_Joindate);
$datePartsTwo = array_reverse($datePartsTwo);
$date = implode("/", $datePartsTwo);

/*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'];
$early_Joindate = $_POST['early_Joindate'];
$later_Joindate = $_POST['later_Joindate'];
$early_Approveddate = $_POST['early_Approveddate'];
$later_Approveddate = $_POST['later_Approveddate'];

$sql = "SELECT * FROM `tblmembers` WHERE `FirstName` LIKE '%$firstName%'
AND `LastName` LIKE '%$lastName%'
AND `Email` LIKE '%$email%'
AND `State` LIKE '%$state%'
AND `MemberApproved` LIKE '%$status%'
AND `rep_NBR` LIKE '%$rep%'
";
if(!empty($early_Joindate) && !empty($later_Joindate))
{
$sql .= "AND `JoinDate` >= '$early_Joindate' && `JoinDate` <= '$later_Joindate'";
}
if(!empty($early_Approveddate) && !empty($later_Approveddate))
{
$sql .= "AND `MemberApprovedDate` >= '$early_Approveddate' && `MemberApprovedDate` <= '$later_Approveddate'";
}
$sql .= "ORDER BY `LastName`";

NancyJ
07-15-2007, 02:41 PM
the implode should still be using - not /
Also you're processing the date variables before you've set them.

tomyknoker
07-15-2007, 03:02 PM
I'm confused... Can you explain in my code how I should write it?

NancyJ
07-15-2007, 03:53 PM
which parts are you confused about? If I just write it for you, you still wont understand

tomyknoker
07-16-2007, 11:42 AM
no don't want you to write it for me... just not sure where to put it so something like this maybe?




/*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'];
$early_Joindate = $_POST['early_Joindate'];
$later_Joindate = $_POST['later_Joindate'];
$early_Approveddate = $_POST['early_Approveddate'];
$later_Approveddate = $_POST['later_Approveddate'];

$datePartsOne = explode("/", $early_Joindate);
$datePartsOne = array_reverse($datePartsOne);
$date = implode("-", $datePartsOne);

$datePartsTwo = explode("/", $later_Joindate);
$datePartsTwo = array_reverse($datePartsTwo);
$date = implode("-", $datePartsTwo);



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum