Report Builder Plugin Help [Solved]

Hi Paul,

The easiest solution might be to use PHP to loop through the tables, I've created some code that could be used below:

  //Array for storing results
  $completeArray = array();
  //lists of tables to search 
  $mainTablesList = array('blog', 'news', 'main');

  //Loop through tables
  foreach($mainTablesList as $table){
    //get contents from table using the CMSB mysql_select function
    $tableContents = mysql_select($table.'_main');
    //loop through tables contents if any results
    if($tableContents){
      foreach($tableContents as $content){
        //add sachs_dealer value to the array
        $completeArray[] = $content['sachs_dealer'];
      }
    }
  }
  showme($completeArray);

This is just an example, so you'll need to edit the code to work with your site. You'll need to add the table names that you want to search through into the $mainTablesList array without '_main' on the end of them

The code works by first looping through each table and getting its contents. Then looping through each field and storing the value for sach_dealer in the $completeArray.

Let me know if this isn't what you need.

Thanks!

Greg

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 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