@SELECT drop down lists and NULL

60 Posts

MarkTWIC Reply #1, 17 years, 2 months ago

Short and sweet query.

I have an @SELECT working wonderfully producing a drop down list menu from a database table I have. Except I want the option of NULL (first on the list) when the article doesn’t match anything in the datbase I’m querying.

How do I do that?

Mark Crowther

60 Posts

MarkTWIC Reply #2, 17 years, 2 months ago

If anyone has managed to get a NULL column with these drop downs I’d really like to know how. I can’t believe it isn’t a problem that hasn’t come up before and it is going to be a royal pain for me if it is impossible.

5,888 Posts

Bruno17 Reply #3, 17 years, 2 months ago

instead of @select you can also use something like that for example (all in one line):

@EVAL $rs=$modx->db->select('id,meldeverein',$modx->getFullTablename('regatta_meldungen'));$rows=$modx->db->makeArray($rs);$arr_out=array('null==0');foreach($rows as $row){ $arr_out[]=$row['meldeverein'].'=='.$row['id'];}return implode('||',$arr_out);

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

24,544 Posts

BobRay Reply #4, 17 years, 2 months ago

I’m not exactly clear on what you want, but this might get you close. This code is in the file: core/model/modx/modtemplatevar.class.php:

case 'SELECT': /* selects a record from the cms database */
                    $dbtags['DBASE'] = $this->xpdo->db->config['dbase'];
                    $dbtags['PREFIX'] = $this->xpdo->db->config['table_prefix'];
                    foreach($dbtags as $key => $pValue)
                        $param = str_replace('[[+'.$key.']]', $pValue, $param);
                    $rs = $this->xpdo->db->query('SELECT '.$param);
                    $output = $rs;
                    break;

If you duplicate it just below the code above and change the case statement (first line) and the sixth line, you’ll have this:

case 'NULLSELECT': /* selects a record from the cms database */
                    $dbtags['DBASE'] = $this->xpdo->db->config['dbase'];
                    $dbtags['PREFIX'] = $this->xpdo->db->config['table_prefix'];
                    foreach($dbtags as $key => $pValue)
                        $param = str_replace('[[+'.$key.']]', $pValue, $param);
                    $rs = 'NULL||' . $this->xpdo->db->query('SELECT '.$param);
                    $output = $rs;
                    break;

This is all untested but, after that, putting @NULLSELECT etc. in the TV should give you the NULL top choice.

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting

60 Posts

MarkTWIC Reply #5, 17 years, 2 months ago

Quote from: BobRay at Aug 24, 2009, 06:55 PM

I’m not exactly clear on what you want, but this might get you close. This code is in the file: core/model/modx/modtemplatevar.class.php:

At first sight this is exactly what I want many thanks.

I’m not sure why it wasn’t clear 8-) unless I’m misunderstanding the process here. I would have thought it a very frequent use of a dropdown list that it contains information that actually may not apply to the article in question. I’m assuming that all this dropdown does is produce a set of values which are then stored in the template variable.

I have a website called the Week in Chess. A site sorely in need of a CMS. Putting this together is taking forever and I keep thinking it is like doing a million piece jigsaw. Anyhow I am finally making serious progress. I have individual tournaments I want to cover and I’m glueing the whole thing together with an individual identifier for that tournament contained in a template variable.

This way I can have sections for individual writers on each event and can bring up the material using a search with ditto, this actually works better than I ever could have dreamed.

About 2 months ago I realised that it didn’t make sense to put everything in Modx. The game files (records of the games and results and a calendar/details of the tournaments) belonged in a separate database, which I then had to learn to create and update. What I’m trying to do is join all this together using the same template variable above. I’ve got that bit working. But it would be even better if I didn’t have to remember the code I used in the external database but just got a drop down list of them. However not all stories are about tournaments, they may be obituaries or something else that will never need this TV, or events I haven’t put in the database, hence the need for NULL. I just can’t believe this hasn’t come up before.

But even if you didn’t think you understood the problem, you I think did anyhow. Many thanks, unless I misunderstood your reply in return. Although what you suggest is maybe hacking the core?

The suggestion from Bruno above is also a possibility as well, although I had trouble making the databbase query system $MODX->query->select work and ended up just doing the query in snippet as standard php (that is probably a very trivial misunderstanding on my part) which works great as the query is cached.

Mark Crowther

24,544 Posts

BobRay Reply #6, 17 years, 2 months ago

Don’t thank me until you try it and it works for you. wink

It definitly *is* hacking the core and would almost certainly be overwritten when you upgrade.

Bruno17’s solution would really be a better option.

It might also be possible to put Bruno17s code (or the code inside my case statment) in a snippet, then put a tag for that snippet in a chunk and use @CHUNK in the TV. I don’t think the parse order would support that, though.

What we need, IMO, is an @SNIPPET binding. That would make this a trivial problem.

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting

☆ A M B ☆
24,524 Posts

sottwell Reply #7, 17 years, 2 months ago

Indeed this does come up every now and then; there are as many different solutions to it as there are specific requirements for it, which is why till now there is no definitive solution.

Studying MODX in the desert - http://sottwell.com
Tips and Tricks from the MODX Forums and Slack Channels - http://modxcookbook.com
Join the Slack Community - http://modx.org

60 Posts

MarkTWIC Reply #8, 17 years, 2 months ago

Quote from: sottwell at Aug 24, 2009, 10:59 PM

Indeed this does come up every now and then; there are as many different solutions to it as there are specific requirements for it, which is why till now there is no definitive solution.

I’m a bit surprised it isn’t available out of the box. I would have thought at least 50% of the time a NULL option would be required. In fact even if the value could not be NULL, having an effective default as the first value does not strike me as particularly sensible. It is better to be able to see the drop down menus you haven’t filled yet.

Mark Crowther

60 Posts

MarkTWIC Reply #9, 17 years, 2 months ago

Quote from: BobRay at Aug 24, 2009, 08:15 PM

Don’t thank me until you try it and it works for you.

It definitly *is* hacking the core and would almost certainly be overwritten when you upgrade.

Bruno17’s solution would really be a better option.

I tried your solution of hacking the core, I couldn’t get it to work. Then I tried Bruno17’s and discovered that my table structure was unhelpful (which is probably why I couldn’t get accessing databases to work before).

I had it like this

modx database

modx_table_names
my_database

and moved it to modx_my_database and now the drop down works great.

My thanks to everyone for their help.