topflight Posted February 11, 2010 Share Posted February 11, 2010 Hello I have a table called called flights where it has all my flights info. But in that table I have a column called time. I want to put pull a random 15 flights who time is 30mins from the current time. For instance if the current time is 4:30 I want to pull a random 15 rows who time is 35 mins from now(Which is 4:30) how can I do that. I have reccevied help, and someone provided the following query select * from yourtable where timecolumn between date_format(now(),'%H%i') and addtime(date_format(now(),'%H%i'),30) order by rand() limit 15 So then I edit the code to look like this <?php include'config.php'; $get = mysql_query("SELECT * from `dep` WHERE dtime between date_format(now(),'%H%i') and addtime(date_format(now(),'%H%i'),30) order by rand() limit 15");?><table width="100%"><?while($row = mysql_fetch_assoc($get)){?><td><? echo "$row[dtime]";?></td><?php}?></table> In the database the time does not have a ":" between the hours and mins, inseated they look like this 1730 instead of 1730 and the time is in 24 hour. Please help. Thanks in advanced! Quote Link to comment Share on other sites More sharing options...
schilly Posted February 11, 2010 Share Posted February 11, 2010 what type of field is dtime? Quote Link to comment Share on other sites More sharing options...
topflight Posted February 11, 2010 Author Share Posted February 11, 2010 It is decimal(4,0) thats the way I reccevied it and I have 15,000 records so I which I can put ":" between the hour and min. will it still work tho? Quote Link to comment Share on other sites More sharing options...
schilly Posted February 11, 2010 Share Posted February 11, 2010 not with that query. it is assuming you are using a datetime or timestamp field. try SELECT * from `dep` WHERE dtime >= $time_minus_thirty AND dtime <= $time_plus_thirty order by rand() limit 15 you going to need to create time_minus_thirty and time_plus_thirty by yourself using php. Quote Link to comment Share on other sites More sharing options...
topflight Posted February 11, 2010 Author Share Posted February 11, 2010 I am confused how can I do that? Quote Link to comment Share on other sites More sharing options...
schilly Posted February 11, 2010 Share Posted February 11, 2010 $hour = date('G'); $minute = date('i'); //add 30 to minutes. if its over 60 minus 60 off it and add one to hour. if hour is then over 23 set to 1. similar for minus 30 minutes. //put your times together $time_string = $hour . $minute; //now use $time_string in your query. Quote Link to comment Share on other sites More sharing options...
topflight Posted February 11, 2010 Author Share Posted February 11, 2010 I am starting to get it a little bit, can you please write the whole SQL with variables and everything please Quote Link to comment Share on other sites More sharing options...
schilly Posted February 11, 2010 Share Posted February 11, 2010 Well I've already done the query and pretty much spelled out the whole thing so I believe it's up to you now. A little trial and error and you should have it. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.