SQL query with two fields in PHP and MySQL

Question

SQL query with two fields in PHP and MySQL

Navigation

#1 by (0 votes)

0

I have a small problem please if someone can help me = D, I will be very grateful. When looking for products for sale: just pull me to find a single name or field, I want to search for example "tablet asus" two fields.

this is my code:

public static function getLike($p){

$sql = "select * from product inner join category on product.category_id=category.id_categoria where name like '%$p%' or marca like '%$p%' or modelo like '%$p%' or barcode like '%$p%' or description like '%$p%'";

    $query = Executor::doit($sql);
        return Model::many($query[0],new ProductData());
    }

php mysql-workbench

asked by AllegroCantabile 14.01.2018 в 17:13

source

1 answer

Problem in obtaining the value of all the rows of a jquery datatable How vb can be calculated and show many decimals

score 0 · Accepted Answer

By passing the variables directly in the query you expose yourself to an attack of SQL injection , you should consider use prepared statements that among other things help to avoid that.

That said, you can use CONCAT to search in various fields with a string like this:

$sql = "SELECT * FROM product INNER JOIN category ON product.category_id=category.id_categoria WHERE CONCAT (name, marca, modelo, barcode, description) LIKE '%$p%';

In your case you can use the explode function to get all the words of your query and then go concatenated in the search in this way

public static function getLike($p){
    $params = explode(' ', $p);
    $sql = "SELECT * FROM product INNER JOIN category ON product.category_id=category.id_categoria WHERE ";
    foreach ($params as $index => $param) {
        if ($index === 0) {
            $sql .= "CONCAT (name, marca, modelo, barcode, description) LIKE '%$param%' ";
        } else {
            $sql .= "OR CONCAT (name, marca, modelo, barcode, description) LIKE '%$param%' ";
        }
    }
    $sql .= ";";

    $query = Executor::doit($sql);
    return Model::many($query[0],new ProductData());
}