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