Professional Web Applications Themes

Remove character in select * mysql - MySQL

I've got data in a column with the time entered by PHP. The time was written as 09:30. I need to search my table for a time that is earlier than the current date and time. The date is in another column. The PROBLEM: I need to remove th ":"(colon) in my search criteria. For eg."select * from (table) where (field) = (var) and duedate <= '$date' and duetime <= '$time'" I've currently got the current time in a format like - 15:03 to correspond with the time field, but the search is not very acurate. Any Suggestions....

  1. #1

    Default Remove character in select * mysql

    I've got data in a column with the time entered by PHP.
    The time was written as 09:30.
    I need to search my table for a time that is earlier than the current
    date and time. The date is in another column.
    The PROBLEM: I need to remove th ":"(colon) in my search criteria.
    For eg."select * from (table) where (field) = (var) and duedate <=
    '$date' and duetime <= '$time'"
    I've currently got the current time in a format like - 15:03 to
    correspond with the time field, but the search is not very acurate.
    Any Suggestions.

    stevenvanwyk@gmail.com Guest

  2. #2

    Default Re: Remove character in select * mysql

    On 30 Mar, 15:11, com wrote: 

    Is this column actually a TIME datatype?
    "...but the search is not very acurate..." Care to expand on what
    about this is not accurate?

    Captain Guest

  3. #3

    Default Re: Remove character in select * mysql

    > Is this column actually a TIME datatype? 

    No it a CHAR column.
    I'm running a search where I need to find out if a job is late. So
    I've got the current time as a reference and I'm looking into the DB
    to view the duedate and the duetime. The required result I'm looking
    for is whether the job is LATE or NOT LATE.
    The search I'm running is: ."select * from workticket where id = '$id'
    and duedate <= '$date' and duetime <= '$time'"
    The Result: If 'duedate' and the 'duetime' is less than my current
    date and time, I get my required result which is LATE. But if my
    'duedate' is less than my current date and my 'duetime' is more(ie.
    current time is earlier than 'duetime'), I get a NOT LATE response,
    but the job is still late!


    stevenvanwyk@gmail.com Guest

  4. #4

    Default Re: Remove character in select * mysql

    On 30 Mar, 15:25, com wrote: 
    >
    > No it a CHAR column.
    > I'm running a search where I need to find out if a job is late. So
    > I've got the current time as a reference and I'm looking into the DB
    > to view the duedate and the duetime. The required result I'm looking
    > for is whether the job is LATE or NOT LATE.
    > The search I'm running is: ."select * from workticket where id = '$id'
    > and duedate <= '$date' and duetime <= '$time'"
    > The Result: If 'duedate' and the 'duetime' is less than my current
    > date and time, I get my required result which is LATE. But if my
    > 'duedate' is less than my current date and my 'duetime' is more(ie.
    > current time is earlier than 'duetime'), I get a NOT LATE response,
    > but the job is still late![/ref]

    OK, now we know what the problem is, we can fix it!

    Try either:
    CONCAT(`duedate`,`duetime`) <= '{$date}{$time}'

    or

    `duedate` < '$date' OR (`duedate` = '$date' AND `duetime` <= '$time')

    Captain Guest

Similar Threads

  1. Question Remove character from xml while importing
    By Unregistered in forum Brainstorming Area
    Replies: 0
    Last Post: November 18th, 03:16 PM
  2. Remove a character from end?
    By bigbrain28 in forum Coldfusion - Advanced Techniques
    Replies: 3
    Last Post: September 20th, 05:23 PM
  3. Replies: 2
    Last Post: January 23rd, 01:34 PM
  4. [PHP] How to remove new line character?
    By Simon in forum PHP Development
    Replies: 4
    Last Post: November 7th, 07:52 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139