How can I get Jquery to interact with my Textpattern database?

johnstephens · 2009-01-19 21:55:39

How do I get jquery to access and display category titles in an input field?

I’m using mem_simple_form to get article information into a user-driven website and I have a whole mess of categories— over 5,000 —so using a dropdown select menu would be a user experience nightmare.

Some form of autocomplete would be very useful here, and I see that there’s a plugin for jquery that does just that. But I have not idea how to get jquery to grab info from the database, present matching category titles, and pass the selected category-name into the value of an input field.

I’d love to hear from any javascript wizards and/or people who understand the basics!

Thank you in advance!

Bloke · 2009-01-19 22:38:37

johnstephens wrote:

I have not idea how to get jquery to grab info from the database, present matching category titles, and pass the selected category-name into the value of an input field.

Hello, me again. I’m using a variant of this method in smd_tags (as it happens) if you install that other autocomplete plugin and use a text box as your input mechanism.

What it does is render the regular <txp:category /> information in a standard category dropdown list (e.g. using <txp:popup type="category" /> for this) and hides it using CSS display:none. The textbox is given the ID of this humungous select list and the plugin does the rest, autocompleting for you. I should think with 5000 categories it’d be a bit slow though!

With the plugin you mention, you need to follow what they’ve done in the source on the demo page with regards the jQuery:

