Domain Beans Posted January 14, 2011 Share Posted January 14, 2011 I've got a database that lists off rows in a table (sql) one of the columns is a datetime stamp, whats the easiest way of coding a drop down box to show dates from -> to a certain date. Any help appreciated. Link to comment Share on other sites More sharing options...
0 AnthonySterling Posted January 14, 2011 Share Posted January 14, 2011 What front-end language are you using? Is this intended drop-down to be influenced at all by the current schema entries? Link to comment Share on other sites More sharing options...
0 Domain Beans Posted January 14, 2011 Author Share Posted January 14, 2011 Sorry forgot to mention, its in php, currently it just lists off the whole database on a page, I need to filter by a to from date preferably in drop down boxes, the field in the database that contains the date is a mysql datetime stamp if thats any help? Link to comment Share on other sites More sharing options...
0 AnthonySterling Posted January 14, 2011 Share Posted January 14, 2011 Possibly. :) To generate a quick drop-down of dates, like the following:- <form action="" method="post"> <select name="day"> <option value="13-12-2011">Tue, 13 Dec 2011 00:00:00 +0000</option> <option value="14-12-2011">Wed, 14 Dec 2011 00:00:00 +0000</option> <option value="15-12-2011">Thu, 15 Dec 2011 00:00:00 +0000</option> <option value="16-12-2011">Fri, 16 Dec 2011 00:00:00 +0000</option> <option value="17-12-2011">Sat, 17 Dec 2011 00:00:00 +0000</option> <option value="18-12-2011">Sun, 18 Dec 2011 00:00:00 +0000</option> <option value="19-12-2011">Mon, 19 Dec 2011 00:00:00 +0000</option> <option value="20-12-2011">Tue, 20 Dec 2011 00:00:00 +0000</option> <option value="21-12-2011">Wed, 21 Dec 2011 00:00:00 +0000</option> <option value="22-12-2011">Thu, 22 Dec 2011 00:00:00 +0000</option> <option value="23-12-2011">Fri, 23 Dec 2011 00:00:00 +0000</option> <option value="24-12-2011">Sat, 24 Dec 2011 00:00:00 +0000</option> <option value="25-12-2011">Sun, 25 Dec 2011 00:00:00 +0000</option> </select> </form> You could use:- <?php function get_days($from, $to, $format = 'r'){ $days = array(); $from = strtotime($from); $to = strtotime($to); while($from <= $to){ $days[date('d-m-Y', $from)] = date($format, $from); $from = strtotime('+1 day', $from); } return $days; } ?> <form action="" method="post"> <select name="day"> <?php foreach(get_days('13-12-2011', '25-12-2011') as $short => $formatted): ?> <option value="<?php echo $short; ?>"><?php echo $formatted; ?></option> <?php endforeach; ?> </select> </form> That should be enough to get you started. :) Feel free to let me know if you get stuck. Anthony. Domain Beans 1 Share Link to comment Share on other sites More sharing options...
0 Domain Beans Posted January 14, 2011 Author Share Posted January 14, 2011 That's great so far, I've now got two drop downs one named from, one named two. I have never worked with date time, so now I'm pretty stuck for how to get all of the rows from the database within the submitted forms dates, the database field is datet and is a datetime format as follows 2011-01-12 17:57:08 any ideas? :) Link to comment Share on other sites More sharing options...
0 Calculator Posted January 14, 2011 Share Posted January 14, 2011 You'll need two of those <select> fields and give them different names, e.g. "from" and "to". Then, when the form is submitted and you're ready to query the database, you can use BETWEEN ... AND ... in your WHERE clause to filter on the specified date range. It's recommended that you first convert the dates to MySQL format so that MySQL doesn't have to worry about malformatted datetime identifiers. <?php // Convert d-m-Y values to Unix timestamps // strtotime correctly parses this format, so no need for special parsing functions here $date_from = strtotime( $_GET['from'] ); $date_to = strtotime( $_GET['to'] ); // Format the timestamps to valid MySQL date strings: "YY-MM-DD HH:MM:SS" (in PHP: "Y-m-d H:i:s") $date_from_mysql = date('Y-m-d H:i:s', $date_from); $date_to_mysql = date('Y-m-d H:i:s', $date_to); // Build and run the query $query = "SELECT * FROM my_table WHERE my_date BETWEEN '{$date_from_mysql}' AND '{$date_to_mysql}' "; $result = mysql_query($query); // ... ?> You might want to throw in some more validation checks on the received form data before placing them in the query (e.g. invalid dates or null values), but normally this should already be pretty secure. By formatting the dates before passing them to the query, you're already safe from any SQL injections through these fields since invalid dates won't make it through the strtotime() conversion. See if that works for you. :) Link to comment Share on other sites More sharing options...
0 Domain Beans Posted January 14, 2011 Author Share Posted January 14, 2011 Right, Added everyones codes seem to have a problem, its not displaying anything at the moment :). <? function get_days($from, $to, $format = 'r'){ $days = array(); $from = strtotime($from); $to = strtotime($to); while($from <= $to){ $days[date('Y-m-d', $from)] = date($format, $from); $from = strtotime('+1 day', $from); } return $days; } ?> <h2>Yahoo Click Statistics</h2> <p>Below are the yahoo click statistics that are currently stored in the database.</p> <form action="stats.php" method="post"> From:<select name="from" id="from"> <?php foreach(get_days('01-01-2011', '31-12-2011') as $short => $formatted): ?> <option value="<?php echo $short; ?>"><?php echo $short; ?></option> <?php endforeach; ?> </select> To:<select name="to" id="to"> <?php foreach(get_days('01-01-2011', '31-12-2011') as $short => $formatted): ?> <option value="<?php echo $short; ?>"><?php echo $short; ?></option> <?php endforeach; ?> </select> <input type="submit" name="mysubmit" value="Search" /> </form> <?php $date_from = strtotime( $_POST['from'] ); $date_to = strtotime( $_POST['to'] ); // Format the timestamps to valid MySQL date strings: "YY-MM-DD HH:MM:SS" (in PHP: "Y-m-d H:i:s") $date_from_mysql = date('Y-m-d H:i:s', $date_from); $date_to_mysql = date('Y-m-d H:i:s', $date_to); // Build and run the query $query = "SELECT * FROM binfo_britinfo.yahooclicks WHERE datet BETWEEN '{$date_from_mysql}' AND '{$date_to_mysql}' GROUP BY url ORDER By COUNT(url) DESC"; $result = mysql_query($query); ?> <div> <table border="1" style="font-size:12px;font-family:tahoma;"> <tr> <td><b>URL</b></td><td><b>Yahoo Clicks</b></td> </tr> <? while ($row = mysql_fetch_array($result)) { echo "<tr>"; echo "<td>"; echo $row['url']; echo "</td>"; echo "<td>"; echo $row['COUNT(url)']; echo "</td>"; echo "</tr>"; } echo "</table></div>"; ?> Ahh got it working, however the count isn't displaying now! Link to comment Share on other sites More sharing options...
0 firey Posted January 14, 2011 Share Posted January 14, 2011 Right, Added everyones codes seem to have a problem, its not displaying anything at the moment :). // Build and run the query $query = "SELECT * FROM binfo_britinfo.yahooclicks WHERE datet BETWEEN '{$date_from_mysql}' AND '{$date_to_mysql}' GROUP BY url ORDER By COUNT(url) DESC"; $result = mysql_query($query); while ($row = mysql_fetch_array($result)) { echo $row['COUNT(url)']; ?> Ahh got it working, however the count isn't displaying now! Isn't going to work, you would actually need to (in your query) Do something where you actually have COUNT(url). A bit of advice I was given, NEVER use SELECT *, ALWAYS List your tables. Link to comment Share on other sites More sharing options...
0 Domain Beans Posted January 14, 2011 Author Share Posted January 14, 2011 Thanks mate, will change that now. The query has got COUNT(url) in it, Am I missing something? Link to comment Share on other sites More sharing options...
0 Calculator Posted January 14, 2011 Share Posted January 14, 2011 I didn't test this, but you could try to change your query to: <?php $query = "SELECT url, COUNT(url) AS count_url FROM binfo_britinfo.yahooclicks WHERE datet BETWEEN '{$date_from_mysql}' AND '{$date_to_mysql}' GROUP BY url ORDER BY count_url DESC"; ?> and then use: <?php echo $row['count_url']; ?> instead. "COUNT(url)" is not included in the return set when using just "SELECT * FROM table", you need to explicitly add it to the list of fields to return. I'm not sure whether this field gets a proper name in the resulting $row array, therefore I gave it an alias to make sure it's there. As firey said, it's also a good practice to avoid * and list only the fields you actually need. ;) I'm not sure whether this will work as expected with those GROUP and ORDER clauses, but at least it's worth a try. According to this example it should work, but you never now. It may be interesting to test the query in something like phpMyAdmin to see whether it's returning the correct result set. That way, you can just test your query and not worry about possible PHP errors. Domain Beans 1 Share Link to comment Share on other sites More sharing options...
0 Domain Beans Posted January 14, 2011 Author Share Posted January 14, 2011 All the above problems are solved :) One last thing, if the dates are the same I've ran a quick query like the following: if ($date_frm_mysql == $date_to_mysql) { $query = "SELECT url, COUNT(url) FROM binfo_britinfo.yahooclicks WHERE datet = '".$date_from_mysql."' GROUP BY url ORDER By COUNT(url) DESC"; } else { $query = "SELECT url, COUNT(url) FROM binfo_britinfo.yahooclicks WHERE datet BETWEEN '".$date_from_mysql."' AND '".$date_to_mysql."' GROUP BY url ORDER By COUNT(url) DESC"; } I'm presuming the first query is not working as the datet = '".$date_from_mysql."' isn't exactly the same as the hours and minutes are all zeros, whats the best way to get around that? Link to comment Share on other sites More sharing options...
0 Domain Beans Posted January 14, 2011 Author Share Posted January 14, 2011 Done! if ($date_from_mysql == $date_to_mysql) { $date_from_mysql = date('Y-m-d H:i:s', $date_from); $date_to_mysql = date('Y-m-d 23:59:59', $date_to); echo $date_from_mysql; echo $date_to_mysql; $query = "SELECT url, COUNT(url) FROM binfo_britinfo.yahooclicks WHERE datet BETWEEN '".$date_from_mysql."' AND '".$date_to_mysql."' GROUP BY url ORDER By COUNT(url) DESC"; } else { $query = "SELECT url, COUNT(url) FROM binfo_britinfo.yahooclicks WHERE datet BETWEEN '".$date_from_mysql."' AND '".$date_to_mysql."' GROUP BY url ORDER By COUNT(url) DESC"; } Link to comment Share on other sites More sharing options...
0 Calculator Posted January 14, 2011 Share Posted January 14, 2011 A quick and dirty way to get around this is by setting the time on the $date_to to 23:59:59. That way, you don't need the extra query as the BETWEEN will work just fine for all date ranges. (Actually, without this modification you'd never get records on the last day in the selected range because of this time issue. Woops.) <?php $date_from = strtotime( $_POST['from'] . ' 00:00:00' ); $date_to = strtotime( $_POST['to'] . ' 23:59:59' ); ?> Or you could do it like this: <?php $date_from = strtotime( $_POST['from'] ); $date_to = strtotime( $_POST['to'] ); $date_to += 24*60*60 - 1; // Add a day minus one second ?> I prefer the second method because it's probably faster (simple arithmetic versus date string parsing), however the first method may be easier to read. Quick Edit: It looks like you solved it yourself, well done. Have a look at my second method, see if you like it more. (And move the $query assignment out of your if- and else-block, it's duplicate code! :p) Link to comment Share on other sites More sharing options...
Question
Domain Beans
I've got a database that lists off rows in a table (sql) one of the columns is a datetime stamp, whats the easiest way of coding a drop down box to show dates from -> to a certain date. Any help appreciated.
Link to comment
Share on other sites
12 answers to this question
Recommended Posts