Solved

Collect and display data for dates needed using mysql database as a source.

Posted on 2012-03-28
10
363 Views
Last Modified: 2012-06-27
Hi there, I would like to know, how to collect the data for past 2 weeks from MYSQL database.

Basically the output should be

2 Decision(s) were made on 03-29-12
1 Decision(s) were made on 03-28-12
3 Decision(s) were made on 03-27-12

and so on..

Data in mysql database(Username, UserID, Date, DataType, Decision)

demouser demoid 03-29-12 03:52	datatype	decision
demouser demoid 03-29-12 17:07	datatype	decision
demouser demoid 03-28-12 17:07	datatype	decision
demouser demoid 03-27-12 17:07	datatype	decision
demouser demoid 03-27-12 17:07	datatype	decision
demouser demoid 03-27-12 17:07	datatype	decision

Open in new window


Thank you.
0
Comment
Question by:mropenmind
  • 5
  • 4
10 Comments
 
LVL 8

Expert Comment

by:Ahmed Merghani
ID: 37780435
Hello,

You can create temporary view and format your date as you want and then count the date from the view as this:

create OR REPLACE view view1 as (select date(date_time) from table1)

Open in new window



Select count(*) from view1

Open in new window

0
 
LVL 8

Assisted Solution

by:Ahmed Merghani
Ahmed Merghani earned 250 total points
ID: 37780443
Sorry,

This is the correct one:

create OR REPLACE view view1 as (select date(date_time) the_date from table1)

Open in new window


select count(*) from view1 group by the_date

Open in new window

0
 

Author Comment

by:mropenmind
ID: 37780732
Could you please provide me with the statement based on the picture I've posted.
Untitled.png
0
 
LVL 8

Expert Comment

by:Ahmed Merghani
ID: 37780805
Here is the code:

create OR REPLACE view gmtc_tickets_view as (select date(ticket_date) the_date from gmtc_tickets)

Open in new window


select count(*), the_date from gmtc_tickets_view group by the_date

Open in new window

0
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 250 total points
ID: 37781186
Suggest you don't use "DATE" for a column name -- reserved words may cause confusion when they are used for other purposes.

I think fishboy2000sd is on the right track.  If you wanted to limit the query to the last two weeks you might use something like this...

$new = date('c', strtotime('Now'));
$old = date('c', strtotime('Today - 2 weeks'));
$sql = "SELECT ... WHERE ticket_date BETWEEN '$old' AND '$new' ";
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Author Comment

by:mropenmind
ID: 37783524
I couldn't manage to get your method to work, fishboy2000sd and Ray, basically what I need is to output separately how much a gm did every single day.
0
 

Author Comment

by:mropenmind
ID: 37783602
New picture of the DB:

http://i.imgur.com/DJzLo.jpg

Basically when user is logged in, I need to print the text with how many tickets every day logged in person did for the past month.
0
 

Author Comment

by:mropenmind
ID: 37786362
select count(`id`),ticket_date from gmtc_tickets WHERE ticket_date BETWEEN '2012-03-01 00:01:00' AND '2012-03-31 23:59:59' AND gmtc_gm='rejanu' group by date(`ticket_date`) ORDER BY ticket_date DESC

is the answer.
0
 

Author Comment

by:mropenmind
ID: 37786476
I've requested that this question be closed as follows:

Accepted answer: 0 points for mropenmind's comment #37786362

for the following reason:

I found the solution myself.
0
 
LVL 8

Expert Comment

by:Ahmed Merghani
ID: 37786477
May be the author found the solution him self but I think fishboy2000sd and/or Ray assist him to solve it, so I think the author must grant assisted solution at least.
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
These days socially coordinated efforts have turned into a critical requirement for enterprises.
Learn the basics of modules and packages in Python. Every Python file is a module, ending in the suffix: .py: Modules are a collection of functions and variables.: Packages are a collection of modules.: Module functions and variables are accessed us…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now