4 posts by 3 authors in: Forums > CMS Builder: Plugins & Add-ons
Last Post: July 4   (RSS)

By andreasml - June 27 - edited: June 28

Hello,

I am using the Report Builder plugin.

I have made a report which turns out the values of "surname" and "name" fields from the section "aortic_aneurysm_disease", where the "hospital" field will be "Athens Hospital". I have written the following querry and it works fine. 

SELECT
    surname, name
FROM `<?php echo $TABLE_PREFIX ?>aortic_aneurysm_disease`
WHERE hospital='Athens Hospital'
LIMIT 1000

However, when I need to search for the same fields ("surname" and "name") from another hospital, I have to create a new report. 

Question: is there a way to have a single report in which users will insert the value they want for the field "hospital" (in a text-field), or even choose from a list-box with pre-specified values for the specific field "hospital", and the report will give these fields ("surname" and "name") from this specific hospital?

Kind regards,

Andreas Lazaris

Hi Andreas (and Tim), 

There's no built-in search functionality in Report Builder but you can use PHP code in the queries.  So with this query: 

SELECT surname, name
FROM `<?php echo $TABLE_PREFIX ?>aortic_aneurysm_disease`
WHERE hospital='Athens Hospital'
LIMIT 1000

You could do this (untested code) to get there WHERE value from the request: 

SELECT surname, name, hospital
FROM `<?php echo $TABLE_PREFIX ?>aortic_aneurysm_disease`
<?php if (isset($_REQUEST['hospital'])) { echo mysql_escapef(" WHERE hospital = ? ", $hospital); } ?>
LIMIT 1000

And then you could specify the hospital in the url like this: &hospital=Athens+Hospital

You'd still need to modify the plugin or add some custom code or links to generate those links though.

Hope that helps!

Dave Edis - Senior Developer
interactivetools.com

And just noticed, you can also use HTML and PHP in the Description field.  So you could have a list of hospital links there or try adding a pulldown.  

Dave Edis - Senior Developer
interactivetools.com