Need help to search Inventory by Make/Model, but make/model in separate table
5 posts by 2 authors in: Forums > CMS Builder
Last Post: November 2, 2009 (RSS)
By Codee - October 29, 2009
We've set up our site to use the dynamic menus with heirarchy so we can do a lot of manufacturer/model and Equipment Type lists for vehicles. The challenge we're having is two-fold:
In CMSB Admin panel, under inventory section we are unable to search Inventory records by Manufacturer/Model or Equipment Type (because they are in separate sections from Inventory).
For the site visitors they are unable to search by Manufacturer/Model or Equipment type also. To solve that we bought and installed the Google search tool, but that did not perform as expected or refresh quickly enough so we replaced it with a search box that queries the CMSB database but it only queries the fields in the inventory section...so how can we make it query the inventory section, the make/model section, equipment type section and maybe the dealer info section?
We're in crunch on this now.
In CMSB Admin panel, under inventory section we are unable to search Inventory records by Manufacturer/Model or Equipment Type (because they are in separate sections from Inventory).
For the site visitors they are unable to search by Manufacturer/Model or Equipment type also. To solve that we bought and installed the Google search tool, but that did not perform as expected or refresh quickly enough so we replaced it with a search box that queries the CMSB database but it only queries the fields in the inventory section...so how can we make it query the inventory section, the make/model section, equipment type section and maybe the dealer info section?
We're in crunch on this now.
Re: [equinox69] Need help to search Inventory by Make/Model, but make/model in separate table
By Dave - October 29, 2009
Hi Terry,
If the tables are linked with a pulldown field you can add a new search field as we discussed in your other post and it should show a pulldown above the record list. So you could select a make or model from that.
I'm not sure exactly how your site is setup but I think that should work even if they are different sections.
As for searching multiple sections. Do you just want any records that match from any of those sections? Or inventory matches that can use keywords from other section records related to the inventory record?
The first step would be to create a mockup of the search form and results page you want to have.
But basically you can write some custom MySQL query code and pass that to the viewer functions to have them search joined tables or look for a keyword in multiple sections.
If the tables are linked with a pulldown field you can add a new search field as we discussed in your other post and it should show a pulldown above the record list. So you could select a make or model from that.
I'm not sure exactly how your site is setup but I think that should work even if they are different sections.
As for searching multiple sections. Do you just want any records that match from any of those sections? Or inventory matches that can use keywords from other section records related to the inventory record?
The first step would be to create a mockup of the search form and results page you want to have.
But basically you can write some custom MySQL query code and pass that to the viewer functions to have them search joined tables or look for a keyword in multiple sections.
Dave Edis - Senior Developer
interactivetools.com
interactivetools.com
Re: [Dave] Need help to search Inventory by Make/Model, but make/model in separate table
By Codee - October 30, 2009
Dave,
Okay, I get that now...so to make this work for manufacturer I put the following into the "searching" portion of the section:
Manufacturer|manufacturer|match
and that works. But since model is a subcategory of manufacturer, if I just put Model|model|match then the field won't prepopulate.
If the tables are linked with a pulldown field you can add a new search field as we discussed in your other post and it should show a pulldown above the record list. So you could select a make or model from that.
Okay, I get that now...so to make this work for manufacturer I put the following into the "searching" portion of the section:
Manufacturer|manufacturer|match
and that works. But since model is a subcategory of manufacturer, if I just put Model|model|match then the field won't prepopulate.
Re: [Dave] Need help to search Inventory by Make/Model, but make/model in separate table
By Codee - October 30, 2009
As for searching multiple sections. Do you just want any records that match from any of those sections? Or inventory matches that can use keywords from other section records related to the inventory record?
The first step would be to create a mockup of the search form and results page you want to have.
It would be simply awesome to find any records matching from those sections. Here's a mockup of the code we're using at the moment that only searches the chosen fields from the Inventory section. I keyed in each field, comma separated, because I don't know how to set up the query to "just check all" fields:
<!--searchbox starts here-->
<center>
<form method="POST" action="http://www.sitenamewitheld.com/searchinventory.php">
<input type="text" size="40" name="num,createdDate,createdByUserNum,createdBy.fullname,agent,updatedDate,updatedByUserNum,featured,listing_type,specs_table,sold,equipment_type,4_x_4,year,manufacturer,model,make_alternate,quantity,title,content,keyword_1,keyword_2,keyword_3,keyword_4,price,price_message,seller_name,seller_telephone,seller_telephone_2,seller_telephone_3,seller_email,seller_website,vehicle_location_city,vehicle_location_state,vehicle_location_zip,overall_condition,miles,hours,vin,gvw,serial_number,engine_make,engine_horsepower,engine_speed,engine_cylinders,fuel_type_gas,fuel_type_diesel,fuel_type_propane,fuel_type_electric,fuel_type_hybrid,air_conditioning,undercarriage,rear_end,rear_end_mfg,rear_end_ratio,two_speed_rearend,brakes_hydraulic,brakes_air,transmission_make,transmission_automatic,transmission_standard,transmission_mixed,transmission_speed,number_axles,suspension_type,tire_size,tire_condition_front,tire_condition_rear,tracks,bed,bed_type,bed_size,trailer_size,bed_condition_excellent,bed_condition_good,bed_condition_fair,attachments,boom,blade,bucket,lift_height,lift_capacity,shipping_info,weight,dimensions,stock_number,additional_comments,video_link_1,video_link_2,video_link_3,alternative_content_query" value="">
<br clear="all"><br>
<center>
<input type="submit" name="submit" value="Search Inventory">
</center>
</form>
</center>
<!--searchbox ends here-->
Re: [equinox69] Need help to search Inventory by Make/Model, but make/model in separate table
By Dave - November 2, 2009
Hi Terry,
The problem with search every field is you'll start getting lots of irrelevant results when there are many records.
There's no simple and easy way to get search results from multiple related tables right now with CMS Builder.
There's is the following option, although it requires some more advanced PHP and MySQL skills:
'leftJoin' => array( // Note: leftJoins require you to use fully qualified fieldnames in WHERE and ORDER BY, such as tablename.fieldname
'foreignTable' => 'ON thisTable.foreignKey = foreignTable.num',
),
And that will make all the fields from the related table available for searching, but then you need to start using full tablename.fieldname name for all the fields in the query.
You can always see the query being generated with this option:
'debugSql' => true,
For advanced search engines my recommendation would be you figure out exactly how you want it to work.
Write up a few short use cases (searching for 123X should show this result because it's related to table X
as a Y), and then have us or another programmer write up the SQL for you.
The problem with search every field is you'll start getting lots of irrelevant results when there are many records.
There's no simple and easy way to get search results from multiple related tables right now with CMS Builder.
There's is the following option, although it requires some more advanced PHP and MySQL skills:
'leftJoin' => array( // Note: leftJoins require you to use fully qualified fieldnames in WHERE and ORDER BY, such as tablename.fieldname
'foreignTable' => 'ON thisTable.foreignKey = foreignTable.num',
),
And that will make all the fields from the related table available for searching, but then you need to start using full tablename.fieldname name for all the fields in the query.
You can always see the query being generated with this option:
'debugSql' => true,
For advanced search engines my recommendation would be you figure out exactly how you want it to work.
Write up a few short use cases (searching for 123X should show this result because it's related to table X
as a Y), and then have us or another programmer write up the SQL for you.
Dave Edis - Senior Developer
interactivetools.com
interactivetools.com