php_novice2007 Posted March 17, 2007 Share Posted March 17, 2007 Hi, Say in my database I've got a table with structure of time, long, lat Time is in the timestamp. I want to write some code that allows the user to input in two times, A and B. And the program will output all the data from time A to time B. The thing is, I can't just get the user to enter the date right? since the database will search for an exact time, down to the seconds.. The only way I can think of getting round this is to read all the times, and put them into two drop down lists and the user just choose the from and the end time. Though I don't know how to do this and will have to google stuff.. Any other suggestions? Link to comment https://forums.phpfreaks.com/topic/43101-searching-in-mysql/ Share on other sites More sharing options...
Daniel0 Posted March 17, 2007 Share Posted March 17, 2007 mysql_query("SELECT * FROM table WHERE time>{$time_one} AND time<{$time_two}"); Would this do it? Link to comment https://forums.phpfreaks.com/topic/43101-searching-in-mysql/#findComment-209356 Share on other sites More sharing options...
php_novice2007 Posted March 17, 2007 Author Share Posted March 17, 2007 yes but what i'm saying is that the user will have to enter the extact times for $time_one and $time_two, and they'll have to know it down to the seconds for Mysql to produce results right? I guess I'm asking whether there is a way to display data say from 2/2/07 till 10/2/07, without knowing the time of day. And the field has timestamp structure... Link to comment https://forums.phpfreaks.com/topic/43101-searching-in-mysql/#findComment-209363 Share on other sites More sharing options...
Daniel0 Posted March 17, 2007 Share Posted March 17, 2007 Make a form containing three fields/dropdowns: day, month, year Then use mktime to make it into a timestamp (using 0 at the second, minute and hour parameter). E.g.: $time_one = mktime(0,0,0,$_POST['month_start'],$_POST['day_start'],$_POST['year_start']); Then do that for the end times as well. http://php.net/mktime Link to comment https://forums.phpfreaks.com/topic/43101-searching-in-mysql/#findComment-209364 Share on other sites More sharing options...
php_novice2007 Posted March 18, 2007 Author Share Posted March 18, 2007 cool thanks! I actually misread your answer and didn't see the less than and greater than signs Just wondering, for this: $query="SELECT `fixdatetime` FROM `$dbtable` WHERE fixdatetime > '$start' AND fixdatetime < '$end'"; $result=mysql_query($query, $link) or die("Unable to load selected table"); If there is no result from the search, what would $result be? How can I check $result somehow to see theres no results? I tried $num=mysql_num_rows($result); and seeing if $num = 0, but I get an error excuting this.. Also another thing.. When I try to output my results, how come it only outputs the times, I get errors when I try to get the lat and long out.. My code to do the whole thing: $query="SELECT `fixdatetime` FROM `$dbtable` WHERE fixdatetime > '$start' AND fixdatetime < '$end'"; $result=mysql_query($query, $link) or die("Unable to load selected table"); //find out how many rows there are in the database $num=mysql_num_rows($result) or die("Unable to count the number of rows"); //close link mysql_close($link) or die("Unable to close database"); if ($num > 0) { ?> <html> <body> <h1><center>Results from <? echo $start?> to <? echo $end ?></center></h1><br> <table border="1" cellspacing="2" cellpadding="2"> <tr> <th><font face="Arial, Helvetica, sans-serif">Date and Time</font></th> <th><font face="Arial, Helvetica, sans-serif">Latitude</font></th> <th><font face="Arial, Helvetica, sans-serif">Longitude</font></th> </tr> <?php $i=0; //to iterate through each row while ($i < $num) { $time=mysql_result($result,$i,"fixdatetime"); $lat=mysql_result($result,$i,"lat"); $long=mysql_result($result,$i,"lng"); //only getting the fields we want ?> <tr> <td><font face="Arial, Helvetica, sans-serif"><?php echo $time; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><?php echo $lat; ?></font></td> <td><font face="Arial, Helvetica, sans-serif"><?php echo $long; ?></font></td> </tr> <?php $i++; } } else { print "There is no results from your search, please go back and try again."; } And my errors: Warning: mysql_result() [function.mysql-result]: lat not found in MySQL result index 3 in I:\webpages\search.php on line 48 Warning: mysql_result() [function.mysql-result]: lng not found in MySQL result index 3 in I:\webpages\search.php on line 49 Thanks! Link to comment https://forums.phpfreaks.com/topic/43101-searching-in-mysql/#findComment-209843 Share on other sites More sharing options...
jokur Posted March 18, 2007 Share Posted March 18, 2007 Just use @mysql_num_rows, it will hide any notices generated by mysql_num_rows. You can't display the lat and long since you are not retrieving them in your initial query. You are only SELECTing 'fixdatetime'. Add whatever other fields you need to the query and then you shouldn't have a problem. Link to comment https://forums.phpfreaks.com/topic/43101-searching-in-mysql/#findComment-209848 Share on other sites More sharing options...
php_novice2007 Posted March 18, 2007 Author Share Posted March 18, 2007 I solved the second problem, I didn't have a '*' in my query and so it was only showing the times, lol :S Oh cool thanks! that was easy Link to comment https://forums.phpfreaks.com/topic/43101-searching-in-mysql/#findComment-209849 Share on other sites More sharing options...
php_novice2007 Posted March 18, 2007 Author Share Posted March 18, 2007 sorry, how does @mysql_num_rows work? if(!$result) doesn't seem to stop the error... Link to comment https://forums.phpfreaks.com/topic/43101-searching-in-mysql/#findComment-209850 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.