Report Builder Plugin Help [Solved]

Hi Greg,

I am working inside the Report Builder Plugin for CMSB. Your suggestion is not compatible with the plugin but thank you anyway :)

I have researched this and I am wondering if what I want to do is even possible. Maybe your way is the only way Greg.

I know how to find all tables ending with _main: 

select * from Information_schema.tables where table_name like '%_main'

I can also search a specific table and get MySQL to only show is sachs_dealer = true;

select * from bobs_bikes_main where sachs_dealer = 1;


But I can't seem to be able to search for dealers ending in _main AND where the field sachs_dealer = 1

So the output I am looking for is:

Dealer Main         |     Sachs Dealer

Bobs Bikes          |     True

Mean Machines  |     True

Does anyone have any suggestions?

Hi,

We've done some more research and it is possible to link tables in the way you want, I think this might be the solution:

select a.sachs_dealer as Dealer, 'Bobs Bikes' as DealerMain from bobs_bikes_main a
   WHERE sachs_dealer = 1
UNION
select b.sachs_dealer as Dealer, 'Mean Machines' as DealerMain from mean_machines b
   WHERE sachs_dealer = 1

This should return a list with two columns, the first showing the sachs_dealer value from each table under a column called Dealer, and the second displaying a custom string (eg, bobs bikes, or mean machines) which contains the table name. 

You will have to modify the code slightly to work with your table names and variables. Also this code does assume that you know the table names before you carry out the query. 

Let me know if you have any questions.

Thanks!

Greg

Hi Greg,

I really appreciate the effort. However I must be explaining this all wrong, so I'll try again :)

I have an unusual setup for CMSB (for reasons which are far too long to go into here), but it is the only way it can work they way we need it. Here is the setup.

We have Over 100 'dealers'. The number of dealers changes from week to week. Each dealer has their own schema files. For example:

bobs_bikes_main
mean_machines_main
crusier_world_main

....and so on x 100.

There are also many other schema files of different types in our CMSB install.

Some dealers will only sell certain brands of bikes. In this particular example I am looking to list all dealers who are Sachs Dealers.
They are identified by a check box field called "sachs_dealer" inside the _main schema.

So I need to:
Automatically search through all schema that end with "_main" and show which of them has the check box field called "sachs_dealer" checked.

I hope this helps to clairify, and I thank you for your patience!

Paul

Hi Paul,

Thanks for the details, we think we have a solution that should look for all the tables that end in _main, and display them if the check box field sachs_dealer is ticked:

<?php $firstTable = true;?>
<?php foreach (getSchemaTables() as $tableName) :?>
<?php if (strpos($tableName, "_main")): ?>
<?php if ($firstTable) {
$firstTable = false;
}
else {
echo "UNION";
}
?>

SELECT "<?php echo $tableName;?>" as "Sachs Dealer Selected" FROM `<?php echo $TABLE_PREFIX;?><?php echo $tableName;?>`
WHERE sachs_dealer = '1'

<?php endif ?>
<?php endforeach ?>

So this code basically recreates the previous MySQL code, but creates it dynamically using PHP, and only creates a statement for tables that end in _main.

So the code gets the current tables from the getSchemaTables function, then loop through them checking if they end in _main, if it does, then create the select statement for that table. 

This code does assume that every table that ends in _main has a field called sachs_dealer. 

Let me know if you have any questions. 

Thanks!

Greg

That did it Greg! I had no idea that PHP could be used to that extent in the report builder.

Is there a way to add a row number to the result to be able to quickly see how many there are?

Many thanks for your time and patience on this, and sorry for explaining it so bad initially ;)

Paul