Is there way to compare time values stored as string value in php mysql?
Get the solution ↓↓↓I have a column (time) in database with time stored asvarchar
in the formathh.mm.ss
. I have a PHP string variable with time value stored in same format . I need to select all rows where time column values greater than variable time value.
I tried this query but it's not working
$timevar='15.26.00';
$sql="select name,id from device where time > '$timevar' ";
Is there any other way I can achieve this without modifying my table structure?
Answer
Solution:
If your column is a varchar just use replace function to get rid of dots:replace(value, '.', '')
. You will get strings with integers, like :152600
and those can be directly compared against each other.
Fiddle: http://sqlfiddle.com/#!9/ca8e4b/12/0
Schema:
Create table a (b varchar(10));
Insert into a Values ('16.00.00');
Insert into a Values ('17.00.00');
Insert into a Values ('16.01.00');
Insert into a Values ('15.59.00');
Insert into a Values ('16.00.01');
Query:
select * from a where replace(b, '.', '') > replace('16.00.00', '.', '');
a is table name so device in your case, a is column with times, so time in your case.
Result:
| b |
|
Your query would look something like this:
{-code-6}
When this starts working for you fix sql injection issues as mentioned in comments. Also even better solution would be to migrate to time column type.
Answer
Share solution ↓
Additional Information:
Link To Answer People are also looking for solutions of the problem: warning: undefined array key
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Similar questions
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.