Creating an advanced list based on the table in another field

5 posts by 2 authors in: Forums > CMS Builder
Last Post: September 1, 2020   (RSS)

Nevermind - I found the ESCAPED_FILTER_VALUE and it works great!

So, now my query is:

SELECT num, title
FROM `<?php echo $TABLE_PREFIX ?><?php echo $ESCAPED_FILTER_VALUE ?>_images`

I'm selecting the "Gallery" in one field and then selecting the num/title for an entry in the same named section + _images.  For example, if the gallery is slideshow_gallery, the images are slideshow_gallery_images.

Gary.

I spoke too soon - this is a problem when there is no value in the $ESCAPED_FILTER_VALUE field.

So, I guess I not only need to create a list based on this value, but also need to know if the field has a value.

Gary.

By Carl - September 1, 2020

If I understand correctly, you want two dropdown boxes. The first dropdown box will contain all your tables, and the other will contain the rows from the table you selected.

I got a demo working locally. As long as you know the table you're selecting will have the two fields you wanted, "num, title", you should get a working dropdown or you will get a mysql error when selecting that table.

The solution I came up with is this,

I created a field with type "list". The field label and name can be whatever you want. I selected "pulldown" for Display As. List Options was the advanced MySQL Query option.

I used your same query for displaying the table names.

SELECT REPLACE(TABLE_NAME, '<?php echo $TABLE_PREFIX;?>', '') FROM information_schema.`TABLES` where TABLE_SCHEMA = '<?php echo $GLOBALS['SETTINGS']['mysql']['database'];?>' AND TABLE_NAME like '<?php echo $TABLE_PREFIX;?>_%' ORDER BY TABLE_NAME

For Input Validation, I selected the checkbox for "User may not leave field blank".

The second field I created was also of type "list" and it was a pulldown. Using the advanced list options query, I used the following code

<?php
if($ESCAPED_FILTER_VALUE): ?>
SELECT num, title FROM <?php echo $TABLE_PREFIX.$ESCAPED_FILTER_VALUE; ?>
<?php
else:
?>
SELECT "Please select value in field above...";
<?php
endif;
?>

In the Advanced Filter dropdown, I selected the first field so it refreshes upon the first dropdown list being changed. Let me know if this make sense or if you have any other questions.

Carl

PHP Programmer

interactivetools.com

By garyhoffmann - September 1, 2020 - edited: September 1, 2020

That's awesome and does work.  Thank you!  I was not aware that basically you could put any PHP you wanted in that area.  That makes life a lot easier.