php - Mysql query for sorting Time in hms format -

i trying sort data in mysql descendingly based on time videos.

i have video_duration column in database in store time videos in hms format( example 1h54m3s, 9m3s, 0m3s).

when try sort database using query below getting output 9m0s, 9m0s first instead of time more time(like 1h35m29s).

i using following query sort database

$sql = "select * videos category_name='$category' order video_duration desc limit 50"; 

can guys me solve problem

unfortunately data saved database not in best way. varchar sortable follows rules of alphabetical order. example if want order first 12 numbers with:

select top 12 value number_in_order order value asc 

i obtain this:

0 1 10 11 2 3 4 5 6 7 8 9 

that wrong.

the correct way handle length of video files using int field store amount of seconds , via php retrive value , format in user-readable way.

what suggest create new column called "new_video_duration" int, using simple routine in php read data varchar column, populate new 1 correct value in seconds.

you can parse string value:

$stringvalue = "1h3m40s"; $h = 0; $m = 0; $s = 0;  $splitted = $stringvalue.split("h");  if($splitted.length > 0){     $h = $splitted[0];     $splitted = $splitted[1].split("m"); }else{     $splitted = $splitted[0].split("m"); }  $m = $splitted[0]; $s = $splitted[1];  $intvalue = ((($h * 60) + $m) * 60) + $s; 

now inside var $intvalue have correct time in seconds can stored inside new column in database.

once you've converted values can delete old column as:

alter table table_name drop column video_duration 

and rename new column in:

alter table table_name rename column new_video_duration video_duration 

and you're done.

cheers