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.
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
PHP Programmer - interactivetools.com
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
PHP Programmer - interactivetools.com
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
PHP Programmer - interactivetools.com
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.