smd_query: Talk to the database directly via SQL

Bloke · 2010-06-13 21:45:21

the_ghost wrote:

Does the plugin supports breaking query in lines?

I don’t think the plugin cares. But the TXP parser might complain or interpret it wrongly, especially given that you have a “closing tag” character in the COUNT(*)>4 line.

But having said that, I’ve used multi-line things in smd_xml for the ‘source’ attribute and the parser has held up fine — and that attribute contained XML markup with < and > symbols in it.

Hmmm, are you writing this tag in an article or a Form? If in an article, is it set to Leave text untouched? Does it work if you put it in a Form and call it with <txp:output_form/> or the override_form?

Last edited by Bloke (2010-06-13 21:45:40)

CeBe · 2010-07-05 11:04:58

Sorry if the question has already been asked, I can’t find any answer right now (I began using TXP 1 month ago, until then I always found answers … but not now).

Here is my problem : I would like to retrieve the email of an article’s author to re-inject in zem_contact_reborn.
What is written for the moment :

<txp:smd_query column="email" table="txp_users" where="RealName=' _I_don_t_know_what_to_put_here_ '">
    <txp:zem_contact to="{email}" label="" thanks_form="f_zem_ok" form="f_zem_contact" />
</txp:smd_query>

Thanks in advance.

^{[Edited for code display — Stef]}

Last edited by Bloke (2010-07-05 11:09:41)

Bloke · 2010-07-05 11:15:01

CeBe wrote:

I would like to retrieve the email of an article’s author to re-inject in zem_contact_reborn.

If you are in an individual article context then you should be able to use <txp:author /> to get the current article author’s real name and pass that along. Something like this (untested) :

<txp:smd_query column="email" table="txp_users" where='RealName="<txp:author />"'>
    <txp:zem_contact to="{email}" label="" thanks_form="f_zem_ok" form="f_zem_contact" />
</txp:smd_query>

If you’re not viewing an article then it’s somewhat trickier!

EDIT: added missing quotes, d’oh

Last edited by Bloke (2010-07-05 11:16:02)

CeBe · 2010-07-05 12:07:33

(Thanks for fixing the display, I just didn’t understand well how to post code)

Ok … that’s what I tried first, but didn’t notice that I had just before a self-closing article tag. So I wasn’t in an article context, right ? And of course it didn’t work.

As I have 2 articles in this page (first live, second sticky), I put the contact form in the second article before displaying the body, and
- it’s ok, just have to pay attention to the ‘ and “
- body displays live article instead of sticky (o_O)

… sigh … I must have missed something that is not related to smd_query.
Back in the documentation and/or find another way.

Anyway, thank you, it seems to be the right solution.

makss · 2010-08-31 08:40:41

Thank you for very useful plugin!
Possibly it will be comfortably to add the attribute ID in smd_query.

patch for smd_query 0.41

 --replace--
                'class' => '',
                'debug' => '0',
--to--
                'class' => '',
                'id' => '',
                'debug' => '0',
--and replace--
        $finalout[] = doLabel($label, $labeltag).doWrap(array_merge($colout, $out), $wraptag, $break, $class);
--to--
        $finalout[] = doLabel($label, $labeltag).doWrap(array_merge($colout, $out), $wraptag, $break, $class,'','','',$id);

patch for smd_query 0.21

 --replace--
                'class' => '',
                'debug' => '0',
--to--
                'class' => '',
                'id' => '',
                'debug' => '0',
--and replace--
        return doLabel($label, $labeltag).doWrap(array_merge($colout,$out), $wraptag, $break, $class);
--to--
        return doLabel($label, $labeltag).doWrap(array_merge($colout,$out), $wraptag, $break, $class,'','','',$id);

makss · 2010-08-31 09:01:04

PHP4 fix – replace function stripos to function preg_match

patch for smd_query 0.41

--replace--
               $mode = ($mode == 'auto') ? ((stripos($query, "SELECT") === 0) ? 'output' : 'input') : $mode;
--to--
                $mode = ($mode == 'auto') ? ((preg_match('/(select|show) /i', $query)) ? 'output' : 'input') : $mode;

Upd: small fix

Last edited by makss (2010-08-31 09:52:36)

Bloke · 2010-08-31 09:28:25

Thanks makss. Next version now has:

support for TXP tags inside the container under certain conditions
html_id attribute
breakclass attribute
fix for PHP 4 compatibility (oops, sorry)
mode is correctly set as ‘output’ when either SELECT or SHOW query types are used

Final testing in progress.

makss · 2010-08-31 09:48:09

Thanks Bloke.

Another idea for simple creation table header, add attributes:

colshead = "field1|field2|sometext1|..."
colshead_delim = "|"

It will be generate code

<thead><tr><th>field1</th><th>field2</th><th>sometext1</th><th>...</th></tr></thead>

.

Upd: Maybe it useful… :)

patch for smd_query 0.41

--replace--
		'colsform' => '',
		'escape' => '',
--to--
		'colsform' => '',
		'colshead' => '',
		'colshead_delim' => '|',
		'escape' => '',
