Php MySQL filter search

[Andy2024]

I have a materials table with over 7000 rows in it and it can take some time to load in datatables so have a pick from list drop down which works but not how i would like it, here is the code

public function get_materials_pick($id) {
return $this->db->select("SELECT 
".PREFIX."tprices.IdPrice,
".PREFIX."tprices.Supplier,
".PREFIX."tprices.ProductCode,
".PREFIX."tprices.ProductDesc,
".PREFIX."tprices.Price
FROM
".PREFIX."tprices 
WHERE
".PREFIX."tprices.ProductDesc LIKE '%$id%'
ORDER BY
".PREFIX."tprices.Price, ".PREFIX."tprices.Supplier ASC");
}

within datatables if i wanted to search say a black coat size XL i could put "Coat Black XL" "Black XL Coat" and anything and it would filter it but when I do the query above i have to type in the order its saved in the table

 

Is their a way i can get arounf this

 

Thanks in advance

[mac_gyver]

you would explode the search term on the space characters, then dynamically build an expression for each word, that get ORed together to build the WHERE ... term.

you need to use a prepared query, so that nothing in a value can break the sql query syntax, which is how sql injection is accomplished.

[Barand]

I agree with mac_gyver except for the use of OR in the WHERE clause.

Consider this product table...

+------------+-------------------------+
| product_id | description             |
+------------+-------------------------+
| 1          | Black mamba             |
| 2          | Fireball XL5            |
| 3          | Single coat black paint |
| 4          | Coat of many colours    |
| 5          | Black coat XL           |
| 6          | Not of interest         |
+------------+-------------------------+

Code...

    $search = 'coat black xl';
    $params = array_map(fn($v)=>"%$v%" , explode(' ', $search));
    
    $q1 = "SELECT description FROM product          -- search query using OR
           WHERE description LIKE ?
              OR description LIKE ?
              OR description LIKE ?
          ";
          
    $q2 = "SELECT description FROM product          -- search query using AND
           WHERE description LIKE ?
             AND description LIKE ?
             AND description LIKE ?
          ";

Results...

Results using OR
+-------------------------+
| description             |
+-------------------------+
| Black mamba             |
| Fireball XL5            |
| Single coat black paint |
| Coat of many colours    |
| Black coat XL           |
+-------------------------+

Results using AND
+---------------+
| description   |
+---------------+
| Black coat XL |
+---------------+

A couple of other options are open to you

1. FULLTEXT
   Add fulltext index on description and

   $q3 = "SELECT description
                         , MATCH(description) AGAINST('coat black xl') as relevance
                    FROM product
                    WHERE MATCH(description) AGAINST('coat black xl')
                    ORDER BY relevance DESC
                   " ;
                   +-------------------------+-------------------+
                   | description             | relevance         |
                   +-------------------------+-------------------+
                   | Black coat XL           | 0.18123811483383  |
                   | Single coat black paint | 0.18123811483383  |
                   | Coat of many colours    | 0.090619057416916 |
                   | Black mamba             | 0.090619057416916 |
                   +-------------------------+-------------------+

   NOTE: with fulltext, words of 3 or less characters (eg "XL") are ignored.

2. Use separate columns for category, colour and size and search on those.

[Danishhafeez]

You can achieve this by splitting the user input into separate keywords and then searching for each keyword individually in your database query.

public function get_materials_pick($id) {
    // Split the user input into separate keywords
    $keywords = explode(" ", $id);
    
    // Construct the WHERE clause dynamically to search for each keyword
    $whereClause = "";
    foreach ($keywords as $keyword) {
        $whereClause .= "(".PREFIX."tprices.ProductDesc LIKE '%$keyword%') OR ";
    }
    // Remove the last 'OR' from the WHERE clause
    $whereClause = rtrim($whereClause, "OR ");

    // Construct and execute the SQL query
    $sql = "SELECT 
        ".PREFIX."tprices.IdPrice,
        ".PREFIX."tprices.Supplier,
        ".PREFIX."tprices.ProductCode,
        ".PREFIX."tprices.ProductDesc,
        ".PREFIX."tprices.Price
        FROM
        ".PREFIX."tprices 
        WHERE
        $whereClause
        ORDER BY
        ".PREFIX."tprices.Price, ".PREFIX."tprices.Supplier ASC";

    return $this->db->select($sql);
}

i hope With this modification, you can input keywords in any order, and the function will search for each of them individually in the ProductDesc column of your materials table. This should provide the flexibility you need for searching.

Best regard

Danish Hafeez | QA Assistant

ICTInnovations