StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMySql Search Query for Two Combined Fields
text
Body
copied!<p>I am working on a site that allows users to list boats and yachts for sale. There is a mysql database that has a table "yachts" and among other fields ther are "make" and "model".</p> <p>When people come to the site to look for boats for sale there is a search form, one of the options is to enter the make and/or model into a text field. The relevant where clause on the results page is the following </p> <pre><code>WHERE ( make LIKE '%$yacht_make%' OR model LIKE '%$yacht_make%') </code></pre> <p>This is working if someone enters <em>either</em> the make <em>or</em> model but not if they enter both.</p> <p>For example, if someone enters "Jeanneau", the make, it finds the boat with that make, or if they enter "Sun Odyssey", the model, it finds the boat of that model, but if they enter "Jeanneau Sun Odyssey" it comes up empty.</p> <p>Is there is a way to write a query where all three ways of entering the above search criteria would find the boat?</p> <p>Here is the site <a href="http://yachtsoffered.com/" rel="nofollow">http://yachtsoffered.com/</a></p> <p>Thanks,</p> <p>Rob Fenwick</p> <p>Edit:</p> <p>The query is built with a php script here is the script</p> <pre><code> if(!empty($yacht_id)) { $where = " WHERE yacht_id = $yacht_id "; } else { $where = " WHERE ( make LIKE '%$yacht_make%' OR model LIKE '%$yacht_make%') "; if(!empty($year_from) && !empty($year_to)){ $where .= "AND ( year BETWEEN $year_from AND $year_to ) "; } if(!empty($length_from) && !empty($length_to)){ $where .= "AND ( length_ft BETWEEN $length_from AND $length_to ) "; } if(!empty($price_from) && !empty($price_to)){ $where .= "AND ( price BETWEEN $price_from AND $price_to ) "; } if ($sail_power != 2){ $where .= "AND ( sail_power = $sail_power ) "; } if (count($material_arr) > 0){ $material = 'AND ('; foreach ($material_arr as $value) { $material .= ' material LIKE \'%' . $value . '%\' OR'; } $material = substr_replace ( $material , ') ' , -2 ); $where .= $material; } if (count($engine_arr) > 0){ $engine = 'AND ('; foreach ($engine_arr as $value) { $engine .= ' engine LIKE \'%' . $value . '%\' OR'; } $engine = substr_replace ( $engine , ') ' , -2 ); $where .= $engine; } if (count($type_arr) > 0){ $type = 'AND ('; foreach ($type_arr as $value) { $type .= ' type LIKE \'' . $value . '\' OR'; } $type = substr_replace ( $type , ') ' , -2 ); $where .= $type; } if (count($region_arr) > 0){ $region = 'AND ('; foreach ($region_arr as $value) { $region .= ' region LIKE \'' . $value . '\' OR'; } $region = substr_replace ( $region , ') ' , -2 ); $where .= $region; } $where .= 'AND ( active = 1 ) ORDER BY yacht_id DESC'; } $sql = "SELECT * FROM $tbl_name $where LIMIT $start, $limit"; $result = mysql_query($sql); </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload