Ajax DataGrid Support Product Page

Search does not work with complex field

Reported 28 Mar 2013 12:31:39
1
has this problem
28 Mar 2013 12:31:39 Michael Bullard posted:
Hello,

I have combined 2 columns in my database using a view: job title and first 7 words of job description; then wrapped them with <p> tags and <a link> tags, all in the MySQL Select statement. This works and displays very well in the Ajax Datagrid.

However, when you try to search on this field, it gives no results or inconsistent results. This is the CONCAT part of the SQL:

CONCAT('<a href=\"vacancy_details.php?id_vac=',id_vac,'\" title=\"Full details for ',job_type_jty,' (Ref:',id_vac,')\">',job_type_jty,'<\/a>','<p style=\"color:#666;font-size: 0.9em;margin-left: 0;margin-top: 5px;margin-bottom: 1px;\">',SUBSTRING_INDEX(MID(jobdescription_vac,4),' ', 7),'&hellip;</p>'),

You can see the test page here:
http://www.vinylanalysis.com/all_jobs.php


A live, working example using the same data, without the complex CONCAT statement is here: www.wizardrecruitment.com/all_jobs.php

Is there a way to search the complex field to get consistent, correct search results?

Thanks

Michael Bullard

Replies

Replied 28 Mar 2013 13:10:54
28 Mar 2013 13:10:54 Miroslav Zografski replied:
Hello Michael,

I guess it is the Job Title field you refer to. What kind of search queries you execute on it?
I tried some simple ones like "Cook", "looking for" and "Home Cook" and they worked.

Regards.
Replied 29 Mar 2013 08:07:32
29 Mar 2013 08:07:32 Michael Bullard replied:
Hello Miroslav,

Yes, it is the Job Title field. I think you have demonstrated the inconsistent results. In the vinylanalysis.com version with the job description included in the Job Title, if I type in 'care worker' I get no results. But if I use the older version at wizardrecruitment.com and type in the same, I get 73 results. Similarly, 'chef' give 3 results compared with 32 results.

My client really likes the look of the page with the short job descriptions, but we must be able to search on it. Do you think the in-line CSS code might have something to do with it?

Regards

Michael Bullard
Replied 01 Apr 2013 07:03:57
01 Apr 2013 07:03:57 Miroslav Zografski replied:
Hello Michael,

Appears that the Search is case sensitive.
In vinylanalysis.com search for "Care worker" returns nothing, while searching for "Care Worker" provides 62 results. Similar with the "chef" and "Chef" queries. "Chef" provides 30 results against 1 for "chef".

Basically if you are using MySQL, as I think you do, the collation of the table and fields is responsible for the case sensitivity of the search. But that is not in the scope of the extension. More of this topic can be found in MySQL documentation.

Regards.
Replied 02 Apr 2013 08:35:56
02 Apr 2013 08:35:56 Michael Bullard replied:
Hello Miroslav,

Thanks for finding the root of the problem. I'll try adjusting the MySQL statement to make it case insensitive.

Regards

Michael
Replied 02 Apr 2013 11:23:21
02 Apr 2013 11:23:21 Michael Bullard replied:
Hello again Miroslav,

I have a partial work-round: In the MySQL I have formatted the first part of the Job Title to lowercase, using LCASE(). In the CSS, I have then used 'text-transform: capitalize' to make it display correctly. Next to the search box I have added the words 'lowercase only' (line 59 of flexigrid.pack.js) - all searches now work consistently using lowercase.

If you can tell me where to include the javascript code '.toLowerCase()' for the search input box, we can get all searches to work - both upper and lowercase. Any ideas?

Thanks

Michael

Reply to this topic