Solved

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

Posted on 2012-03-28
10
362 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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

This article discusses four methods for overlaying images in a container on a web page
This article discusses how to create an extensible mechanism for linked drop downs.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
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 …

707 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

17 Experts available now in Live!

Get 1:1 Help Now