--and replace--
			if ($out) {
--to--
			if ($out) {
				if ($colshead) {
					$colout[] = '<th>'.str_replace($colshead_delim, '</th><th>', $colshead).'</th>';
				}

It’s generate output:

<tr><th>field1</th><th>field2</th><th>sometext1</th><th>...</th></tr>

Last edited by makss (2010-08-31 10:36:23)

Bloke · 2010-08-31 20:58:24

v0.5 is released

form now overrides container
container’s <txp:else /> tag can be applied to forms (see example 14 in the plugin help)
populate attribute : support for TXP tags inside the container under certain conditions (thanks atbradley)
html_id attribute (thanks makss)
breakclass attribute
fix for PHP 4 compatibility (thanks makss)
mode is correctly set as ‘output’ when either SELECT or SHOW query types are used

Hope that helps a few folk out to do some further crazy SQL skulduggery.

aslsw66 · 2010-10-28 14:31:20

Is there any way to iterate across the results from smd_query and conditionally set an output based on the result?

Here’s what I’m now trying to do…

With your help, I have now set up an area in a password protected section where nominated users can add items to a table (in both senses – a database table, and a table presented on the web page). These users can also delete from the table, and of course edit an existing entry in a table.

One of the items is selectable from a select input. If they are creating a new entry, then the select defaults to the first option. Unfortunately, this is the same if they are editing an existing entry.

What I would really like is for the select input to default to the previously stored entry. Now, I know that smd_query can extract multiple values and iterate over them for the sake of pagination. But what I want to do is to check the result of each row and then set the default attribute for the matching option.

I thought I could do it with some of the {smd_rows} but my brain has run out of ideas.

Does that make sense? Or am I trying to push smd_query beyond its natural limits?

Thanks

Bloke · 2010-10-28 15:54:21

aslsw66

First off: awesome that you’re stretching smd_query to the max. Now to your problem:

What I would really like is for the select input to default to the previously stored entry.

I think I know what you’re trying to do. If I’ve understood correctly I’m not sure smd_query can do it directly but smd_if might help. In the example below I’ve also used smd_each to generate the option list but you can use any other iterator plugin like rah_repeat, which is far simpler. I’ve assumed that the value holding the item you want is in custom_2 and assigned it to a variable called ‘sel’:

<txp:smd_query query="select * from textpattern where ID in ('2', '3', '42', '96')" wraptag="ul" break="li">
<txp:variable name="sel">{custom_2}</txp:variable>
{Title}
<select>
<txp:smd_each type="fixed" include="options:One:Two:Three" subset="2">
<option value="{smd_var_value}"<txp:smd_if field="{smd_var_value}" value='<txp:variable name="sel" />'> selected="selected"</txp:smd_if>>{smd_var_value}</option>
</txp:smd_each>
</select>
</txp:smd_query>

There might be a neater way of doing it, but it’s getting late and my brain’s frazzled for the day. To test it, drop your query in and then put values in custom_2 or alter the code to taste. Setting the custom field to one of the words ‘One’, ‘Two’ or ‘Three’ will auto-select that entry in the list.

You’ll probably require a bit of defensive coding at the head of the smd_query loop to initialise the value of ‘sel’ to the default first option value in case custom_2 is not set, otherwise you’ll get the same item selected until another entry comes along and changes it.

If that’s not quite what you’re after then holler and I’ll try again. In the meantime, someone else may be able to come up with something infinitely better than my code wibbling.

Last edited by Bloke (2010-10-28 15:56:43)

aslsw66 · 2010-10-29 09:22:06

Worked first time!

I often forget to think outside the box and use multiple plugins like this, but I’m getting better I hiope.

PS. If that’s what sleep deprivation does to you, then I reckon that if we can manage to keep you awake for 24 hours a day for the next week you might be able to cure cancer and bring about world peace.

Textpattern CMS

Textpattern CMS support forum

#241 2010-06-13 21:45:21

Re: smd_query: Talk to the database directly via SQL

#242 2010-07-05 11:04:58

Re: smd_query: Talk to the database directly via SQL

#243 2010-07-05 11:15:01

Re: smd_query: Talk to the database directly via SQL

#244 2010-07-05 12:07:33

Re: smd_query: Talk to the database directly via SQL

#245 2010-08-31 08:40:41

Re: smd_query: Talk to the database directly via SQL

#246 2010-08-31 09:01:04

Re: smd_query: Talk to the database directly via SQL

#247 2010-08-31 09:28:25

Re: smd_query: Talk to the database directly via SQL

#248 2010-08-31 09:48:09

Re: smd_query: Talk to the database directly via SQL

#249 2010-08-31 20:58:24

Re: smd_query: Talk to the database directly via SQL

#250 2010-10-28 14:31:20

Re: smd_query: Talk to the database directly via SQL

#251 2010-10-28 15:54:21

Re: smd_query: Talk to the database directly via SQL

#252 2010-10-29 09:22:06

Re: smd_query: Talk to the database directly via SQL

Board footer