44 posts
  • Bought between 100 and 499 items
  • Has been a member for 4-5 years
  • United Kingdom
cfoinfo says

Looking to pay for for a simple parameter driven crosstab generator in php that cross tabulates data across two mysql tables with a FK relation e.g

product down the side (ROW) location across the top (COL) SUM (e.g. amount) OR COUNT (e.g. matches) in the intersection (CELL)

So there’s a product table (for xtab row names) and a sales table with a product_id (FK) column (for the xtab join) and a location column (for the xtab columns – that may be a foreign key to a location table) and an amount column (for the xtab SUM )

If you’re familiar with crosstabs you’ll know what I mean…

812 posts
  • Bought between 10 and 49 items
  • Exclusive Author
  • Has been a member for 4-5 years
iamthwee says

I would just use navicat for mysql. You can download a demo version and join tables with drag and drop…

44 posts
  • Bought between 100 and 499 items
  • Has been a member for 4-5 years
  • United Kingdom
cfoinfo says

I think you’ve misunderstood what I’m looking for. I can already generate crosstabs in sql without the use of navicat or any other tool other than sql itself.

I’m looking for a php script that can generate and display crosstabs based on setting a handful of parameters in the code. Lots of report writers, Excel etc. can generate crosstabs directly from a database but I’m after a php script to do this so I can integrate it into a site without the need for a 3rd party tool, exporting to Excel etc.

812 posts
  • Bought between 10 and 49 items
  • Exclusive Author
  • Has been a member for 4-5 years
iamthwee says
http://rpbouman.blogspot.co.uk/2005/10/creating-crosstabs-in-mysql.html

^^ You mean something like that but coded so that it is dynamic?

812 posts
  • Bought between 10 and 49 items
  • Exclusive Author
  • Has been a member for 4-5 years
iamthwee says

And maybe this:

http://www.jasny.net/articles/creating-a-cross-tab-in-mysql/

Which in turn points to the open source:

http://mondrian.pentaho.com/

Although I’m not sure how useful this is as I’ve not tried it myself.

44 posts
  • Bought between 100 and 499 items
  • Has been a member for 4-5 years
  • United Kingdom
cfoinfo says

Thanks. As one of the links you provided says:

The big downside of this method is that you need to modify the query if a category is added. This can be solved though by dynamically creating the query in a PHP script.

That’s what I’m looking for…

Yes…Pentaho is one of the 3rd party open source report writers that do crosstabs. The paid for PHP Report Maker even generates the php code for you. I’m just looking for a php script specifically for crosstabs rather than a whole report writing tool.

812 posts
  • Bought between 10 and 49 items
  • Exclusive Author
  • Has been a member for 4-5 years
iamthwee says

I see… if I find anything I’ll be sure to let you know.

by
by
by
by
by
by