Golly Gosh, here we go again.
On Fri, 2011-09-16 at 11:10 -0700, John R Pierce wrote:
On 09/15/11 12:51 PM, Always Learning wrote:
I would not design my orders database exactly like you have.
once again, you completely miss the point, and go off on a tangent explaining how YOU do things, justifying your ignorance.
The point is I write programmes for pleasure. Naturally as an always learning individual, I appraise others' methods and when they seem advantageous and relevant I am likely to use them.
Ignorance, by the way, includes the inability of some to understand the perspective of others!
ignoring the fact that I contrived my example in a few minutes, and it was not intended to be a realistic complete implementation of a order processing system, that same set of normalized tables could be used to generate a result like...
When IBM introduced its Structured Query Language many years ago, it seemed like a helpful facility for end-users who could, using SQL, access they own data fairly quickly rather than wait several weeks for a purposely written computer programme.
You write SQL more like an end-user. I use SQL like a computer programmer and via a programming language called PHP.
I do not propose to use your SQL techniques. I prefer my own.
select c.name, c.address, sum(ci.price*oi.qty) from customers c join customerorders co on (co.customer=c.id) join orderlineitem oi on (co.id=oi.catalogid) join catalogitem cati on (cati.id=oi.catalogid) group by c.id;
which would output a list of all customers with their name, address, and total of their sales.
select cati.description, sum(oi.qty) from orderlineitem oi join catalogitem cati on (cati.id=oi.catalogid) group by cati.id order by sum(oi.qty) desc;
would output a list of all catalog items and the total quantity. a slightly more involved query could summarize this by month or by year by customer, etc etc.
How does a PHP programmer obtain the output, from your examples, in a variable ?