Curly one .. video database

10 posts by 2 authors in: Forums > CMS Builder
Last Post: February 26, 2009   (RSS)

By jenolan - February 22, 2009

Now I have been a noobie for a while but I am managing to start doing some neat stuff, to me not having to write/manage the content creation part of the system and use CMSBuilder is sweet .. oh yeah.

Now what I have is a number of areas, eg products & files will do for the example.

So I have a table for each which are totally different structure no relationship between them, now add my third one 'videos' inside here is information relating to video tutorials for products and files.

So I have two fields in the video DB one is 'Video Type' and the other is 'Video Type ID'. The Type points to the table and the id points to the record.

Now what I would like to do is this.... In the Type I would like the table names, I do this with a manual drop down at the moment so I can live with that... but depending on the Type I would like the drop down to contain the list from the relevant table.

At the mo I just go and look [cool] and insert the number, but if I can have it automagick it would be very nice.

Thanks,
Larry
---
<?= "Jenolan(Larry) :: Coding Since 1973" ?>
Peace and Long Life

Re: [jenolan] Curly one .. video database

By Dave - February 23, 2009

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.
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] Curly one .. video database

By jenolan - February 23, 2009

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
---
<?= "Jenolan(Larry) :: Coding Since 1973" ?>
Peace and Long Life

Re: [jenolan] Curly one .. video database

By Dave - February 24, 2009

>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!
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] Curly one .. video database

By jenolan - February 24, 2009

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
---
<?= "Jenolan(Larry) :: Coding Since 1973" ?>
Peace and Long Life

Re: [jenolan] Curly one .. video database

By Dave - February 25, 2009

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. :)
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] Curly one .. video database

By jenolan - February 25, 2009

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
---
<?= "Jenolan(Larry) :: Coding Since 1973" ?>
Peace and Long Life

Re: [jenolan] Curly one .. video database

By jenolan - February 25, 2009 - edited: February 25, 2009

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
---
<?= "Jenolan(Larry) :: Coding Since 1973" ?>
Peace and Long Life

Re: [jenolan] Curly one .. video database

By jenolan - February 25, 2009

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'";
}
?>

---
<?= "Jenolan(Larry) :: Coding Since 1973" ?>
Peace and Long Life