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,

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