Report Builder

8 posts by 2 authors in: Forums > CMS Builder
Last Post: October 25, 2013   (RSS)

By gadefgaertgqe - October 22, 2013 - edited: October 24, 2013

Hi,

I have a series of tables and need to find tables that end with '_main' and display the table name + the contents of a field called  'phone' which is in the _main tables.

I've tried all sorts today but with little luck.

I am able to find only tables with a field called 'phone' but I cannot output the field value.

Here is my code so far:

<?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 "Phone Numbers" FROM `<?php echo $TABLE_PREFIX;?><?php echo $tableName;?>`
WHERE phone

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

Any and all pointers in the right direction welcome!

Thanks.

By gadefgaertgqe - October 24, 2013

Anyone?

By gregThomas - October 24, 2013

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

Greg Thomas







PHP Programmer - interactivetools.com

By gregThomas - October 24, 2013

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

Greg Thomas







PHP Programmer - interactivetools.com

By gadefgaertgqe - October 24, 2013

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!

By gregThomas - October 24, 2013

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

Greg Thomas







PHP Programmer - interactivetools.com

By gadefgaertgqe - October 25, 2013

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.