Product search by name and brand

Question

I have two tables, one call brands with product brands and another call products. Within that table products has shirts, pants and etc. I want that when making a search, for example "Hollister shirt", it returns all shirts with the brand Hollister. someone could help me?

Table `produto` (
  `item_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `item_name` varchar(50) NOT NULL,
  `item_slug` varchar(50) NOT NULL,
  `item_brand_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`item_id`)
) 

ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1432 ;

TABLE `marcas` (
  `item_brand_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `item_brand_name` varchar(50) NOT NULL,
  `item_brand_slug` varchar(50) NOT NULL,
  `item_brand_image` mediumtext NOT NULL,
  `item_brand_color` varchar(6) NOT NULL DEFAULT 'ffffff',
  `item_brand_priority` bigint(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`item_brand_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=319 ;

Answer 1

Prepare the data

First you need to prepare the data that the user provides you, that is, whether indicated camisa Hollister or Hollister camisa, you should find results assuming that the brand and product exist:

$search_term = "camisa Hollister";

$search_arr = explode(' ', $search_term);

$append_produtos = "";
$append_marcas = "";
foreach ($search_arr as $term) {
    $append_produtos.= "p.item_name LIKE '%".$term."%' OR ";
    $append_marcas.= "m.item_brand_name LIKE '%".$term."%' OR ";
}

Consultation

Now that we have the provided search terms prepared to apply in our query, we will prepare the same relating to the two tables, collecting only table records produtos that has a relationship with the table marca through the column item_brand_id:

$query = "SELECT p.* FROM produtos p
INNER JOIN marcas m ON (m.item_brand_id = p.item_brand_id)
WHERE (".rtrim($append_produtos, "OR ").") AND (".rtrim($append_marcas, "OR ").")";

Explanation

The consultation would be as follows:

SELECT p.* FROM produtos p
INNER JOIN marcas m ON (m.item_brand_id = p.item_brand_id)
WHERE (p.item_name LIKE '%camisa%' OR p.item_name LIKE '%Hollister%')
AND (m.item_brand_name LIKE '%camisa%' OR m.item_brand_name LIKE '%Hollister%')

What is being done is to consult all the records in the table produtos containing a value in the column item_brand_id equal to the value of the column item_brand_id table marcas where the record in the table marcas contains some of the given words and the record in the table produto contains any of the words provided.

---

Functions used in this response:

• PHP rtrim() - To remove something on the far right of string
• PHP foreach() - To traverse a matrix and perform operations for each input
• PHP explodes() - To convert a string in a matrix

Answer 2

The biggest difficulty is prepare the "LIKE" clause appropriately, since the user can type "%" or other characters with special meaning in your search.

function like($string, $e)
{
    return str_replace(array($e, '_', '%'), array($e.$e, $e.'_', $e.'%'), $string);
}

function like_clauses($campo, $termos)
{
    $clauses = array();

    foreach ($termos as $termo)
    {
        $clause = 
            '(' . $campo
            . ' LIKE "%'
            . mysqli_real_escape_string(like($termo, '='))
            . '%" ESCAPE "=")';

        $clauses[] = $clause;
    }

    return $clauses;
}

We can use the function like_clauses above, stating the name of the field for search and a list of words (terms), to obtain the respective clauses "LIKE" appropriately formatted.

function minha_busca($busca)
{
    $termos = array();

    foreach (explode(' ', $busca) as $palavra)
    {
        $termo = trim($palavra);

        if (!empty($termo))
        {
            $termos[] = $termo;
        }
    }

    $termos = array_unique($termos);

    if (empty($termos))
    {
        return array();
    }

    // até aqui apenas limpamos a entrada
    // garantindo a existência de pelo menos um termo de busca

    $sql = sprintf(
        'SELECT item_brand_id FROM marcas WHERE %s',
        implode(' OR ', like_clauses('item_brand_name', $termos))
    );

    // com este primeiro SELECT, obtemos as MARCAS

    $brand_ids = array();

    /* rodar SQL, fazer loop nos resultados, incluir os item_brand_ids em $brand_ids */

    $sql = sprintf(
        'SELECT * FROM produto WHERE %s AND (%s)',
        empty($brand_ids) ? 'TRUE' : 'item_brand_id IN (' . implode(', ', $brand_ids) . ')',
        implode(' OR ', like_clauses('item_name', $termos))
    );

    // com este segundo SELECT, obtemos os produtos, restringidos (ou não) pelas marcas

    $produtos = array();

    /* rodar SQL, fazer loop nos resultados, incluir os registros em $produtos */

    return $produtos;
}

To use the function minha_busca above, and check the results:

$busca = $_POST['busca'];

$resultado = minha_busca($busca);

print_r($resultado);

---

Remarks:

• The use of mysqli_real_escape_string assumes that the extension mysqli is in use, with an active connection.

• In function minha_busca still need to fill in the code that executes the SQL and makes the loops in the results.

Answer 3

You can use the SQL LIKE operator. Depending on how your tables look. I’ll show you an example of how to do with PHP PDO:

public function get_product( $product_name ) {
  $sql  = "SELECT * FROM produto LIKE :product_name";
  $stmt = Connection::prepare( $sql );
  $stmt->execute();

  return $stmt->fetchAll();
}

Answer 4

It was not clear. You want the SELECT of the tables, or you want to know how to connect PHP and database?

The query will be something like this:

SELECT * FROM produto 
LEFT JOIN marcas
ON produto.item_brand_id = marcas.item_brand_id
WHERE marcas.item_brand_name = 'Hollister'
AND (não entendi como tu sabe o que é camisa, o que é bermuda, etc. 
Mas aqui iria o critério para ver o que é camisa)

Or you may not know how to make php "chat" with the bank. I advise the use of documentation.

http://www.php.net/manual/en/function.mysql-connect.php

http://www.php.net/manual/en/function.mysql-query.php

Answer 5

One idea I have is this, create a view with the data you need, then when someone does a search, you do your select in that view.

Example of the view

CREATE VIEW `produto_marca` AS
SELECT CONCAT_WS(' ', p.item_name, m.item_brand_name) as item
FROM produto p
LEFT JOIN marcas m ON m.item_brand_id = p.item_brand_id

Example of the query

SELECT * FROM produto_marca WHERE item LIKE '%camisa hollister%'

Remembering that this is an idea, so I made it as simple as possible and in the view you will probably need more fields.