Curly one .. video database

Hi Larry,

Sounds like you're a programmer. :) Sometimes it can be a lot easier to have the data a little less normalized. Are the videos specific to a single record? Would it be possible to just add a field for them to the product and files section?

If not, the first step would be to figure out a query to list the tablenames and setup a list field with "Get options from MySQL Query (advanced)."

Next, you'd need another query to load the record numbers from a specified tablename. You can setup a second list field with "Get options from MySQL Query (advanced)." with this option set: Advanced Filter: Refresh list when this field changes: yourTableNameField

And then insert the tablename in the second query like this: <?php echo $ESCAPED_FILTER_VALUE ?>

When you select "Get options from MySQL Query (advanced)." it will show some advanced notes on how to do this. And it will updated field2 dynamically with ajax when field 1 changes and re-execute the mysql query for field 2 with the new value from field 1.

Hope that helps! Let me know how it goes.

Yah me is a coderbot

I like using 3NF where possible, though not manic about it as the old mainframe dayz.

One question, if I update the 'table' is that then available to use in the custom query? Will tinker with it later as I am still trying to get all the site level widgets working as well .. I turf'd all the damnable CMS programs I tried easier to just build the stuff using cmsB as the 'writer' and grab data and arrange as I like.

Cheers,
Larry

>if I update the 'table' is that then available to use in the custom query?

Yes, it's a new advanced feature we added a few versions ago for a custom job. You can change one field and then it submits that fields value and refreshes the 2nd fields query with that value. It works with list fields.

Hope that helps!

OK have setup a field that has the db name in it. Works and saves ok.

But when I try and do the select..
SELECT num, title FROM <?php echo $TABLE_PREFIX ?><?php echo $video_type ?>

The field name is not recognised, also what happens before I have selected a value from the previous list field :)

Ta
Larry

If you're running a recent version of CMS Builder you should see these fields below the query box:

Advanced Filter: Refresh list when this field changes: [______________]
Set this field to be your table pulldown

And then instead of $video_type use this value:
<?php echo $ESCAPED_FILTER_VALUE ?>

>What happens before I have selected a value from the previous list field?

You'd get an error because there would be no matching table. You could use a MySQL IF to deal with that situation.

Let me know how it goes. :)

Right..... sorry it didn't register that the value was the filter value .. oh my.

It seems to work .. will not really know until I go into add information mode for the content.

Thanks!
Larry

Still no luck. I have tried various IF's, BUTT's even tried MAYBE :)

The statement "SELECT num, title FROM cms_<?php echo $ESCAPED_FILTER_VALUE ?>" works fine if the filter has a value, the only problem in a new record the list field that the filter comes from hasn't got a default value option. I assume that would solve the problem as the new record could have a valid value dropped in from the start??????

Almost this works in phpMyAdmin but not in php
select num, title from <?php echo $TABLE_PREFIX ?><?php echo $ESCAPED_FILTER_VALUE ?>
where length( '<?php echo $ESCAPED_FILTER_VALUE ?>' ) > 0

Oh fun it is .. savvy!
Larry

Finally got through my thick skull you meant php IF not SQL.. hohum
<?php
if( strlen( $ESCAPED_FILTER_VALUE ) > 0 )
{
echo "select num, title from {$TABLE_PREFIX}{$ESCAPED_FILTER_VALUE}";
}
else
{
echo "select 0, 'Select Type'";
}
?>