[SOLVED] Complex arrays and conditional loops

[MichaelGallagher]

Hi guys,

 

First of all:

 

I'm using XAMPP LITE (latest version) on Windows XP for development (or 2003 server for live)

All basic php settings, nothing flash.

 

This is really a generalized question anwyay..

 

Basically I have the following problem (I have simplified the data for ease of understanding):

 

I have a ONE-TO-MANY relationship with --> a table containing 5,000 customers and another table containing 10,000 sales relating to these customers.

So each customer may have one or multiple sales records.

 

for example (sales table) sorted by PurchaseDate which isnt shown:

 

 

CustID

ProductID

 

1

200

 

1

200

 

1

200

 

1

300

 

1

400

 

1

700

 

2

200

 

2

300

 

2

300

 

3

300

 

 

 

Now, I need to consolidate these sales records into a single SALE record for each customer - each sale record would be a copy of the LAST record (based on most recent purchase date already sorted) with two additions...

 

The record that is created to represent all sales must contain a COUNT of all sales, and a consolidation of the ProductID.

 

The outcome looking something like this:

 

CustID TotalPurchases  Products

16200, 200, 200, 300, 400, 700

23200, 300, 300

31300

 

I have tried several different ways of performing this, and all have failed.  ???I know this is not a forum for asking for CODE to be delivered, although of course that would be appreciated, since I learn well from breaking the code down and implementing it myself.

 

I am just looking for someone to discuss with me the PSEUDOCODE or possibly the LOGICAL STEPS that are required.

 

I am not a beginner in the world of programming, however PHP is relatively new to me and I am learning as I go.

 

What makes it worse is that I am in Australia and it isn't entirely possible for me to converse freely with everyone, since i have to wait hours and hours for response, etc. PLUS I am leaving for overseas soon, so time is running short.

 

Like I said, I don't expect someone to reply with a full blown script, but please don't be too ambiguous; I am here to learn, but i have a time limit.

 

In all seriousness I really appreciate any help you can provide.

 

Regards,

 

Michael Gallagher

[btherl]

Hi Michael,

 

Mysql has a nice kludgy aggregate operator for situations such as this, which may simplify your job.

 

SELECT CustID, count(*) as TotalPurchases, GROUP_CONCAT(ProductID) AS Products FROM Customers JOIN Sales ON ... GROUP BY CustID

 

After that you can parse the concatenated products and off you go.

 

Alternately you can select the whole lot as rows and group them in php, but that is a pain (though quite possible, and I've taken this approach many times, when aggregates aren't powerful enough for what I need)

[btherl]

Here you go.. the documentation

[MichaelGallagher]

Brian, you are a legend!

 

Your solution works perfectly, with some minor modification...

 

"SELECT *, count(*) as TotalServices, GROUP_CONCAT(w.Mobile) AS Mobiles FROM Customers c JOIN Working w ON c.ClickPosID = w.ClickPosID GROUP BY w.ClickPosID;"

 

I have one question though -- each sale has a sales representative, the original sales person, and when i output the results set it gives me the sales rep for the first sale only. That is, if there are 15 sales, it outputs the SalesRep for sale number 1. Is there any way I can retrieve the most RECENT SalesRep (the last record equal to the count(*) ) but ONLY this value.. I do have a SaleDate field that can be used to determine the latest sale, yet I assume using the value of the count is more appropriate.

 

Thanks for the guidance, GROUP_CONCAT() is a great function..

 

Regards,

 

Michael Gallagher

[btherl]

Oh dear.. "most recent" is a tough one.  I think you can do it like this (not tested but it makes sense to me)

 

[/code]SELECT .... , sales_joined.SalesRep as most_recent_sales_rep

FROM (SELECT *, count(*) as TotalServices, GROUP_CONCAT(w.Mobile) AS Mobiles, max(date_of_sale) as most_recent_sale

  FROM Customers c

  JOIN Working w ON c.ClickPosID = w.ClickPosID

  GROUP BY w.ClickPosID) AS subq

JOIN sales AS sales_joined ON (subq.date_of_sale = sales_joined.date_of_sale);[/code]

 

In that "..." you really should be listing the columns you want.  Using "*" is a habit I gave up long ago, because it makes things more confusing in complex queries.  Particularly when you self-join like this, as you need to specify which copy of the sales table columns you want.

 

Also be warned that mysql does not guarantee which entry you will get when you don't specify an aggregate like max or min.  It may not always be the first one.  That's just luck, because the data is stored in that order on disk.