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)

By garyhoffmann - August 28, 2020

I sincerely apologize if this has been asked/answered already...

I have two fields - the first field I would like to pick a table to use, the second field would be an entry in that table.

For the first table, I have the following list code in my field definition:

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 my second field, I would like to show the title field from table and capture the "num" field.  For example, I have the following so far:

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

What I'm not sure how to do is to replace the value "tableName" with the value of the previous field.  Ultimately, I want this to be:

SELECT num, title FROM <table selected in the previous field>

Is that possible?

Thank you in advance.

Gary.

By garyhoffmann - August 28, 2020

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.

By garyhoffmann - August 28, 2020

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