Re: PHP querying mysql db for data limited to the last month

1. To get last months date, you can use strtotime("1 month ago") instead of mktime. 2. I don't see anywhere in the code where you are limiting by date. Try using > and <. Between is tricky on dates. Thank you, Micah Gersten onShore Networks Internal Developer http://www.onshore.com Vinny Gullotta wrote: > So I have this code I'm working with (pasted below) that queries a > mysql db table called timetracking. The goal of the page is to search > the db for all data based on a certain engineer, sorted by product and > it takes pre-defined values based on actions performed, sums them > based on product and display's the percentage of time an engineer has > spent on each product. Everything works great except I need to limit > the results to the last months data only, but everything I try seems > to just break it. Can anyone push me in the right direction a little? > I have tried using BETWEEN in the SELECT statement, some while > statements and if statements, and all I do is keep breaking it. If > anyone has any ideas, it would be exceptionally helpful. > > Thanks in advance, > Vinny > > > <?php > $total = 0; > $today = date('Y-m-d h:i:s'); > $monthago = date("Y-m-d h:i:s", mktime(date("h"), date("i"), > date("s"), date("m")-1, date("d"), date("Y"))); > echo "Today = ", $today; > echo "<br>One Month Ago = ", $monthago, "<br>"; > > $query = "SELECT *, SUM(timespent) FROM timetracking WHERE engineer = > '$engineer' GROUP BY product"; > $result = mysql_query($query) or die(mysql_error()); > $result2 = mysql_query($query) or die(mysql_error()); > echo "<center>"; > > while($row = mysql_fetch_array($result)){ > $total = $row['SUM(timespent)'] + $total; > } > while($row = mysql_fetch_array($result2)){ > $perc = $row['SUM(timespent)'] * 100 / $total; > echo "[ <font color=#1E429B size=+1>", $row[product]. " = ". > number_format($perc,2), "%</font> ]"; > } > > ?> > >