• 0

datetime question


Question

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

  • 0

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

  • 0

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.

  • Like 1
Link to comment
Share on other sites

  • 0

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

  • 0

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.

&lt;?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);
// ...
?&gt;

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

  • 0

Right, Added everyones codes seem to have a problem, its not displaying anything at the moment :).

&lt;?
function get_days($from, $to, $format = 'r'){
  $days = array();
  $from = strtotime($from);
  $to = strtotime($to);
  while($from &lt;= $to){
        $days[date('Y-m-d', $from)] = date($format, $from);
        $from = strtotime('+1 day', $from);
  }
  return $days;
}

?&gt;

    &lt;h2&gt;Yahoo Click Statistics&lt;/h2&gt;
    &lt;p&gt;Below are the yahoo click statistics that are currently stored in the database.&lt;/p&gt;

	&lt;form action="stats.php" method="post"&gt;
  From:&lt;select name="from" id="from"&gt;
        &lt;?php foreach(get_days('01-01-2011', '31-12-2011') as $short =&gt; $formatted): ?&gt;
        &lt;option value="&lt;?php echo $short; ?&gt;"&gt;&lt;?php echo $short; ?&gt;&lt;/option&gt;
        &lt;?php endforeach; ?&gt;
  &lt;/select&gt;

    To:&lt;select name="to" id="to"&gt;
        &lt;?php foreach(get_days('01-01-2011', '31-12-2011') as $short =&gt; $formatted): ?&gt;
        &lt;option value="&lt;?php echo $short; ?&gt;"&gt;&lt;?php echo $short; ?&gt;&lt;/option&gt;
        &lt;?php endforeach; ?&gt;
  &lt;/select&gt;
  &lt;input type="submit" name="mysubmit" value="Search" /&gt;
&lt;/form&gt;

	&lt;?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);
?&gt;
&lt;div&gt;
&lt;table border="1" style="font-size:12px;font-family:tahoma;"&gt;
&lt;tr&gt;
&lt;td&gt;&lt;b&gt;URL&lt;/b&gt;&lt;/td&gt;&lt;td&gt;&lt;b&gt;Yahoo Clicks&lt;/b&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;?

while ($row = mysql_fetch_array($result)) {
echo "&lt;tr&gt;";
echo "&lt;td&gt;";
echo $row['url'];
echo "&lt;/td&gt;";
echo "&lt;td&gt;";
echo $row['COUNT(url)'];
echo "&lt;/td&gt;";
echo "&lt;/tr&gt;";
}

echo "&lt;/table&gt;&lt;/div&gt;";

?&gt;

Ahh got it working, however the count isn't displaying now!

Link to comment
Share on other sites

  • 0

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)'];

?&gt;

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

  • 0

I didn't test this, but you could try to change your query to:

&lt;?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";
?&gt;

and then use:

&lt;?php
echo $row['count_url'];
?&gt;

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.

  • Like 1
Link to comment
Share on other sites

  • 0

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

  • 0

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

  • 0

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.)

&lt;?php
$date_from = strtotime( $_POST['from'] . ' 00:00:00' );
$date_to = strtotime( $_POST['to'] . ' 23:59:59' );
?&gt;

Or you could do it like this:

&lt;?php
$date_from = strtotime( $_POST['from'] );
$date_to = strtotime( $_POST['to'] );
$date_to += 24*60*60 - 1; // Add a day minus one second
?&gt;

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

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.