php sql help

Forums › IT Pro and developers › php sql help

Anibor

25 posts

Geek

#70149 19-Oct-2010 11:54

I want to display statistics from my database.

I have a quote request form on my page, each time a quote is requested it is saved to the database and given a timestamp yyyy-mm-dd-tt:tt:tt. I want to make select statements that show how many quotes were requested in different months

during the current month and the same month the previous year,
during the previous month and same month month the previous year

total quotes this current year and the total quotes the previous year

total quotes ever

Can someone please help me?

it is a mysql database and i am using php by the way

this is to be displayed in a table.

ScottStevensNZ

245 posts

Master Geek

#393561 19-Oct-2010 12:34

Hey - What does the table structure look like ?

Load & Performance Tester/PHP/JSP/C/PERL/MYSQL/LoadRunner8->11/HTML/CSS/XML/XSLT/2B|!2B/Cervelo Soloist/EMC Equip4/ Samsung Galaxy S /Darkys 10.2 Extreme

Do androids dream of electric sheep?
use strict;
my $sheepCount;

Yes, they can.

Anibor

25 posts

Geek

#393567 19-Oct-2010 12:48

ScottStevensNZ

245 posts

Master Geek

#393578 19-Oct-2010 13:08

Anibor:

Ok, What I would expect would be 3 tables:

Customer
CustomerCode(*)
...

Quote
CustomerCode(*)
ProductCode(*)
TimeStamp(*)
...

Product (or something like it)
Product(*)
...

Obviously your tables include other fields too... the (*) represents the primary key for each table
'Quote' has a composite key of 3 parts to ensure uniqueness - without knowing the data fully its just a guess of what it would likely be

so the sql would be something like (this probably isn't syntactically correct - but it should give you a clue)

select * from customer, quote where customerName="Name"
and quote.CustomerCode=Customer.CustomerCode
and product.productCode=quote.productCode
and quote.timestamp between xxxxx and yyyyy

Can't remember the exact syntax - hopefully the logic is reasonably correct - to sum i think it would be something like select count(*) from customer, quote where customerName="Name"
and quote.CustomerCode=Customer.CustomerCode
and product.productCode=quote.productCode
and quote.timestamp between xxxxx and yyyyy

ScottStevensNZ

245 posts

Master Geek

#393580 19-Oct-2010 13:09

Quick and dirty loigical model is below
https://cdn.geekzone.co.nz/imagessubs/blog4b5b18e48f92c6b45ab0fecf57effb90.jpg

Anibor

25 posts

Geek

#393586 19-Oct-2010 13:32

Um, what i'm doing only relates to this table, nothing to do with the customer connected to it, the idea is just to show how many quotes the company received per month (current and previous) in this year and the last year. Nothing to do with which customers requested that quote, that is all done in a different area of this site.

This is a hardcoded version of what i want the code to do.
$currentyear - output the current year ie 2010
$previousyear - output the previous year ie 2009
$thisMonth - will output the amount of quote that have been recieved online for the current month
$thisyear - will output the amount of quote that have been recieved online for the current month but the previous year
$previousMoth - will output the amount of quote that have been recieved online for the prevous month
PreviousYear - will output the amount of quote that have been recieved online for the previous month in the previous year
$thisYear (will be changed to $year) - will display all the quotes requested in this current year
$lastYear - will display all the quotes requested the previous year

All these variables should be able to be coded by jsut using select statements using the quote table and the timestamp but i dont know how to get the correct information out of the timestamp that i need.

ScottStevensNZ

245 posts

Master Geek

#393601 19-Oct-2010 14:04

Select count(*) from Quote where TimeStamp between val1 and val2
you would need to do it twice, once for current year, once for previous year

You will probably do a conversion in php to take its default date format to the format used in the DB - http://php-date.com/#mysql will show you.

BUT

I'm guessing though that the table in question is there to resolve a many to many relationship": many customers can request a quote for manyproducts/services, and many products and services can be requested by many customers?

If ypou run using the shown table only I'd say that dollars to donuts that you will return a result set for every product and service requested - and I would guess that a quote must contain atleast 1 product or service for it to be a quote. Therefore, you won't be returning the number of quotes, just the number of line items included within them.

Anibor

25 posts

Geek

#393605 19-Oct-2010 14:12

A "quote" is actually just an email its not an actually quote, in other words the only relationship is between the customer table and this one and its a one to many 1 customer can have many quotes but each quote can only belong to one customer.

I only want to return a number, nothing thats actually stored in the database

Equinox IT

Cloud spending continues to surge globally, but most organisations haven’t made the changes necessary to maximise the value and cost-efficiency benefits of their cloud investments. Download the whitepaper From Overspend to Advantage now.

ScottStevensNZ

245 posts

Master Geek

#393612 19-Oct-2010 14:29

Right, now I am with you. So the 'quote' table contains a generated quote number, a foreign key ref to the customer table, and the quote details are just a free text string.

So the tricky part is getting the date format correct. Convert the date in php then construct the query $query="select count(*) from quotes where TimeStamp between'". $lowerLimit . "' and '".$upperLimit."';"

Your timestamp from what I can remember is probably YYYY-MM-DD HH:MM:SS ?

You could also look at: SELECT count(*) formatted_date FROM quotes WHERE
formatted_date BETWEEN 'DD-MM-YYYY' and 'DD-MM-YYYY'; which would make it more readable.

Anibor

25 posts

Geek

#393615 19-Oct-2010 14:39

True, but how do i just rip the yyyy from that timestamp? (you were correct about the timestamp format). Or rip the mm from the timestamp

ScottStevensNZ

245 posts

Master Geek

#393619 19-Oct-2010 14:46

of the top of my head, the if you use select * where TimeStamp = YEAR(YYYY)

select * where TimeStamp between YEAR(YYYY) and YEAR(YYYY); ??

I'm going from memory :)

Anibor

25 posts

Geek

#393624 19-Oct-2010 15:01

Even if this doesnt work, THANK YOU for putting up with me!

ScottStevensNZ

245 posts

Master Geek

#393628 19-Oct-2010 15:02

No worries, I'm a load and performance tester so I *should* know the SQL off the top of my head. If only every DBMS used the same date format *sigh*

Antzzz

190 posts

Master Geek

#394163 20-Oct-2010 22:27

Ok, totals are easy (assuming quotes are not duplicated):

Total ever:

SELECT COUNT(Quote_Code)
FROM quotes

Total this year:

SELECT COUNT(Quote_Code)
FROM quotes
WHERE TimeStamp >= '2010-01-01'

Total last year

SELECT COUNT(Quote_Code)
FROM quotes
WHERE TimeStamp >= '2009-01-01'
AND TimeStamp < '2010-01-01'

Note you should build the date in quote marks above using whatever the php call is to get the current year and then build the yyyy-mm-dd strings from that.

Then to get the number for each month repeat the last query but build the limit date strings up by month.

Does that help? The COUNT() function makes this kind of thing fairly straightforward to do.