Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 15 of 15
  1. #1
    Regular Coder
    Join Date
    Mar 2006
    Posts
    459
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Formatting Search Date As It's Entered

    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 Code:
    <?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...

  • #2
    New to the CF scene
    Join Date
    Jul 2007
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  • #3
    Regular Coder
    Join Date
    Mar 2006
    Posts
    459
    Thanks
    3
    Thanked 0 Times in 0 Posts
    But I'm conducting a search I'm not inserting any data into my database...

  • #4
    Senior Coder NancyJ's Avatar
    Join Date
    Feb 2005
    Location
    Bradford, UK
    Posts
    3,172
    Thanks
    19
    Thanked 65 Times in 64 Posts
    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
    PHP Code:
    $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
    PHP Code:

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

  • #5
    Regular Coder
    Join Date
    Mar 2006
    Posts
    459
    Thanks
    3
    Thanked 0 Times in 0 Posts
    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...

  • #6
    Senior Coder NancyJ's Avatar
    Join Date
    Feb 2005
    Location
    Bradford, UK
    Posts
    3,172
    Thanks
    19
    Thanked 65 Times in 64 Posts
    If they are always entering the date as DD-MM-YYYY then its easy
    PHP Code:
    $dateParts explode("-"$inputDate);
    $dateParts array_reverse($dateParts);
    $date implode("-"$dateParts); 

  • #7
    Regular Coder
    Join Date
    Mar 2006
    Posts
    459
    Thanks
    3
    Thanked 0 Times in 0 Posts
    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...
    Last edited by tomyknoker; 07-15-2007 at 11:28 AM.

  • #8
    Senior Coder NancyJ's Avatar
    Join Date
    Feb 2005
    Location
    Bradford, UK
    Posts
    3,172
    Thanks
    19
    Thanked 65 Times in 64 Posts
    you put it where-ever you're sanitising your date inputs. And just change the first line to explode on "/" instead of "-"

  • #9
    Regular Coder
    Join Date
    Mar 2006
    Posts
    459
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by NancyJ View Post
    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...

    PHP Code:
    /*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?

    PHP Code:
    $dateParts explode("/"$early_Joindate$later_Joindate$early_Approveddate$later_Approveddate);
    $dateParts array_reverse($dateParts);
    $date implode("/"$dateParts); 

  • #10
    Senior Coder NancyJ's Avatar
    Join Date
    Feb 2005
    Location
    Bradford, UK
    Posts
    3,172
    Thanks
    19
    Thanked 65 Times in 64 Posts
    you need to apply the code to each date individually not all at the same time

  • #11
    Regular Coder
    Join Date
    Mar 2006
    Posts
    459
    Thanks
    3
    Thanked 0 Times in 0 Posts
    right ok... so then like this? using the first two as an example...

    PHP Code:
    $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`"

  • #12
    Senior Coder NancyJ's Avatar
    Join Date
    Feb 2005
    Location
    Bradford, UK
    Posts
    3,172
    Thanks
    19
    Thanked 65 Times in 64 Posts
    the implode should still be using - not /
    Also you're processing the date variables before you've set them.

  • #13
    Regular Coder
    Join Date
    Mar 2006
    Posts
    459
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I'm confused... Can you explain in my code how I should write it?

  • #14
    Senior Coder NancyJ's Avatar
    Join Date
    Feb 2005
    Location
    Bradford, UK
    Posts
    3,172
    Thanks
    19
    Thanked 65 Times in 64 Posts
    which parts are you confused about? If I just write it for you, you still wont understand

  • #15
    Regular Coder
    Join Date
    Mar 2006
    Posts
    459
    Thanks
    3
    Thanked 0 Times in 0 Posts
    no don't want you to write it for me... just not sure where to put it so something like this maybe?



    PHP Code:
    /*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); 


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •