Solved

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

Posted on 2012-03-28
10
364 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
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.

 
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 109

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
 

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

Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
This article discusses how to create an extensible mechanism for linked drop downs.
Learn the basics of if, else, and elif statements in Python 2.7. Use "if" statements to test a specified condition.: The structure of an if statement is as follows: (CODE) Use "else" statements to allow the execution of an alternative, if the …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

777 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