<script type="text/javascript">
$().ready(function() {
	$("#yourTextAreaID").autocomplete("site.com/autocomplete", {
		width: 260,
		selectFirst: false
	});
</script>

and then write yourself a bit of PHP and stash it on the server in a section Page called ‘autocomplete’ with all its radio button set to ‘No’:

<txp:php>
$q = strtolower(gps('q'));
if (!$q) return;
$items = safe_rows("*", "txp_category", "type='article'");
foreach ($items as $row) {
  if (strpos($row['name'], $q) !== false) {
    echo "$row['name']|$row['title']\n";
  }
}
</txp:php>

Hopefully that (or something very similar) will do the same trick but again, with 5000 categories it’s gonna be a bit slow and you’ll hammer the server like mad. There’s probably a more efficient way of doing it, that’s just untested and off the top of my head.

The hard part is then what to do when the stuff is submitted by the user, and it largely depends on what you are going to be doing with the data as to how to approach it.

Last edited by Bloke (2009-01-19 22:39:46)

johnstephens · 2009-01-20 01:41:11

Thank you, sir! I might have posted the wrong link, but the autocomplete plugin I have was the one I downloaded for smd_tags tag-completion, from the link in the plugin’s help file. I’ll try this out tomorrow, and check back in. Thanks!

Bloke · 2009-01-20 09:11:42

johnstephens wrote:

the autocomplete plugin I have was the one I downloaded for smd_tags tag-completion

No probs. But you might find it works differently to the one you posted, so the code I wrote above might not work. I’m not sure if the one I used in smd_tags can work interactively (I never even thought to try it, which was a bit rubbish of me: I’ll have to investigate that). Would be interesting to compare the performance of the approaches:

download and hide huge list (smd_tags method)
recreate the list on the fly for every character (code I posted above)
some other mechanism, perhaps using a cached category list to avoid the server going into meltdown

johnstephens · 2009-01-21 19:46:10

1. download and hide huge list (smd_tags method)
2. recreate the list on the fly for every character (code I posted above)
3. some other mechanism, perhaps using a cached category list to avoid the server going into meltdown

Mem_form already gives me the <txp:mem_form_select_category /> tag that outputs a select-style input element with all the categories as options— this could be the easiest way to load all the necessary data. I’d love it if we could load and hide that and make a selection using a text-style input field with some kind of auto-complete. ~~Ideally, it would be nice to present the input field something like this, but~~ I would be happy with any kind of autocomplete that displayed the category title and passed the category name as the value string.

I could use asy_jpcache to cache the mem_form_select_category category list too, to dodge the huge blow to my server.

I shall hunker down and experiment with what you’ve given me, but if you get inspired to post again, you would have my attention! My skill with php is on the same level as my javascript— it looks like magic to me.

Thanks again!

Last edited by johnstephens (2009-01-21 23:33:50)

johnstephens · 2009-03-03 18:50:51

Bloke wrote:

With the plugin you mention, you need to follow what they’ve done in the source on the demo page with regards the jQuery:

<script type="text/javascript"> $().ready(function() { $("#yourTextAreaID").autocomplete("site.com/autocomplete", { width: 260, selectFirst: false }); </script>

…and then write yourself a bit of PHP and stash it on the server in a section Page called ‘autocomplete’ with all its radio button set to ‘No’:

<txp:php> $q = strtolower(gps('q')); if (!$q) return; $items = safe_rows("*", "txp_category", "type='article'"); foreach ($items as $row) { if (strpos($row['name'], $q) !== false) { echo "$row['name']|$row['title']\n"; } } </txp:php>

I had some setbacks in another aspect of the project and I’m finally able to get back to this. The javascript above returns a parse error, and I think it’s because of the autocomplete page, which returns this error:

Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in …/textpattern/publish/taghandlers.php(3082) : eval()’d code on line 7

The tag-trace for that page looks like this:

txp tag trace: 
[SQL (0.00628900527954): select name, data from txp_lang where lang='jtk-custom' AND ( event='public' OR event='common')]
[SQL (0.00413799285889): select name, code, version from txp_plugin where status = 1 AND type IN (0,1) order by load_order]
[SQL (0.000550031661987): select name,code,version from txp_plugin where status = 1 AND name='mem_form']
[SQL (0.000514030456543): select name,code,version from txp_plugin where status = 1 AND name='glz_custom_fields']
[SQL (0.000464200973511): SHOW TABLES LIKE 'custom_fields']
[SQL (0.00029993057251): select name, privs, realname, nonce, last_access, email from ign_users where name='john']
[SQL (0.000244140625): update ign_users set last_access = now() where name = 'john']
[SQL (0.000582933425903): select * from tru_tags_prefs where 1]
[SQL (0.000278949737549): select name,code,version from txp_plugin where status = 1 AND name='sed_plugin_library']
[SQL (0.000255823135376): select name from txp_section where `name` like 'autocomplete' limit 1]
[SQL (0.000159025192261): select page, css from txp_section where name = 'autocomplete' limit 1]
[SQL (0.00046706199646): select name,type,IFNULL(unix_timestamp(file_mod_time), 0) as mod_time from txp_form where 1=1]
[SQL (0.000250816345215): select name,IFNULL(unix_timestamp(file_mod_time), 0) as mod_time from txp_page where 1=1]
[SQL (0.000257015228271): select name,IFNULL(unix_timestamp(file_mod_time), 0) as mod_time from txp_css where 1=1]
[SQL (0.000168085098267): select user_html from txp_page where name='_autocomplete']
[Page: _autocomplete]
<txp:php>
</txp:php>

The smd_tags method of downloading and hiding a huge category list would probably be the best option, but I would be willing to try out anything that works.

Thanks again!

johnstephens · 2009-03-09 19:29:58

I found a solution that works over on stackoverflow.

Here’s the code that worked:

<txp:popup type="c" />
<label for="myTextBoxId">Select Category *</label>
<input type="text" id="myTextBoxId"></input>
<txp:mem_form_text name="string_category1" required="1" label="Hidden Category Selector" />

For some reason I couldn’t get it to pass the information into a hidden field generated by mem_form_hidden.

johnstephens · 2009-07-28 23:13:17

I got another recommendation that seems a bit cleaner than the hidden popup, but I think I’m doing something wrong. Perhaps someone can identify my mistake and point me in the right direction.

Here’s what I have in my autocomplete page:

[
<txp:article_custom
	limit="99999"
	sortby="custom_x"
	sortdir="asc"
	section="my-section">
	"<txp:custom_field name="my_custom_field" />",
</txp:article_custom>
]

Going to the Sections tab, I set my_autocomplete_section to use that page. Then I added the following code to my header:

<script type="text/javascript" src="<txp:site_url />scripts/jquery-autocomplete/jquery.autocomplete.pack.js"></script>
<script type="text/javascript">
    $(function(){
		$("#myTextInputID").autocomplete("<txp:link_to_home />my_autocomplete_section");
    });
</script>

But when I enter text in the input field, the autocomplete shows me every row in my_autocomplete_section, without matching the typed text— it even shows the [brackets@]@ and "quotes@”@.

Can anyone see what I’m doing wrong?

MattD · 2009-07-28 23:23:55

rah_external_output will allow you to get rid of the hidden section.

What does the data at yoursite.com/my_autocomplete look like?

johnstephens · 2009-07-28 23:32:30

MattD:

rah_external_output will allow you to get rid of the hidden section.

Thanks! I’ve looked at it before, and I might use it. My only reservation is that using a page in tandem with cnk_versioning allows me to keep my code in the filesystem.

What does the data at yoursite.com/my_autocomplete look like?

Here’s a sample:

[

	" Archaeology in Oceania ",

	" Arq ",

	" Cambridge Classical Journal ",

	" Du-Die Zeitschrift Der Kultur ",

	...

Does that reveal anything useful?

MattD · 2009-07-28 23:44:28

You can use the way one of the demos does it and try changing the data format to

var autodata = [
<txp:article_custom
	limit="99999"
	sortby="custom_x"
	sortdir="asc"
	section="my-section">
	"<txp:custom_field name="my_custom_field" />",
</txp:article_custom>
]

Then change your header

<script type="text/javascript" src="<txp:site_url />scripts/jquery-autocomplete/jquery.autocomplete.pack.js"></script>
<script type="text/javascript" src="<txp:site_url />my_autocomplete_section"></script>

<script type="text/javascript">
    $(function(){
		$("#myTextInputID").autocomplete(autodata);
    });
</script>

I tested this using rah_external_output for the javascript source which allows you to set the content type but it should work even with text/html content type.

EDIT: Change $("#myTextInputID").autocomplete("autodata"); to $("#myTextInputID").autocomplete(autodata); oops.

Last edited by MattD (2009-07-29 18:53:52)

MattD · 2009-07-28 23:47:03

johnstephens wrote:

[

“ Archaeology in Oceania “,

“ Arq “,

“ Cambridge Classical Journal “,

“ Du-Die Zeitschrift Der Kultur “,

…

The leading spaces might be a problem. When I test with this data I have to enter a space first to get a match.

johnstephens wrote:

Thanks! I’ve looked at it before, and I might use it. My only reservation is that using a page in tandem with cnk_versioning allows me to keep my code in the filesystem.

You could use a form instead of a page and output that form using rah_external_output.

Last edited by MattD (2009-07-29 00:02:06)

Textpattern CMS

Textpattern CMS support forum

#1 2009-01-19 21:55:39

How can I get Jquery to interact with my Textpattern database?

#2 2009-01-19 22:38:37

Re: How can I get Jquery to interact with my Textpattern database?

#3 2009-01-20 01:41:11

Re: How can I get Jquery to interact with my Textpattern database?

#4 2009-01-20 09:11:42

Re: How can I get Jquery to interact with my Textpattern database?

#5 2009-01-21 19:46:10

Re: How can I get Jquery to interact with my Textpattern database?

#6 2009-03-03 18:50:51

Re: How can I get Jquery to interact with my Textpattern database?

#7 2009-03-09 19:29:58

Re: How can I get Jquery to interact with my Textpattern database?

#8 2009-07-28 23:13:17

Re: How can I get Jquery to interact with my Textpattern database?

#9 2009-07-28 23:23:55

Re: How can I get Jquery to interact with my Textpattern database?

#10 2009-07-28 23:32:30

Re: How can I get Jquery to interact with my Textpattern database?

#11 2009-07-28 23:44:28

Re: How can I get Jquery to interact with my Textpattern database?

#12 2009-07-28 23:47:03

Re: How can I get Jquery to interact with my Textpattern database?

Board footer