Advanced Search

Hi Ray,

We worked through this together on a premium ticket and it works fine

<?php
$searchrs_products_WADbSearch1 = new WA_MySQLi_Search("rs_products","");
if (isset($_SESSION["keywords"])) {
$searchrs_products_WADbSearch1->clearSearch();
$searchrs_products_WADbSearch1->setSearch(array("type"=>"k", "comparison"=>"like", "join"=>"and", "and"=>" ", "or"=>", ", "end_encap"=>"'", "start_encap"=>"'"),array("products.itemnumber", "products.supplieritemnumber", "products.description", "products.german_description", "products.supplier", "products.barcode"),"c",$_SESSION["keywords"]);
}
?>
<?php
$rs_products = new WA_MySQLi_RS("rs_products",$dmbc_database,200);
$rs_products->setQuery("SELECT id, itemnumber, german_description, selling_quantity, stockstatus, stock, packed FROM products ORDER BY description ASC");
$rs_products->execute();
?>

I now want to use CONCAT(suppler, ' ', description) AS search1 in the recordset so that I can search for products by supplier and description so I have modified the code to:

<?php
$searchrs_products_WADbSearch1 = new WA_MySQLi_Search("rs_products","");
if (isset($_SESSION["keywords"])) {
$searchrs_products_WADbSearch1->clearSearch();
$searchrs_products_WADbSearch1->setSearch(array("type"=>"k", "comparison"=>"like", "join"=>"and", "and"=>" ", "or"=>", ", "end_encap"=>"'", "start_encap"=>"'"),array("products.itemnumber", "products.supplieritemnumber", "products.description", "products.german_description", "products.supplier", "products.barcode", "products.search1"),"c",$_SESSION["keywords"]);
}
?>
<?php
$rs_products = new WA_MySQLi_RS("rs_products",$dmbc_database,200);
$rs_products->setQuery("SELECT CONCAT(supplier, description) AS search1, id, itemnumber, description, german_description, selling_quantity, stockstatus, stock, packed FROM products ORDER BY description ASC");
$rs_products->execute();
?>

When I search for products PHP throws an error:

SELECT CONCAT(supplier, description) AS search1, id, itemnumber, description, german_description, selling_quantity, stockstatus, stock, packed FROM products WHERE ((`products`.`itemnumber` like ? AND `products`.`itemnumber` like ?) OR (`products`.`supplieritemnumber` like ? AND `products`.`supplieritemnumber` like ?) OR (`products`.`description` like ? AND `products`.`description` like ?) OR (`products`.`german_description` like ? AND `products`.`german_description` like ?) OR (`products`.`supplier` like ? AND `products`.`supplier` like ?) OR (`products`.`barcode` like ? AND `products`.`barcode` like ?) OR (`products`.`search1` like ? AND `products`.`search1` like ?)) ORDER BY description ASC

Unknown column 'products.search1' in 'where clause'

Can you help me resolve this.

You would only need to add the concat() to the select statement if you wanted to display that value. To add it to the search you can just add it to the array of columns searched. It already has supplier and description in the field list:

$searchrs_products_WADbSearch1->setSearch(array("type"=>"k", "comparison"=>"like", "join"=>"and", "and"=>" ", "or"=>", ", "end_encap"=>"'", "start_encap"=>"'"),array("products.itemnumber", "products.supplieritemnumber", "products.description", "products.german_description", "products.supplier", "products.barcode"),"c",$_SESSION["keywords"]);

So you shouldn't need to add anything to it to find results from the supplier and description fields.

Hi Ray,

I understand what you are saying but let us say that I am searching for products from a particular supplier then the results are not found, for example 'Pioneer ribbon' I can search individually for these terms, either the supplier 'Pioneer' or something that would be found in the product description 'Ribbon' and that is why I though about using CONCAT() in the recordset. Is it possible to do get results for products from a particular supplier using search; I am fairly sure I had this running like this using DataAssist when my site was coded in ASP.

Kind regards.

Try adding the concat to the array instead of the select like:

$searchrs_products_WADbSearch1->setSearch(array("type"=>"k", "comparison"=>"like", "join"=>"and", "and"=>" ", "or"=>", ", "end_encap"=>"'", "start_encap"=>"'"),array("products.itemnumber", "products.supplieritemnumber", "products.description", "products.german_description", "products.supplier", "products.barcode", "CONCAT(supplier, description)"),"c",$_SESSION["keywords"]);

Worked a treat, thank you.