Re: [PHP] PHP querying mysql db for data limited to the last month
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 can't see a timestamp field in your code, but I'm assuming you've got one? SELECT *,SUM(timespent) FROM timetracking WHERE engineer=engineer AND month(timestamp)=month(date_sub(now(),interval 1 month)) GROUP BY product; /Per Jessen, Zürich -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP querying mysql db for data limited to the last month
Sorry, I took that stuff out because it was making the page not load and so
I figured it was wrong. I guess I should have posted the whole thing, but
since it didn't work I left it out.
Micah, thank you very much for the idea, it is working great!!!
Micah Gersten [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
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 brOne 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 ];
}
?
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] PHP querying mysql db for data limited to the last month
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 brOne 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 ];
}
?
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] 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 brOne 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 ];
}
?
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
