Forums

Thanks to Max, I got the Multi-Table joins to work. (Not easy with the clumsy legacy table & column names I inherited. The model names I provided were even close to these other things, it really got confusing when reading the Front View Settings, Front View Fields.)

When I enable & use the CC search tool on a CC script which has multi-table joins enabled, and provide a search criteria, the search fails with an error! (Frank is used below!)

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 SQL=SELECT `Members`.*, `Status`.`member_status_desc` AS `Status.member_status_desc`, `Committee`.`committee_name` AS `Committee.committee_name`, `Industry`.`industry_name` AS `Industry.industry_name`, `Jobclass`.`jobclass_name` AS `Jobclass.jobclass_name` FROM `eu_members` AS `Members` LEFT JOIN `eu_member_statuses` AS `Status` ON Members.status=Status.member_status LEFT JOIN `eu_committees` AS `Committee` ON Members.committee=Committee.committee LEFT JOIN `eu_industries` AS `Industry` ON Members.industry_id=Industry.industry_id LEFT JOIN `eu_jobclasses` AS `Jobclass` ON Members.jobclass_id=Jobclass.jobclass_id WHERE (UPPER(STATUS)!='D') AND (`first_name` LIKE '%Frank%' OR `last_name` LIKE '%Frank%' OR `email_address` LIKE '%Frank%' OR `status` LIKE '%Frank%' OR `committee` LIKE '%Frank%') ORDER BY `last_name`, `first_name` LIMIT 0,

Disable the multi-table joins, the search works, but returns results from a single table with many coded values which users cannot understand. The joins solve that. It also finds the occurrence of all matching criteria, often not in the column where the search is intended.

At present there is a single search tool for all fields. (Not desired, but worked enough to demonstrate & have management's blessing to proceed.)

In the near future, we want to implement, 9 search criteria inputs where one or multiple criteria can be input to find specific matches. The operators may be looking for members of specific first name, or last name, or e-mail address, or committee, or status, or industry, or job class, or active date or inactive date. (A MySQLi %LIKE% type of search where all characters of last name may not be known.)

My question, should I fix the present CC ability to search or abandon it & try to make the 9 column search? How would I do that?

Thanks in advance!
Roger

I got it working somewhat. I need to set the Front View Settings, Search Settings, Search Fields to look at the same List View Fields. This included Model ID.field_name_used_in_the_JOIN_RULE. It works well.

However, we need to search on one or multiple criteria. Such as, "Active" (in membership status) & belonging to a specific Committee. Now, a single search criteria input box does not work well.

Any ideas?

Hi RLaurel,

You can use a ChronoForm to build a more complex search form - see the FAQs here for an example. Without researching I'm not exactly sure how Max links the form to the listing. It does use the ChronoConnectivity and ChronoForms App settings.

For the FAQs the CC | Frontend List Settings | Search Settings tab has the settings shown below. The 'keywords' Search Field Name is from the search box and the 'Category.id' Filters Fields Name near the foot is from the Category drop-own (it is filtering the FAQ articles by category).

Bob

I think I understand. I will try this. If my thinking is clear about this, the search criteria is stored in the query. This will work in some cases where we can predict what the operator may want. (A constant value in the criteria.)

If I understand you correctly, ChronoForms can be used to capture the search criteria from the operator, pass it to ChronoConnectivity, which can conduct the search, fetch results & pass that result back to ChronoForms which will populate the page.

However, we wanted to give the operators the privilege to input one or multiple search criteria desired from the website UI.

Is this where ChronoForms can enhance a ChronoConnectivity search?

Thanks,
Roger

OK, I am still lost! I have created a CF which returns information from a single table. I have not got the join to work & worse yet, I need 4 tables to join in providing complete information. I think (from what I understand), CF can only join 2 tables, no more.

I did get CC to make a result which produces a complete dump of all members. That is what we want if a 9 criteria search boxes are left empty. But we want to build a 9 criteria search form where none or all 9 criteria can be completed to produce a more limited result.

I purchased the CF book, read it, but I find the mailing list for subscription to a newsletter example to be far to simplistic.

Can this be done with this product?

I am still struggling with this one. I am getting a search form, I am getting a listing of select fields from a DB On Load action, but the joins are not working, I have the 5 tables associated with form, (took 5 DB On Load actions). I wonder if 4 of these should be nested to make a join on the inner DB table loads?

Just found the book is woefully out of date. Just found a post between Alexi & GreyHead in June 2011. "Transform Form" is not in version 4. Have to use "Load CSS" type of event.

I am getting there, but not home yet!

Hi RLaurel,

If you are building a Join that needs five tables then I wouldn't try to do that using the DB Multi-Record loaders, it's possible but not straightforward. If you are OK with MySQL it's much easier to build a complex query using PHP in a Custom Code action. The basic framework is like this:

$db =& JFactory::getDBO();
$query = "
    SELECT ``
        FROM `#__`
        WHERE `` = '' ;
";
$db->setQuery($query);
$data = $db->loadAssocList();
//$data = $db->loadResult();

You can then manipulate the $data result and add it to the $form->data array to make it available to the rest of ChronoForms.

The ChronoForms book was written for ChronoForms v3 (as it says in the title); there is no book for CFv4 but there are a hundred or so FAQs that cover most of the basics.

Bob

Thank you Bob! I noticed that the book was written for ChronoForms version 3, but I expected much of the information is transferable. (And it is!)

It is finding those differences which are wondrous whether I didn't install the extension correctly, completely or something has become a problem.

I am not the, "world's foremost expert" with MySQL, nor would I say I am an intermediate student, however, given sufficient time, I figure them all out! I have taken courses on MySQL, but now I have to put that into practice at a level far above the course syllabus.

This doesn't mean anything to you, but I will continue trying to reach that goal.

Thank you, and I will try your offerings to make this happen.

BTW JQwery(DB)::Object or JFactory(DB)::Object statements presently make my head spin because I don't understand completely. I have a course on that under way, however, between this project, my family life & learning a new product, the grey matter between my left & right ear is a very comfortable place to be these days for an English vacationer in December or January! 🤣