Report Builder

Hi 8bitPixel,

I think something like this should work:

<?php $firstTable = true;?>
<?php foreach (getSchemaTables() as $tableName) :?>
  <?php if(strpos($tableName, "_main")): ?>
    <?php $schema = loadSchema($table); ?>
    <?php if(@$schema['phone']): ?>
      <?php if($firstTable) {
        $firstTable = false;
        }
        else {
        echo "UNION";
      }
      ?>

      SELECT "<?php echo $tableName;?>" as "Phone Numbers" FROM `<?php echo $TABLE_PREFIX;?><?php echo $tableName;?>`
    <?php endif; ?>
  <?php endif ?>
<?php endforeach ?>


The changes I've made are:

• Changing the field that is retrieved to 'phone' and not the table name.
• Used loadSchema to retrieve all of the fields for each section, then used it to check if there is a phone field.
• Removed the where statement, as we want to retrieve all records.  

Let me know if you have any questions.

Thanks!

Greg

Thanks Greg,

I think I see what you are doing. However I get the followig error:

Notice: Undefined variable: table in /homepages/16/d311219399/htdocs/national/cms-hub/lib/common.php(466) : eval()'d code on line 4 loadSchema: no tableName specified!

Sorry, forgot to change a variable name after I'd finished testing:

<?php $firstTable = true;?>
<?php foreach (getSchemaTables() as $tableName) :?>
  <?php if(strpos($tableName, "_main")): ?>
    <?php $schema = loadSchema($tableName); ?>
    <?php if(@$schema['phone']): ?>
      <?php if($firstTable) {
        $firstTable = false;
        }
        else {
        echo "UNION";
      }
      ?>

      SELECT "<?php echo $tableName;?>" as "Phone Numbers" FROM `<?php echo $TABLE_PREFIX;?><?php echo $tableName;?>`
    <?php endif; ?>
  <?php endif ?>
<?php endforeach ?>

You should just need to change the $table variable to $tableName.

Thanks!

Greg

Hi, Greg. I tried that before I posted but it just gives me the names of the tables it finds and not the actual numbers, so I assumed I had got it wrong.

Just checked again and the same results.

Thanks for the help so far!

Sorry! I thought you were trying to get the table names with phone number fields, you should be able to get the content of the phone number field like this:

<?php $firstTable = true;?>
<?php foreach (getSchemaTables() as $tableName) :?>
  <?php if(strpos($tableName, "_main")): ?>
    <?php $schema = loadSchema($tableName); ?>
    <?php if(@$schema['phone']): ?>
      <?php if($firstTable) {
        $firstTable = false;
        }
        else {
        echo "UNION";
      }
      ?>

      SELECT `phone` as "Phone Numbers" FROM `<?php echo $TABLE_PREFIX;?><?php echo $tableName;?>`
    <?php endif; ?>
  <?php endif ?>
<?php endforeach ?>

So I've updated the code to select the phone number field from each table.

Let me know if the code doesn't work.

Thanks!

Greg

Hi Greg,

No problem! All working, thanks for all the help. I'm off to look at it closer now to try and understand it fully.