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

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.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ahmed MerghaniSoftware EngineerCommented:

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

Ahmed MerghaniSoftware EngineerCommented:

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

mropenmindAuthor Commented:
Could you please provide me with the statement based on the picture I've posted.
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Ahmed MerghaniSoftware EngineerCommented:
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

Ray PaseurCommented:
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' ";

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mropenmindAuthor Commented:
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.
mropenmindAuthor Commented:
New picture of the DB:

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.
mropenmindAuthor Commented:
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.
mropenmindAuthor Commented:
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.
Ahmed MerghaniSoftware EngineerCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.