Re^3: Help with MySQL SELECT into multidimensional array

[runrig]

I would consider making views out of the other queries (then the main query and the joins are simple), but I would also probably change the queries. The usual thing to do is to include everything but the aggregate in the GROUP BY clause, but I'm not sure what MySQL does when you don't do that and I don't know, e.g., what the keys of the tables are or if you have duplicate client name's with multiple client id's and how you want to handle that, etc.

[btongeorge]

Very interested in your comments re. changing the queries. Haven't ever used views in MySQL so will look into that. There are no duplicate clients in the table and there won't be, the client ID is the primary key.

How do you suggest that I modify the queries? Not sure I fully understand the implications of your comments.

[runrig]

Something along the lines of this...first I'd combine your conference size queries into one view:

-- Or maybe conf_size_counts ??
CREATE VIEW conf_sizes AS
SELECT
  c2.id,
  sum(if(value<11,1,0)) AS "conf10",
  sum(if(value<20 and value>10,1,0)) AS "conf20",
  sum(if(value>20,1,0)) AS "conf30"
FROM extension ext, client c1, client c2, extension_prefs exp
WHERE ext.client_id = c1.id
  AND exp.param="conf_size"
  AND ext.type="conference"
  AND c1.parent_client_id = c2.id
  AND exp.extension_id = ext.id
GROUP BY c2.id;

Then modify your main query as:

SELECT c.id, c.name, cs.conf10, cs.conf20, cs.conf30
FROM client c LEFT OUTER JOIN conf_sizes cs ON c.id = cs.id
WHERE c.level = 50
  AND c.status = 1

The rest of the views and joins I leave for you...

[Marshall]

I suggest reading the SQL book that I recommended in another part of this thread. It will cover "views". Your application is "read only" so something like that is completely appropriate.

I am no SQL guru, but SQL has several types of table joins where 2 or more tables can be combined into a single "view" which is like a temporary table with the columns of both tables, but read only. On all tricky SQL questions, I defer to wiser Monks than myself.

However, if you can do the SQL, it can make the Perl code easier because you could just get all the results at once.

There is sometimes a tradeoff between Perl voodoo and SQL voodoo. I had one SQL query that I worked on very hard and it worked, but it took 15 minutes. Then I "simplified it" into 1,500 SQL queries and it took only 15 seconds. But I used some serious "Perl-kung-foo" on it!

From what I understand so far, your app is not performance or memory limited. Find some combination of stuff that works for you! Any of the SQL or Perl code that we've discussed so far will be just fine.

[btongeorge]

I did some more reading around this, my SQL-fu is way more advanced than my Perl-fu, so I went about it with views, which worked great. Now I have a very simple query which outputs everything that I need, literally as simple as:

SELECT * from view;

So now my quest becomes to work out how to spit out an HTML table from my Perl array.

This is a great step forwards on my path to enlightenment, and I thank everyone for their help so far!