Need help to search Inventory by Make/Model, but make/model in separate table

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.

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-->

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.