?
Solved

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

Posted on 2012-03-28
10
Medium Priority
?
376 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 750 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 111

Accepted Solution

by:
Ray Paseur earned 750 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

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

In threads here at EE, each comment has a unique Identifier (ID). It is easy to get the full path for an ID via the right-click context menu. However, we often want to post a short link within a thread rather than the full link. This article shows a…
The Windows functions GetTickCount and timeGetTime retrieve the number of milliseconds since the system was started. However, the value is stored in a DWORD, which means that it wraps around to zero every 49.7 days. This article shows how to solve t…
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
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 …
Suggested Courses

621 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